explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NULb

Settings
# exclusive inclusive rows x rows loops node
1. 1,558.661 11,486.663 ↑ 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,627.617..11,486.663 rows=4,192,176 loops=1)

  • Group Key: tmp_shops_relations.visitable_shop_id, delivery_data_2019_q2.delivery_group_id, delivery_data_2019_q2.delivery_date, available_client_products.client_sku
2. 3,953.446 9,928.002 ↑ 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,627.607..9,928.002 rows=4,330,171 loops=1)

  • Sort Key: tmp_shops_relations.visitable_shop_id, delivery_data_2019_q2.delivery_group_id, delivery_data_2019_q2.delivery_date DESC, available_client_products.client_sku
  • Sort Method: external merge Disk: 156776kB
3. 570.861 5,974.556 ↑ 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,640.862..5,974.556 rows=4,330,171 loops=1)

  • Merge Cond: (tmp_shops_relations.final_shop_id = delivery_data_2019_q2.id_client_tt)
4. 14.928 14.928 ↑ 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.019..14.928 rows=40,054 loops=1)

5. 306.693 5,388.767 ↑ 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,640.837..5,388.767 rows=4,901,251 loops=1)

6. 1,872.018 5,082.074 ↑ 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,640.834..5,082.074 rows=4,901,251 loops=1)

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

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

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

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

9. 899.919 899.919 ↑ 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.599..899.919 rows=4,120,155 loops=1)

  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 268982
10. 928.585 928.585 ↑ 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..928.585 rows=4,475,400 loops=1)

  • Filter: ((amount > '0'::numeric) AND (delivery_date > '2019-04-04'::date))
  • Rows Removed by Filter: 69835
11. 52.393 52.393 ↑ 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..52.393 rows=254,210 loops=1)

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

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

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

Planning time : 5.336 ms
Execution time : 11,625.684 ms