explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8BQ3

Settings
# exclusive inclusive rows x rows loops node
1. 1,548.189 8,368.983 ↑ 381.7 4,192,176 1

GroupAggregate (cost=762,534,710.85..809,127,116.72 rows=1,600,000,000 width=318) (actual time=6,514.173..8,368.983 rows=4,192,176 loops=1)

  • Group Key: tmp_shops_relations.visitable_shop_id, delivery_data_2019_q2.delivery_date, delivery_data_2019_q2.delivery_group_id, available_client_products.client_sku
2. 3,917.553 6,820.794 ↑ 409.4 4,330,171 1

Sort (cost=762,534,710.85..766,966,778.49 rows=1,772,827,058 width=291) (actual time=6,514.162..6,820.794 rows=4,330,171 loops=1)

  • Sort Key: tmp_shops_relations.visitable_shop_id, delivery_data_2019_q2.delivery_date DESC, delivery_data_2019_q2.delivery_group_id, available_client_products.client_sku
  • Sort Method: external merge Disk: 156776kB
3. 628.347 2,903.241 ↑ 409.4 4,330,171 1

Hash Join (cost=286.61..5,440,896.37 rows=1,772,827,058 width=291) (actual time=2.810..2,903.241 rows=4,330,171 loops=1)

  • Hash Cond: (delivery_data_2019_q2.client_product_id = available_client_products.id_uproduct)
4. 305.791 2,272.114 ↑ 407.5 4,350,776 1

Nested Loop (cost=0.43..785,011.24 rows=1,772,827,058 width=21) (actual time=0.022..2,272.114 rows=4,350,776 loops=1)

5. 3.383 3.383 ↑ 1.0 40,060 1

Seq Scan on tmp_shops_relations (cost=0.00..617.60 rows=40,060 width=8) (actual time=0.008..3.383 rows=40,060 loops=1)

6. 200.300 1,962.940 ↑ 1.2 109 40,060

Append (cost=0.43..18.22 rows=136 width=21) (actual time=0.005..0.049 rows=109 loops=40,060)

7. 801.200 801.200 ↑ 1.2 50 40,060

Index Scan using delivery_data_2019_q2_id_client_tt_idx2 on delivery_data_2019_q2 (cost=0.43..8.10 rows=59 width=21) (actual time=0.004..0.020 rows=50 loops=40,060)

  • Index Cond: (id_client_tt = tmp_shops_relations.final_shop_id)
  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 3
8. 841.260 841.260 ↑ 1.2 55 40,060

Index Scan using delivery_data_2019_q3_id_client_tt_idx2 on delivery_data_2019_q3 (cost=0.43..8.38 rows=65 width=21) (actual time=0.003..0.021 rows=55 loops=40,060)

  • Index Cond: (id_client_tt = tmp_shops_relations.final_shop_id)
  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 1
9. 120.180 120.180 ↑ 3.0 4 40,060

Index Scan using delivery_data_2019_q4_id_client_tt_idx2 on delivery_data_2019_q4 (cost=0.42..1.06 rows=12 width=21) (actual time=0.002..0.003 rows=4 loops=40,060)

  • Index Cond: (id_client_tt = tmp_shops_relations.final_shop_id)
  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 0
10. 1.556 2.780 ↑ 1.0 10,230 1

Hash (cost=158.30..158.30 rows=10,230 width=278) (actual time=2.779..2.780 rows=10,230 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 608kB
11. 1.224 1.224 ↑ 1.0 10,230 1

Seq Scan on tmp_available_client_products_5d97615906de97_87165407 available_client_products (cost=0.00..158.30 rows=10,230 width=278) (actual time=0.006..1.224 rows=10,230 loops=1)

Planning time : 5.162 ms
Execution time : 8,485.416 ms