explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cZxH

Settings
# exclusive inclusive rows x rows loops node
1. 762,527.312 897,477.013 ↓ 1.0 19,929,907 1

Hash Full Join (cost=1,509,231.48..13,992,442.02 rows=19,926,214 width=849) (actual time=20,536.253..897,477.013 rows=19,929,907 loops=1)

  • Hash Cond: (vw.spot_id = lh.source_spot_id)
2. 7,348.990 117,090.217 ↓ 1.0 19,927,609 1

Hash Left Join (cost=550,473.17..3,495,518.74 rows=19,926,214 width=250) (actual time=2,651.872..117,090.217 rows=19,927,609 loops=1)

  • Hash Cond: (vw.order_id = lh_1.order_id)
3. 6,627.409 107,587.548 ↓ 1.0 19,927,609 1

Hash Left Join (cost=27,330.60..2,920,062.29 rows=19,926,214 width=246) (actual time=498.043..107,587.548 rows=19,927,609 loops=1)

  • Hash Cond: (vw.sales_person_master_id = ss.sales_split_id)
4. 19,494.397 100,568.270 ↓ 1.0 19,927,609 1

Merge Left Join (cost=22,169.92..2,682,528.35 rows=19,926,214 width=242) (actual time=106.002..100,568.270 rows=19,927,609 loops=1)

  • Merge Cond: (vw.order_id = order_to_deal.order_id)
5. 80,164.032 80,164.032 ↓ 1.0 19,927,609 1

Index Scan using idx_vw_sphd_order_id on vw_spot_and_header vw (cost=0.44..2,316,086.65 rows=19,926,214 width=230) (actual time=0.027..80,164.032 rows=19,927,609 loops=1)

6. 735.618 909.841 ↓ 65.2 11,142,491 1

Materialize (cost=21,196.03..22,050.93 rows=170,980 width=16) (actual time=105.967..909.841 rows=11,142,491 loops=1)

7. 151.553 174.223 ↓ 1.0 171,099 1

Sort (cost=21,196.03..21,623.48 rows=170,980 width=16) (actual time=105.963..174.223 rows=171,099 loops=1)

  • Sort Key: order_to_deal.order_id
  • Sort Method: external sort Disk: 4,624kB
8. 22.670 22.670 ↓ 1.0 171,099 1

Seq Scan on order_to_deal (cost=0.00..3,412.40 rows=170,980 width=16) (actual time=0.250..22.670 rows=171,099 loops=1)

  • Filter: (is_impression_adjustment = 0)
  • Rows Removed by Filter: 2,133
9. 6.267 391.869 ↓ 1.0 33,307 1

Hash (cost=4,746.11..4,746.11 rows=33,165 width=8) (actual time=391.869..391.869 rows=33,307 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,814kB
10. 8.474 385.602 ↓ 1.0 33,307 1

Hash Join (cost=1,979.12..4,746.11 rows=33,165 width=8) (actual time=189.836..385.602 rows=33,307 loops=1)

  • Hash Cond: (ssp.sales_split_id = ss.sales_split_id)
11. 7.362 251.816 ↓ 1.0 33,307 1

Hash Join (cost=424.94..2,735.91 rows=33,165 width=8) (actual time=64.211..251.816 rows=33,307 loops=1)

  • Hash Cond: (ssp.sales_person_id = sp.sales_person_id)
12. 180.371 180.371 ↓ 1.0 33,307 1

Seq Scan on sales_split_sales_person ssp (cost=0.00..1,854.95 rows=33,165 width=8) (actual time=0.109..180.371 rows=33,307 loops=1)

  • Filter: (is_primary = 1)
  • Rows Removed by Filter: 40,689
13. 0.627 64.083 ↑ 1.0 2,859 1

Hash (cost=389.21..389.21 rows=2,859 width=8) (actual time=64.083..64.083 rows=2,859 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
14. 1.192 63.456 ↑ 1.0 2,859 1

Hash Join (cost=281.31..389.21 rows=2,859 width=8) (actual time=25.701..63.456 rows=2,859 loops=1)

  • Hash Cond: (sp.user_id = du.user_id)
15. 37.523 37.523 ↑ 1.0 2,859 1

Seq Scan on sales_person sp (cost=0.00..68.59 rows=2,859 width=8) (actual time=0.927..37.523 rows=2,859 loops=1)

16. 0.987 24.741 ↑ 1.0 7,019 1

Hash (cost=193.57..193.57 rows=7,019 width=4) (actual time=24.741..24.741 rows=7,019 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 311kB
17. 23.754 23.754 ↑ 1.0 7,019 1

Index Only Scan using idx_stage_harbor_user_user_id on "user" du (cost=0.28..193.57 rows=7,019 width=4) (actual time=1.568..23.754 rows=7,019 loops=1)

  • Heap Fetches: 0
18. 6.435 125.312 ↑ 1.0 41,919 1

Hash (cost=1,030.19..1,030.19 rows=41,919 width=4) (actual time=125.312..125.312 rows=41,919 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,986kB
19. 118.877 118.877 ↑ 1.0 41,919 1

Seq Scan on sales_split ss (cost=0.00..1,030.19 rows=41,919 width=4) (actual time=0.113..118.877 rows=41,919 loops=1)

20. 2.763 2,153.679 ↑ 1.7 17,567 1

Hash (cost=522,776.36..522,776.36 rows=29,297 width=4) (actual time=2,153.679..2,153.679 rows=17,567 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 874kB
21. 174.520 2,150.916 ↑ 1.7 17,567 1

HashAggregate (cost=522,190.42..522,483.39 rows=29,297 width=4) (actual time=2,148.076..2,150.916 rows=17,567 loops=1)

  • Group Key: lh_1.order_id
22. 1,976.396 1,976.396 ↓ 1.0 1,116,476 1

Seq Scan on lh_spots_to_convert lh_1 (cost=0.00..519,437.86 rows=1,101,026 width=4) (actual time=1,639.061..1,976.396 rows=1,116,476 loops=1)

  • Filter: (((clean_product_name)::text <> 'IMPRESSION ADJUSTMENT'::text) AND (source_spot_id_desc = 'spot_id - spot'::text))
  • Rows Removed by Filter: 6,339,048
23. 11,850.812 17,859.484 ↑ 1.0 6,338,032 1

Hash (cost=538,076.67..538,076.67 rows=6,353,651 width=412) (actual time=17,859.484..17,859.484 rows=6,338,032 loops=1)

  • Buckets: 16,384 Batches: 1,024 Memory Usage: 2,668kB
24. 6,008.672 6,008.672 ↑ 1.0 6,338,032 1

Seq Scan on lh_spots_to_convert lh (cost=0.00..538,076.67 rows=6,353,651 width=412) (actual time=0.114..6,008.672 rows=6,338,032 loops=1)

  • Filter: ((record_row_number = 1) AND ((source_spot_id_desc = ANY ('{"usn - aired spot","usn - booked spot"}'::text[])) OR (spot_type_sk = 3)))
  • Rows Removed by Filter: 1,117,492
Planning time : 5.051 ms
Execution time : 1,059,016.212 ms