explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GEvh

Settings
# exclusive inclusive rows x rows loops node
1. 1,592.369 11,631.714 ↑ 381.7 4,192,176 1

GroupAggregate (cost=786,253,240.77..832,845,646.64 rows=1,600,000,000 width=318) (actual time=9,732.969..11,631.714 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,909.005 10,039.345 ↑ 409.4 4,330,171 1

Sort (cost=786,253,240.77..790,685,308.42 rows=1,772,827,058 width=291) (actual time=9,732.960..10,039.345 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. 583.623 6,130.340 ↑ 409.4 4,330,171 1

Merge Join (cost=2,542,876.23..29,159,426.30 rows=1,772,827,058 width=291) (actual time=4,766.578..6,130.340 rows=4,330,171 loops=1)

  • Merge Cond: (tmp_shops_relations.final_shop_id = delivery_data_2019_q2.id_client_tt)
4. 15.497 15.497 ↑ 1.0 40,054 1

Index Scan using tmp_shops_relations_final_shop_id_idx on tmp_shops_relations (cost=0.29..1,917.19 rows=40,060 width=8) (actual time=0.017..15.497 rows=40,054 loops=1)

5. 314.398 5,531.220 ↑ 1.8 4,901,251 1

Materialize (cost=2,542,875.94..2,587,130.23 rows=8,850,859 width=291) (actual time=4,766.555..5,531.220 rows=4,901,251 loops=1)

6. 1,909.440 5,216.822 ↑ 1.8 4,901,251 1

Sort (cost=2,542,875.94..2,565,003.09 rows=8,850,859 width=291) (actual time=4,766.552..5,216.822 rows=4,901,251 loops=1)

  • Sort Key: delivery_data_2019_q2.id_client_tt
  • Sort Method: external merge Disk: 191840kB
7. 972.649 3,307.382 ↑ 1.8 4,901,251 1

Hash Join (cost=286.18..311,522.99 rows=8,850,859 width=291) (actual time=8.157..3,307.382 rows=4,901,251 loops=1)

  • Hash Cond: (delivery_data_2019_q2.client_product_id = available_client_products.id_uproduct)
8. 395.745 2,327.224 ↑ 1.0 8,849,765 1

Append (cost=0.00..287,993.68 rows=8,850,859 width=21) (actual time=0.617..2,327.224 rows=8,849,765 loops=1)

9. 946.340 946.340 ↑ 1.0 4,120,155 1

Seq Scan on delivery_data_2019_q2 (cost=0.00..116,423.05 rows=4,120,833 width=21) (actual time=0.616..946.340 rows=4,120,155 loops=1)

  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 268982
10. 931.901 931.901 ↑ 1.0 4,475,400 1

Seq Scan on delivery_data_2019_q3 (cost=0.00..120,479.52 rows=4,475,816 width=21) (actual time=0.007..931.901 rows=4,475,400 loops=1)

  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 69835
11. 53.238 53.238 ↑ 1.0 254,210 1

Seq Scan on delivery_data_2019_q4 (cost=0.00..6,836.81 rows=254,210 width=21) (actual time=0.006..53.238 rows=254,210 loops=1)

  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 3844
12. 4.287 7.509 ↑ 1.0 10,230 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 608kB
13. 3.222 3.222 ↑ 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.020..3.222 rows=10,230 loops=1)

Planning time : 5.188 ms
Execution time : 11,773.581 ms