explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aoyc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=49.12..57.19 rows=1 width=144) (actual rows= loops=)

2.          

CTE picklist_0

3. 0.000 0.000 ↓ 0.0

Seq Scan on t16_opportunitystagenamepicklistdim (cost=0.00..15.38 rows=2 width=4) (actual rows= loops=)

  • Filter: (correlated_value = 'wonStage'::text)
4.          

CTE picklist_1

5. 0.000 0.000 ↓ 0.0

Seq Scan on t16_opportunitystagenamepicklistdim t16_opportunitystagenamepicklistdim_1 (cost=0.00..15.38 rows=2 width=4) (actual rows= loops=)

  • Filter: (correlated_value = 'lostStage'::text)
6.          

CTE bucketec

7. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=18.18..18.22 rows=1 width=120) (actual rows= loops=)

  • Group Key: (CASE WHEN (t13_opp_fact_1.sid IS NULL) THEN 'newBucket'::text WHEN (t13_opp_fact_1.c118_opportunity_close_date > 20171231) THEN 'pulledInBucket'::text WHEN (t13_opp_fact_1.c118_opportunity_close_date < 20171001) THEN 'pushedInBucket'::text WHEN ((t13_opp_fact_1.c118_opportunity_close_date >= 20,171,001) AND (t13_opp_fact_1.c118_opportunity_close_date <= 20,171,231) AND (t13_opp_fact_1.c114_opportunity_ownerid = 2)) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 3) THEN 'won'::text WHEN (hashed SubPlan 4) THEN 'lost'::text ELSE 'newOpen'::text END), t13_opp_fact.c116_opportunity_forecastcategory
8. 0.000 0.000 ↓ 0.0

Sort (cost=18.09..18.09 rows=1 width=80) (actual rows= loops=)

  • Sort Key: (CASE WHEN (t13_opp_fact_1.sid IS NULL) THEN 'newBucket'::text WHEN (t13_opp_fact_1.c118_opportunity_close_date > 20171231) THEN 'pulledInBucket'::text WHEN (t13_opp_fact_1.c118_opportunity_close_date < 20171001) THEN 'pushedInBucket'::text WHEN ((t13_opp_fact_1.c118_opportunity_close_date >= 20,171,001) AND (t13_opp_fact_1.c118_opportunity_close_date <= 20,171,231) AND (t13_opp_fact_1.c114_opportunity_ownerid = 2)) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 3) THEN 'won'::text WHEN (hashed SubPlan 4) THEN 'lost'::text ELSE 'newOpen'::text END), t13_opp_fact.c116_opportunity_forecastcategory
9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=9.90..18.08 rows=1 width=80) (actual rows= loops=)

  • Hash Cond: ((t13_opp_fact_1.sid = t13_opp_fact.sid) AND (t13_opp_fact_1.sid = t13_opp_fact.sid))
  • Filter: ((t13_opp_fact_1.sid IS NULL) OR (t13_opp_fact_1.c118_opportunity_close_date < 20171001) OR (t13_opp_fact_1.c118_opportunity_close_date > 20171231) OR (t13_opp_fact_1.c114_opportunity_ownerid <> 2))
10. 0.000 0.000 ↓ 0.0

Seq Scan on t13_opp_fact t13_opp_fact_1 (cost=0.00..7.40 rows=21 width=12) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (start_stamp <= '1506816000000'::bigint) AND (end_stamp > '1506816000000'::bigint))
11. 0.000 0.000 ↓ 0.0

Hash (cost=9.11..9.11 rows=47 width=20) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on t13_opp_fact (cost=0.00..9.11 rows=47 width=20) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (start_stamp <= '1514764799999'::bigint) AND (end_stamp > '1514764799999'::bigint) AND (c118_opportunity_close_date >= 20,171,001) AND (c118_opportunity_close_date <= 20,171,231) AND (c114_opportunity_ownerid = 2))
13.          

SubPlan (for Hash Right Join)

14. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_0 cte0_1 (cost=0.00..0.04 rows=2 width=4) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_1 cte0_2 (cost=0.00..0.04 rows=2 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

CTE Scan on bucketec cte0 (cost=0.00..0.02 rows=1 width=116) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Scan using t15_opportunityforecastcategorypicklistdim_sidunique on t15_opportunityforecastcategorypicklistdim (cost=0.15..8.17 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (cte0.c7 = sid)