explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Semt

Settings
# exclusive inclusive rows x rows loops node
1. 592,504.642 673,182.518 ↓ 1.0 19,753,313 1

Hash Full Join (cost=1,486,449.24..14,709,922.98 rows=19,750,904 width=849) (actual time=20,606.379..673,182.518 rows=19,753,313 loops=1)

  • Hash Cond: (vw.spot_id = lh.source_spot_id)
2. 7,202.607 62,688.901 ↑ 1.0 19,750,904 1

Hash Left Join (cost=533,089.34..4,302,238.02 rows=19,750,904 width=250) (actual time=2,592.826..62,688.901 rows=19,750,904 loops=1)

  • Hash Cond: (vw.order_id = lh_1.order_id)
3. 6,373.373 53,310.410 ↑ 1.0 19,750,904 1

Hash Left Join (cost=11,611.18..3,728,906.03 rows=19,750,904 width=246) (actual time=416.762..53,310.410 rows=19,750,904 loops=1)

  • Hash Cond: (vw.sales_person_master_id = ss.sales_split_id)
4. 22,080.625 46,598.684 ↑ 1.0 19,750,904 1

Hash Left Join (cost=6,437.06..3,491,385.39 rows=19,750,904 width=242) (actual time=78.130..46,598.684 rows=19,750,904 loops=1)

  • Hash Cond: (vw.order_id = order_to_deal.order_id)
5. 24,448.145 24,448.145 ↑ 1.0 19,750,904 1

Seq Scan on vw_spot_and_header vw (cost=0.00..1,980,753.04 rows=19,750,904 width=230) (actual time=7.481..24,448.145 rows=19,750,904 loops=1)

6. 39.119 69.914 ↓ 1.0 172,211 1

Hash (cost=3,444.68..3,444.68 rows=172,111 width=16) (actual time=69.914..69.914 rows=172,211 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,134kB
7. 30.795 30.795 ↓ 1.0 172,211 1

Seq Scan on order_to_deal (cost=0.00..3,444.68 rows=172,111 width=16) (actual time=0.508..30.795 rows=172,211 loops=1)

  • Filter: (is_impression_adjustment = 0)
  • Rows Removed by Filter: 2,243
8. 6.883 338.353 ↑ 1.0 33,288 1

Hash (cost=4,754.39..4,754.39 rows=33,578 width=8) (actual time=338.353..338.353 rows=33,288 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,813kB
9. 10.430 331.470 ↑ 1.0 33,288 1

Hash Join (cost=1,977.56..4,754.39 rows=33,578 width=8) (actual time=124.643..331.470 rows=33,288 loops=1)

  • Hash Cond: (ssp.sales_split_id = ss.sales_split_id)
10. 7.732 199.895 ↑ 1.0 33,288 1

Hash Join (cost=424.81..2,739.94 rows=33,578 width=8) (actual time=3.222..199.895 rows=33,288 loops=1)

  • Hash Cond: (ssp.sales_person_id = sp.sales_person_id)
11. 189.097 189.097 ↑ 1.0 33,288 1

Seq Scan on sales_split_sales_person ssp (cost=0.00..1,853.44 rows=33,578 width=8) (actual time=0.124..189.097 rows=33,288 loops=1)

  • Filter: (is_primary = 1)
  • Rows Removed by Filter: 40,667
12. 0.416 3.066 ↑ 1.0 2,859 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
13. 0.814 2.650 ↑ 1.0 2,859 1

Hash Join (cost=281.17..389.07 rows=2,859 width=8) (actual time=1.686..2.650 rows=2,859 loops=1)

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

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

15. 0.894 1.645 ↑ 1.0 7,014 1

Hash (cost=193.49..193.49 rows=7,014 width=4) (actual time=1.645..1.645 rows=7,014 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 311kB
16. 0.751 0.751 ↑ 1.0 7,014 1

Index Only Scan using idx_stage_harbor_user_user_id on "user" du (cost=0.28..193.49 rows=7,014 width=4) (actual time=0.024..0.751 rows=7,014 loops=1)

  • Heap Fetches: 0
17. 6.794 121.145 ↑ 1.0 41,900 1

Hash (cost=1,029.00..1,029.00 rows=41,900 width=4) (actual time=121.145..121.145 rows=41,900 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,986kB
18. 114.351 114.351 ↑ 1.0 41,900 1

Seq Scan on sales_split ss (cost=0.00..1,029.00 rows=41,900 width=4) (actual time=0.121..114.351 rows=41,900 loops=1)

19. 2.857 2,175.884 ↑ 1.6 17,994 1

Hash (cost=521,122.14..521,122.14 rows=28,482 width=4) (actual time=2,175.884..2,175.884 rows=17,994 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 889kB
20. 180.156 2,173.027 ↑ 1.6 17,994 1

HashAggregate (cost=520,552.50..520,837.32 rows=28,482 width=4) (actual time=2,169.903..2,173.027 rows=17,994 loops=1)

  • Group Key: lh_1.order_id
21. 1,992.871 1,992.871 ↓ 1.0 1,134,289 1

Seq Scan on lh_spots_to_convert lh_1 (cost=0.00..517,733.59 rows=1,127,561 width=4) (actual time=1,650.291..1,992.871 rows=1,134,289 loops=1)

  • Filter: (((clean_product_name)::text <> 'IMPRESSION ADJUSTMENT'::text) AND (source_spot_id_desc = 'spot_id - spot'::text))
  • Rows Removed by Filter: 6,292,284
22. 11,358.195 17,988.975 ↑ 1.0 6,291,727 1

Hash (cost=536,300.03..536,300.03 rows=6,298,950 width=412) (actual time=17,988.975..17,988.975 rows=6,291,727 loops=1)

  • Buckets: 16,384 Batches: 1,024 Memory Usage: 2,654kB
23. 6,630.780 6,630.780 ↑ 1.0 6,291,727 1

Seq Scan on lh_spots_to_convert lh (cost=0.00..536,300.03 rows=6,298,950 width=412) (actual time=0.155..6,630.780 rows=6,291,727 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,134,846
Planning time : 13.395 ms
Execution time : 822,171.160 ms