explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ea52

Settings
# exclusive inclusive rows x rows loops node
1. 13,966.868 96,779.720 ↑ 1.0 19,750,904 1

Hash Left Join (cost=544,892.51..8,133,108.34 rows=19,750,904 width=1,047) (actual time=2,993.493..96,779.720 rows=19,750,904 loops=1)

  • Hash Cond: (vw.order_id = lh.order_id)
2. 15,891.261 79,943.717 ↑ 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=124.203..79,943.717 rows=19,750,904 loops=1)

  • Hash Cond: (vw.sales_person_master_id = ss.sales_split_id)
3. 58,620.501 64,007.077 ↑ 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=78.532..64,007.077 rows=19,750,904 loops=1)

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

5. 43.842 77.768 ↓ 1.0 169,384 1

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,087kB
6. 33.926 33.926 ↓ 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.419..33.926 rows=169,384 loops=1)

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (sp.user_id = du.user_id)
13. 0.196 0.196 ↑ 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.196 rows=2,859 loops=1)

14. 0.918 1.681 ↑ 1.0 7,014 1

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

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

  • Heap Fetches: 0
16. 6.078 11.051 ↑ 1.0 41,900 1

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

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

18. 2.830 2,869.135 ↑ 1.6 17,658 1

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

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

HashAggregate (cost=532,456.74..532,745.69 rows=28,895 width=4) (actual time=2,863.170..2,866.305 rows=17,658 loops=1)

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

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

  • Filter: ((source_spot_id_desc = 'spot_id - spot'::text) AND (is_manual_adjustment = 0))
  • Rows Removed by Filter: 6,260,009
Planning time : 1.176 ms
Execution time : 232,830.164 ms