explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yuVb

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 1.171 ↑ 9.8 5 1

HashAggregate (cost=4,403.71..4,404.32 rows=49 width=62) (actual time=1.170..1.171 rows=5 loops=1)

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

CTE picklist_1

3. 0.027 0.027 ↑ 2.0 5 1

Seq Scan on t1359_opportunitystagenamepicklistdim t1359_opportunitystagenamepicklistdim_1 (cost=0.00..6.67 rows=10 width=4) (actual time=0.009..0.027 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.019 0.019 ↑ 2.1 7 1

Seq Scan on t1359_opportunitystagenamepicklistdim t1359_opportunitystagenamepicklistdim_2 (cost=0.00..7.06 rows=15 width=4) (actual time=0.005..0.019 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.063 0.998 ↑ 41.4 34 1

HashAggregate (cost=2,430.16..2,444.22 rows=1,406 width=12) (actual time=0.989..0.998 rows=34 loops=1)

  • Group Key: t3304_oppfact.sid
8. 0.061 0.935 ↑ 3.6 394 1

Nested Loop (cost=693.30..2,423.13 rows=1,406 width=12) (actual time=0.431..0.935 rows=394 loops=1)

9. 0.014 0.398 ↓ 1.4 34 1

HashAggregate (cost=692.54..692.79 rows=25 width=4) (actual time=0.393..0.398 rows=34 loops=1)

  • Group Key: t3304_oppfact_1.sid
10. 0.022 0.384 ↓ 1.4 34 1

Hash Semi Join (cost=0.74..692.48 rows=25 width=4) (actual time=0.053..0.384 rows=34 loops=1)

  • Hash Cond: (t3304_oppfact_1.c11620_opp_stagename = cte0_1.c1)
11. 0.330 0.330 ↓ 1.0 159 1

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.016..0.330 rows=159 loops=1)

  • 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
12. 0.003 0.032 ↑ 2.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.029 0.029 ↑ 2.0 5 1

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

14. 0.456 0.476 ↓ 1.2 12 34

Index Scan using idx_3080_t3304_oppfact_sid_end_stamp_start_stamp_null on t3304_oppfact (cost=0.76..69.11 rows=10 width=12) (actual time=0.005..0.014 rows=12 loops=34)

  • Index Cond: ((sid = t3304_oppfact_1.sid) AND (end_stamp < '1600897946000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 1
15.          

SubPlan (for Index Scan)

16. 0.020 0.020 ↑ 2.1 7 1

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

17.          

CTE lost_stages

18. 0.011 1.126 ↑ 9.0 7 1

HashAggregate (cost=1,935.74..1,936.38 rows=63 width=20) (actual time=1.124..1.126 rows=7 loops=1)

  • Group Key: t3304_oppfact_2.c11620_opp_stagename
19. 0.027 1.115 ↑ 1,216.1 34 1

Nested Loop (cost=35.57..1,625.65 rows=41,346 width=16) (actual time=1.024..1.115 rows=34 loops=1)

20. 0.014 1.020 ↑ 5.9 34 1

HashAggregate (cost=35.15..37.15 rows=200 width=12) (actual time=1.014..1.020 rows=34 loops=1)

  • Group Key: p1.c2, p1.c1
21. 1.006 1.006 ↑ 41.4 34 1

CTE Scan on prior_stage p1 (cost=0.00..28.12 rows=1,406 width=12) (actual time=0.989..1.006 rows=34 loops=1)

22. 0.068 0.068 ↑ 1.0 1 34

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

  • Index Cond: ((start_stamp = p1.c1) AND (sid = p1.c2))
23. 0.006 1.162 ↑ 9.0 7 1

Hash Left Join (cost=7.32..8.75 rows=63 width=38) (actual time=1.156..1.162 rows=7 loops=1)

  • Hash Cond: (p.c3 = t1359_opportunitystagenamepicklistdim.sid)
24. 1.130 1.130 ↑ 9.0 7 1

CTE Scan on lost_stages p (cost=0.00..1.26 rows=63 width=20) (actual time=1.126..1.130 rows=7 loops=1)

25. 0.014 0.026 ↑ 1.5 67 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
26. 0.012 0.012 ↑ 1.5 67 1

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

Planning time : 1.516 ms
Execution time : 1.371 ms