explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7KmD

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 14,330.234 ↑ 9.8 5 1

HashAggregate (cost=114,671,942.88..114,671,943.50 rows=49 width=62) (actual time=14,330.232..14,330.234 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.007..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.126 14,329.933 ↑ 93.6 34 1

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

  • Group Key: t3304_oppfact.sid
8. 114.582 14,329.807 ↑ 8.1 394 1

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

9. 105.675 105.675 ↑ 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.028..105.675 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. 219.468 13,954.500 ↑ 1.7 15 155,050

Result (cost=1.09..692.82 rows=25 width=4) (actual time=0.003..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.983 13,735.000 ↓ 1.4 34 67,000

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

  • Hash Cond: (t3304_oppfact_1.c11620_opp_stagename = cte0_1.c1)
13. 12,596.000 12,596.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.188 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.003 0.017 ↑ 2.0 5 1

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=0.017..0.017 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.032 0.032 ↑ 2.1 7 1

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

18.          

CTE lost_stages

19. 0.014 14,330.166 ↑ 9.0 7 1

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

  • Group Key: t3304_oppfact_2.c11620_opp_stagename
20. 0.016 14,330.152 ↑ 1,216.1 34 1

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

21. 0.052 14,330.034 ↑ 9.4 34 1

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

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

CTE Scan on prior_stage p1 (cost=0.00..63.62 rows=3,181 width=12) (actual time=7,325.185..14,329.982 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.013 14,330.221 ↑ 9.0 7 1

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

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

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

26. 0.015 0.037 ↑ 1.5 67 1

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

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

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

Planning time : 1.669 ms
Execution time : 14,338.106 ms