explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YSHz

Settings
# exclusive inclusive rows x rows loops node
1. 804.900 4,117.825 ↑ 423.3 2,090,755 1

GroupAggregate (cost=376,513,890.96..400,853,753.21 rows=885,085,900 width=318) (actual time=3,213.837..4,117.825 rows=2,090,755 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. 1,739.518 3,312.925 ↑ 409.7 2,160,260 1

Sort (cost=376,513,890.96..378,726,605.71 rows=885,085,900 width=291) (actual time=3,213.826..3,312.925 rows=2,160,260 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: quicksort Memory: 251479kB
3. 312.321 1,573.407 ↑ 409.7 2,160,260 1

Hash Join (cost=286.61..2,968,952.78 rows=885,085,900 width=291) (actual time=8.751..1,573.407 rows=2,160,260 loops=1)

  • Hash Cond: (delivery_data_2019_q2.client_product_id = available_client_products.id_uproduct)
4. 150.892 1,252.615 ↑ 407.7 2,170,679 1

Nested Loop (cost=0.43..644,353.59 rows=885,085,900 width=21) (actual time=0.106..1,252.615 rows=2,170,679 loops=1)

5. 1.723 1.723 ↑ 1.0 20,000 1

Seq Scan on tmp_shops_relations (cost=0.00..309.00 rows=20,000 width=8) (actual time=0.017..1.723 rows=20,000 loops=1)

6. 120.000 1,100.000 ↑ 1.2 109 20,000

Append (cost=0.43..30.84 rows=136 width=21) (actual time=0.006..0.055 rows=109 loops=20,000)

7. 440.000 440.000 ↑ 1.2 50 20,000

Index Scan using delivery_data_2019_q2_id_client_tt_idx2 on delivery_data_2019_q2 (cost=0.43..14.14 rows=59 width=21) (actual time=0.004..0.022 rows=50 loops=20,000)

  • 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. 480.000 480.000 ↑ 1.2 55 20,000

Index Scan using delivery_data_2019_q3_id_client_tt_idx2 on delivery_data_2019_q3 (cost=0.43..14.59 rows=65 width=21) (actual time=0.004..0.024 rows=55 loops=20,000)

  • 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. 60.000 60.000 ↑ 4.0 3 20,000

Index Scan using delivery_data_2019_q4_id_client_tt_idx2 on delivery_data_2019_q4 (cost=0.42..1.43 rows=12 width=21) (actual time=0.002..0.003 rows=3 loops=20,000)

  • 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. 5.069 8.471 ↑ 1.0 10,230 1

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

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

Planning time : 24.087 ms
Execution time : 4,176.686 ms