explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qKJM

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 14,255.334 ↑ 9.8 5 1

HashAggregate (cost=114,671,942.88..114,671,943.50 rows=49 width=62) (actual time=14,255.332..14,255.334 rows=5 loops=1)

  • Group Key: t1359_opportunitystagenamepicklistdim.correlated_value, t1359_opportunitystagenamepicklistdim.correlated_sid
2.          

CTE picklist_1

3. 0.013 0.013 ↑ 2.0 5 1

Seq Scan on t1359_opportunitystagenamepicklistdim t1359_opportunitystagenamepicklistdim_1 (cost=0.00..6.67 rows=10 width=4) (actual time=0.002..0.013 rows=5 loops=1)

  • Filter: (correlated_value = ANY ('{Closed-Lost,Closed/Lost,Closed-Disqualified,Closed/Dead,"Closed - Lost/Dead"}'::text[]))
  • Rows Removed by Filter: 62
4.          

CTE picklist_5

5. 0.028 0.028 ↑ 2.1 7 1

Seq Scan on t1359_opportunitystagenamepicklistdim t1359_opportunitystagenamepicklistdim_2 (cost=0.00..7.06 rows=15 width=4) (actual time=0.006..0.028 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{Closed-Won,Closed/Won,"PO Received",Closed-Lost,Closed/Lost,Closed-Disqualified,Closed/Dead,"Closed - Lost/Dead"}'::text[]))
  • Rows Removed by Filter: 60
6.          

CTE prior_stage

7. 0.121 14,255.055 ↑ 93.6 34 1

GroupAggregate (cost=1.51..114,669,081.62 rows=3,181 width=12) (actual time=7,239.658..14,255.055 rows=34 loops=1)

  • Group Key: t3304_oppfact.sid
8. 40.619 14,254.934 ↑ 8.1 394 1

Nested Loop Semi Join (cost=1.51..114,669,033.90 rows=3,181 width=12) (actual time=199.760..14,254.934 rows=394 loops=1)

9. 104.765 104.765 ↑ 1.1 155,050 1

Index Scan using t3304_oppfact_sidendstampunique on t3304_oppfact (cost=0.42..34,404.19 rows=165,384 width=25) (actual time=0.019..104.765 rows=155,050 loops=1)

10. 155.050 14,109.550 ↓ 0.0 0 155,050

Subquery Scan on ANY_subquery (cost=1.09..693.13 rows=1 width=4) (actual time=0.091..0.091 rows=0 loops=155,050)

  • Filter: (t3304_oppfact.sid = "ANY_subquery".sid)
  • Rows Removed by Filter: 15
11. 286.469 13,954.500 ↑ 1.7 15 155,050

Result (cost=1.09..692.82 rows=25 width=4) (actual time=0.002..0.090 rows=15 loops=155,050)

  • One-Time Filter: ((NOT (hashed SubPlan 3)) AND (t3304_oppfact.end_stamp < '1600897946000'::bigint) AND (NOT t3304_oppfact.deleted))
12. 1,138.984 13,668.000 ↓ 1.4 34 67,000

Hash Semi Join (cost=1.09..692.82 rows=25 width=4) (actual time=0.005..0.204 rows=34 loops=67,000)

  • Hash Cond: (t3304_oppfact_1.c11620_opp_stagename = cte0_1.c1)
13. 12,529.000 12,529.000 ↓ 1.0 158 67,000

Index Scan using idx_3093_t3304_oppfact_c11635_opp_close_date_c11620_opp_stagena on t3304_oppfact t3304_oppfact_1 (cost=0.42..691.46 rows=157 width=8) (actual time=0.005..0.187 rows=158 loops=67,000)

  • Index Cond: ((c11635_opp_close_date >= 20,200,801) AND (c11635_opp_close_date <= 20,201,031) AND (start_stamp <= '1600897946000'::bigint) AND (end_stamp > '1600897946000'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 14
14. 0.002 0.016 ↑ 2.0 5 1

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.016..0.016 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.014 0.014 ↑ 2.0 5 1

CTE Scan on picklist_1 cte0_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.014 rows=5 loops=1)

16.          

SubPlan (for Result)

17. 0.031 0.031 ↑ 2.1 7 1

CTE Scan on picklist_5 cte0 (cost=0.00..0.30 rows=15 width=4) (actual time=0.007..0.031 rows=7 loops=1)

18.          

CTE lost_stages

19. 0.012 14,255.273 ↑ 9.0 7 1

HashAggregate (cost=2,837.52..2,838.15 rows=63 width=20) (actual time=14,255.272..14,255.273 rows=7 loops=1)

  • Group Key: t3304_oppfact_2.c11620_opp_stagename
20. 0.014 14,255.261 ↑ 1,216.1 34 1

Nested Loop (cost=79.95..2,527.43 rows=41,346 width=16) (actual time=14,255.157..14,255.261 rows=34 loops=1)

21. 0.048 14,255.145 ↑ 9.4 34 1

HashAggregate (cost=79.53..82.71 rows=318 width=12) (actual time=14,255.137..14,255.145 rows=34 loops=1)

  • Group Key: p1.c2, p1.c1
22. 14,255.097 14,255.097 ↑ 93.6 34 1

CTE Scan on prior_stage p1 (cost=0.00..63.62 rows=3,181 width=12) (actual time=7,239.660..14,255.097 rows=34 loops=1)

23. 0.102 0.102 ↑ 1.0 1 34

Index Scan using t3304_oppfact_startstampsid on t3304_oppfact t3304_oppfact_2 (cost=0.42..7.69 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=34)

  • Index Cond: ((start_stamp = p1.c1) AND (sid = p1.c2))
24. 0.012 14,255.323 ↑ 9.0 7 1

Hash Left Join (cost=7.32..8.75 rows=63 width=38) (actual time=14,255.317..14,255.323 rows=7 loops=1)

  • Hash Cond: (p.c3 = t1359_opportunitystagenamepicklistdim.sid)
25. 14,255.278 14,255.278 ↑ 9.0 7 1

CTE Scan on lost_stages p (cost=0.00..1.26 rows=63 width=20) (actual time=14,255.274..14,255.278 rows=7 loops=1)

26. 0.012 0.033 ↑ 1.5 67 1

Hash (cost=6.03..6.03 rows=103 width=26) (actual time=0.033..0.033 rows=67 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
27. 0.021 0.021 ↑ 1.5 67 1

Seq Scan on t1359_opportunitystagenamepicklistdim (cost=0.00..6.03 rows=103 width=26) (actual time=0.005..0.021 rows=67 loops=1)

Planning time : 1.585 ms
Execution time : 14,262.818 ms