explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5TFA

Settings
# exclusive inclusive rows x rows loops node
1. 14,220.487 95,834.015 ↑ 1.0 19,750,904 1

Hash Left Join (cost=544,892.60..8,133,108.43 rows=19,750,904 width=1,047) (actual time=2,429.787..95,834.015 rows=19,750,904 loops=1)

  • Hash Cond: (vw.order_id = lh.order_id)
2. 15,876.535 79,283.870 ↑ 1.0 19,750,904 1

Hash Left Join (cost=11,496.69..7,547,858.79 rows=19,750,904 width=1,043) (actual time=99.976..79,283.870 rows=19,750,904 loops=1)

  • Hash Cond: (vw.sales_person_master_id = ss.sales_split_id)
3. 58,167.785 63,363.417 ↑ 1.0 19,750,904 1

Hash Left Join (cost=6,322.58..7,310,338.16 rows=19,750,904 width=1,035) (actual time=55.770..63,363.417 rows=19,750,904 loops=1)

  • Hash Cond: (vw.order_id = order_to_deal.order_id)
4. 5,140.505 5,140.505 ↑ 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=1,023) (actual time=0.124..5,140.505 rows=19,750,904 loops=1)

5. 30.711 55.127 ↓ 1.0 169,384 1

Hash (cost=3,379.86..3,379.86 rows=169,257 width=16) (actual time=55.127..55.127 rows=169,384 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,087kB
6. 24.416 24.416 ↓ 1.0 169,384 1

Seq Scan on order_to_deal (cost=0.00..3,379.86 rows=169,257 width=16) (actual time=0.277..24.416 rows=169,384 loops=1)

  • Filter: (is_impression_adjustment = 0)
  • Rows Removed by Filter: 2,125
7. 5.377 43.918 ↑ 1.0 33,288 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,813kB
8. 7.488 38.541 ↑ 1.0 33,288 1

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

  • Hash Cond: (ssp.sales_split_id = ss.sales_split_id)
9. 6.597 20.094 ↑ 1.0 33,288 1

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

  • Hash Cond: (ssp.sales_person_id = sp.sales_person_id)
10. 10.423 10.423 ↑ 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.121..10.423 rows=33,288 loops=1)

  • Filter: (is_primary = 1)
  • Rows Removed by Filter: 40,667
11. 0.412 3.074 ↑ 1.0 2,859 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
12. 0.792 2.662 ↑ 1.0 2,859 1

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

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

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

14. 0.919 1.673 ↑ 1.0 7,014 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 311kB
15. 0.754 0.754 ↑ 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.031..0.754 rows=7,014 loops=1)

  • Heap Fetches: 0
16. 6.018 10.959 ↑ 1.0 41,900 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,986kB
17. 4.941 4.941 ↑ 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.120..4.941 rows=41,900 loops=1)

18. 2.851 2,329.658 ↑ 1.6 17,658 1

Hash (cost=533,034.72..533,034.72 rows=28,895 width=4) (actual time=2,329.658..2,329.658 rows=17,658 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 877kB
19. 194.959 2,326.807 ↑ 1.6 17,658 1

HashAggregate (cost=532,456.82..532,745.77 rows=28,895 width=4) (actual time=2,323.820..2,326.807 rows=17,658 loops=1)

  • Group Key: lh.order_id
20. 2,131.848 2,131.848 ↓ 1.0 1,124,361 1

Seq Scan on lh_spots_to_convert lh (cost=0.00..529,686.55 rows=1,108,109 width=4) (actual time=1,788.264..2,131.848 rows=1,124,361 loops=1)

  • Filter: (((clean_product_name)::text <> 'IMPRESSION ADJUSTMENT'::text) AND (source_spot_id_desc = 'spot_id - spot'::text))
  • Rows Removed by Filter: 6,260,009
Planning time : 1.283 ms
Execution time : 235,362.955 ms