explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NSW5

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

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

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

CTE picklist_1

3. 0.025 0.025 ↑ 2.0 5 1

Seq Scan on t1359_opportunitystagenamepicklistdim t1359_opportunitystagenamepicklistdim_1 (cost=0.00..6.67 rows=10 width=4) (actual time=0.008..0.025 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 1.026 ↑ 41.4 34 1

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

  • Group Key: t3304_oppfact.sid
8. 0.042 0.963 ↑ 3.6 394 1

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

9. 0.014 0.411 ↓ 1.4 34 1

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

  • Group Key: t3304_oppfact_1.sid
10. 0.023 0.397 ↓ 1.4 34 1

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

  • Hash Cond: (t3304_oppfact_1.c11620_opp_stagename = cte0_1.c1)
11. 0.343 0.343 ↓ 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.017..0.343 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.004 0.031 ↑ 2.0 5 1

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

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

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

14. 0.489 0.510 ↓ 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.015 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.021 0.021 ↑ 2.1 7 1

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

17.          

CTE lost_stages

18. 0.012 1.157 ↑ 9.0 7 1

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

  • Group Key: t3304_oppfact_2.c11620_opp_stagename
19. 0.029 1.145 ↑ 1,216.1 34 1

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

20. 0.013 1.048 ↑ 5.9 34 1

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

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

CTE Scan on prior_stage p1 (cost=0.00..28.12 rows=1,406 width=12) (actual time=1.018..1.035 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.193 ↑ 9.0 7 1

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

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

CTE Scan on lost_stages p (cost=0.00..1.26 rows=63 width=20) (actual time=1.156..1.161 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.850 ms
Execution time : 1.406 ms