explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OkBA

Settings
# exclusive inclusive rows x rows loops node
1. 0.173 127.886 ↓ 1,250.0 2,500 1

Limit (cost=8,874.88..8,874.89 rows=2 width=27) (actual time=127.513..127.886 rows=2,500 loops=1)

2.          

CTE picklist_0

3. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on t347_opportunityequinixsolutionpicklistdim (cost=0.00..2.31 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=1)

  • Filter: (correlated_value = 'Hyperscale Infrastructure Team'::text)
  • Rows Removed by Filter: 24
4.          

CTE picklist_1

5. 0.004 0.027 ↑ 1.0 2 1

Bitmap Heap Scan on t354_opportunitystagenamepicklistdim (cost=8.57..15.14 rows=2 width=4) (actual time=0.027..0.027 rows=2 loops=1)

  • Recheck Cond: (correlated_value = ANY ('{"4 - Closed - Won","6 Close - Won"}'::text[]))
  • Heap Blocks: exact=2
6. 0.023 0.023 ↑ 1.0 2 1

Bitmap Index Scan on idx_t354_opportunitystagenamepicklistdim_correlated_value (cost=0.00..8.57 rows=2 width=0) (actual time=0.023..0.023 rows=2 loops=1)

  • Index Cond: (correlated_value = ANY ('{"4 - Closed - Won","6 Close - Won"}'::text[]))
7. 1.043 127.713 ↓ 1,250.0 2,500 1

Sort (cost=8,857.43..8,857.43 rows=2 width=27) (actual time=127.512..127.713 rows=2,500 loops=1)

  • Sort Key: ((t596_opp_split_fact.c2149_opp_split_quota_retirement * COALESCE(t599_clari_conversion_rate.conversion_rate, '1'::double precision))) DESC NULLS LAST
  • Sort Method: quicksort Memory: 332kB
8. 2.437 126.670 ↓ 1,506.5 3,013 1

Nested Loop Left Join (cost=171.18..8,857.42 rows=2 width=27) (actual time=2.087..126.670 rows=3,013 loops=1)

9. 2.938 112.181 ↓ 1,506.5 3,013 1

Nested Loop (cost=171.18..8,856.62 rows=2 width=35) (actual time=2.076..112.181 rows=3,013 loops=1)

10. 1.887 97.191 ↓ 1,506.5 3,013 1

Nested Loop (cost=170.75..8,842.53 rows=2 width=20) (actual time=2.061..97.191 rows=3,013 loops=1)

  • Join Filter: (t596_opp_split_fact.c2153_opp_split_type = t403_opportunitysplittypedim.sid)
  • Rows Removed by Join Filter: 5,337
11. 2.119 95.304 ↓ 1,391.7 4,175 1

Nested Loop Semi Join (cost=170.75..8,841.35 rows=3 width=24) (actual time=2.056..95.304 rows=4,175 loops=1)

  • Join Filter: (t596_opp_split_fact.c2144_opp_stagename = cte0.c1)
  • Rows Removed by Join Filter: 3,318
12. 0.697 93.185 ↓ 149.6 5,834 1

Nested Loop (cost=170.75..8,839.58 rows=39 width=28) (actual time=2.003..93.185 rows=5,834 loops=1)

13. 0.019 0.208 ↓ 12.0 12 1

HashAggregate (cost=82.00..82.01 rows=1 width=4) (actual time=0.193..0.208 rows=12 loops=1)

  • Group Key: t432_userrolehierarchy.sid
14. 0.022 0.189 ↓ 13.0 13 1

Nested Loop (cost=0.70..82.00 rows=1 width=4) (actual time=0.028..0.189 rows=13 loops=1)

15. 0.071 0.071 ↓ 13.7 96 1

Index Scan using idx_t432_userrolehierarchy_c1957_ancestor_role_id_end_s50458516 on t432_userrolehierarchy (cost=0.42..31.88 rows=7 width=8) (actual time=0.019..0.071 rows=96 loops=1)

  • Index Cond: ((c1957_ancestor_role_id = 11,556) AND (end_stamp = '32503680000000'::bigint))
16. 0.096 0.096 ↓ 0.0 0 96

Index Scan using "idx_166_t399_userroledim_sid_start_stamp_end_stamp_(c1678_porta" on t399_userroledim (cost=0.28..7.15 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=96)

  • Index Cond: ((sid = t432_userrolehierarchy.c1956_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
17. 82.644 92.280 ↓ 12.5 486 12

Bitmap Heap Scan on t596_opp_split_fact (cost=88.74..8,757.17 rows=39 width=32) (actual time=1.175..7.690 rows=486 loops=12)

  • Recheck Cond: ((c2126_opportunity_split_ownerid = t432_userrolehierarchy.sid) AND (end_stamp > '1592377199999'::bigint) AND (start_stamp <= '1592377199999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 3)) AND (c2133_opp_close_date >= 20,200,401) AND (c2133_opp_close_date <= 20,200,630))
  • Rows Removed by Filter: 7,531
  • Heap Blocks: exact=28,341
18. 9.624 9.624 ↓ 3.6 8,038 12

Bitmap Index Scan on idx_234_t596_opp_split_fact_c2126_opportunity_split_ownerid_end (cost=0.00..88.71 rows=2,246 width=0) (actual time=0.802..0.802 rows=8,038 loops=12)

  • Index Cond: ((c2126_opportunity_split_ownerid = t432_userrolehierarchy.sid) AND (end_stamp > '1592377199999'::bigint) AND (start_stamp <= '1592377199999'::bigint))
19.          

SubPlan (for Bitmap Heap Scan)

20. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.008..0.012 rows=1 loops=1)

21. 0.000 0.000 ↑ 2.0 1 5,834

CTE Scan on picklist_1 cte0 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=1 loops=5,834)

22. 0.000 0.000 ↑ 1.0 2 4,175

Materialize (cost=0.00..1.09 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=4,175)

23. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on t403_opportunitysplittypedim (cost=0.00..1.08 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1592377199999'::bigint) AND (end_stamp > '1592377199999'::bigint) AND ((c1722_masterlabel = 'Overlay'::text) OR (c1722_masterlabel = 'Consolidation'::text)))
  • Rows Removed by Filter: 2
24. 12.052 12.052 ↑ 2.0 1 3,013

Index Scan using t340_opportunitysplitdim_sidendstampunique on t340_opportunitysplitdim (cost=0.43..7.03 rows=2 width=23) (actual time=0.004..0.004 rows=1 loops=3,013)

  • Index Cond: ((sid = t596_opp_split_fact.sid) AND (end_stamp > '1592377199999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1592377199999'::bigint))
  • Rows Removed by Filter: 0
25. 0.000 12.052 ↑ 2.0 1 3,013

Append (cost=0.00..0.38 rows=2 width=16) (actual time=0.004..0.004 rows=1 loops=3,013)

26. 0.000 0.000 ↓ 0.0 0 3,013

Seq Scan on t599_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=3,013)

  • Filter: ((to_iso_code = 'USD'::text) AND (t596_opp_split_fact.c2146_opp_currency_code = sid) AND (t596_opp_split_fact.c2133_opp_close_date = rate_date))
27. 12.052 12.052 ↑ 1.0 1 3,013

Index Scan using t599_clari_conversion_rate_usd_sidratedateunique on t599_clari_conversion_rate_usd (cost=0.29..0.38 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=3,013)

  • Index Cond: ((t596_opp_split_fact.c2146_opp_currency_code = sid) AND (t596_opp_split_fact.c2133_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
Planning time : 3.804 ms
Execution time : 128.482 ms