explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qvpK

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 1.319 ↑ 9.8 5 1

HashAggregate (cost=4,652.89..4,653.51 rows=49 width=62) (actual time=1.317..1.319 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.007..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.018 0.018 ↑ 2.1 7 1

Seq Scan on t1359_opportunitystagenamepicklistdim t1359_opportunitystagenamepicklistdim_2 (cost=0.00..7.06 rows=15 width=4) (actual time=0.004..0.018 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 subq

7. 0.025 0.402 ↓ 1.4 34 1

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

  • Hash Cond: (t3304_oppfact.c11620_opp_stagename = cte0.c1)
8. 0.346 0.346 ↓ 1.0 159 1

Index Scan using idx_3093_t3304_oppfact_c11635_opp_close_date_c11620_opp_stagena on t3304_oppfact (cost=0.42..691.46 rows=157 width=8) (actual time=0.020..0.346 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
9. 0.004 0.031 ↑ 2.0 5 1

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

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

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

11.          

CTE prior_stage

12. 0.043 1.121 ↑ 7.5 34 1

GroupAggregate (cost=2,025.19..2,029.67 rows=256 width=12) (actual time=1.069..1.121 rows=34 loops=1)

  • Group Key: t3304_oppfact_1.sid
13. 0.092 1.078 ↓ 1.5 394 1

Sort (cost=2,025.19..2,025.83 rows=256 width=12) (actual time=1.060..1.078 rows=394 loops=1)

  • Sort Key: t3304_oppfact_1.sid
  • Sort Method: quicksort Memory: 43kB
14. 0.067 0.986 ↓ 1.5 394 1

Nested Loop (cost=0.76..2,014.95 rows=256 width=12) (actual time=0.101..0.986 rows=394 loops=1)

15. 0.409 0.409 ↓ 1.4 34 1

CTE Scan on subq s (cost=0.00..0.50 rows=25 width=4) (actual time=0.057..0.409 rows=34 loops=1)

16. 0.491 0.510 ↓ 1.2 12 34

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

  • Index Cond: ((sid = s.c1) AND (end_stamp < '1600897946000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 1
17.          

SubPlan (for Index Scan)

18. 0.019 0.019 ↑ 2.1 7 1

CTE Scan on picklist_5 cte0_1 (cost=0.00..0.30 rows=15 width=4) (actual time=0.004..0.019 rows=7 loops=1)

19.          

CTE lost_stages

20. 0.012 1.269 ↑ 9.0 7 1

HashAggregate (cost=1,906.99..1,907.62 rows=63 width=20) (actual time=1.268..1.269 rows=7 loops=1)

  • Group Key: t3304_oppfact_2.c11620_opp_stagename
21. 0.013 1.257 ↑ 1,216.1 34 1

Nested Loop (cost=6.82..1,596.90 rows=41,346 width=16) (actual time=1.148..1.257 rows=34 loops=1)

22. 0.014 1.142 ↑ 5.9 34 1

HashAggregate (cost=6.40..8.40 rows=200 width=12) (actual time=1.136..1.142 rows=34 loops=1)

  • Group Key: p1.c2, p1.c1
23. 1.128 1.128 ↑ 7.5 34 1

CTE Scan on prior_stage p1 (cost=0.00..5.12 rows=256 width=12) (actual time=1.070..1.128 rows=34 loops=1)

24. 0.102 0.102 ↑ 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.003..0.003 rows=1 loops=34)

  • Index Cond: ((start_stamp = p1.c1) AND (sid = p1.c2))
25. 0.007 1.309 ↑ 9.0 7 1

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

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

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

27. 0.015 0.030 ↑ 1.5 67 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
28. 0.015 0.015 ↑ 1.5 67 1

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

Planning time : 1.726 ms
Execution time : 1.501 ms