explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wDgy

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 6,079.761 ↑ 36.5 52 1

Append (cost=4,319,338.83..4,321,183.29 rows=1,898 width=120) (actual time=4,364.432..6,079.761 rows=52 loops=1)

  • Planning time: 4.862 ms
  • Execution time: 6082.272 ms
2.          

CTE picklist_0

3. 0.056 0.056 ↑ 1.1 11 1

Seq Scan on t34_opportunitystagenamepicklistdim (cost=0.00..14.40 rows=12 width=4) (actual time=0.012..0.056 rows=11 loops=1)

  • Filter: (correlated_value = ANY ('{"E-Launch - Closed/Won/Monitor - 100%","Stage 6 - Implement (Closed)","Closed Won",Closed/Won,"Order won","Stage 7 - Submit Order","Closed Won (Closed/Won, 100%, Closed)",ClosedWon,"Closed Won Reagent","7 Work Complete","Closde Won","\\\"7 Work Complete\\\""}'::text[]))
  • Rows Removed by Filter: 89
4.          

CTE picklist_1

5. 0.031 0.031 ↑ 1.0 8 1

Seq Scan on t34_opportunitystagenamepicklistdim t34_opportunitystagenamepicklistdim_1 (cost=0.00..13.72 rows=8 width=4) (actual time=0.006..0.031 rows=8 loops=1)

  • Filter: (correlated_value = ANY ('{"Abandoned (Closed/Lost, 0%, Omitted)","Closed Lost","Abandoned (E-Com & E-Proc)","Closed Lost (E-Com & E-Proc)","Order deferred","Order lost","Lost Deal",Abandoned}'::text[]))
  • Rows Removed by Filter: 92
6.          

CTE picklist_4

7. 0.068 0.068 ↑ 1.1 19 1

Seq Scan on t34_opportunitystagenamepicklistdim t34_opportunitystagenamepicklistdim_2 (cost=0.00..15.76 rows=20 width=4) (actual time=0.016..0.068 rows=19 loops=1)

  • Filter: (correlated_value = ANY ('{Closed/Won,"Abandoned (E-Com & E-Proc)","Closed Lost (E-Com & E-Proc)","Order won","Stage 7 - Submit Order","Closde Won",Abandoned,"\\\"7 Work Complete\\\"","E-Launch - Closed/Won/Monitor - 100%","Stage 6 - Implement (Closed)","Abandoned (Closed/Lost, 0%, Omitted)","Closed Lost","Closed Won","Order deferred","Closed Won (Closed/Won, 100%, Closed)","Order lost",ClosedWon,"Closed Won Reagent","7 Work Complete","Lost Deal"}'::text[]))
  • Rows Removed by Filter: 81
8.          

CTE bucketac

9. 3.949 4,369.546 ↑ 1,392.6 23 1

GroupAggregate (cost=3,325,196.66..3,470,773.04 rows=32,030 width=92) (actual time=4,364.406..4,369.546 rows=23 loops=1)

  • Group Key: t122633_opp_line_item_fact.c612605_opp_forecastcategory, (CASE WHEN (oli63_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli63_ft1.c4 < 20181001) OR (oli63_ft1.c4 > 20181230)) THEN 'slipped'::text WHEN (alternatives: SubPlan 4 or hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t35_opportunityforecastcategorypicklistdim_2.correlated_app_order = oli63_ft1.c7) THEN 'unchanged'::text WHEN (t35_opportunityforecastcategorypicklistdim_2.correlated_app_order > oli63_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10. 8.919 4,365.597 ↑ 1.9 16,953 1

Sort (cost=3,325,196.21..3,325,276.29 rows=32,032 width=56) (actual time=4,364.402..4,365.597 rows=16,953 loops=1)

  • Sort Key: t122633_opp_line_item_fact.c612605_opp_forecastcategory, (CASE WHEN (oli63_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli63_ft1.c4 < 20181001) OR (oli63_ft1.c4 > 20181230)) THEN 'slipped'::text WHEN (alternatives: SubPlan 4 or hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t35_opportunityforecastcategorypicklistdim_2.correlated_app_order = oli63_ft1.c7) THEN 'unchanged'::text WHEN (t35_opportunityforecastcategorypicklistdim_2.correlated_app_order > oli63_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: quicksort Memory: 2093kB
11. 22.661 4,356.678 ↑ 1.9 16,953 1

Nested Loop Left Join (cost=147,466.08..3,322,799.05 rows=32,032 width=56) (actual time=120.898..4,356.678 rows=16,953 loops=1)

12. 25.538 129.171 ↑ 1.9 16,953 1

HashAggregate (cost=147,371.47..147,691.79 rows=32,032 width=28) (actual time=119.832..129.171 rows=16,953 loops=1)

  • Group Key: t122633_opp_line_item_fact.c612563_opportunity_sid, t122633_opp_line_item_fact.c612605_opp_forecastcategory, t35_opportunityforecastcategorypicklistdim_2.correlated_app_order
13. 9.111 103.633 ↓ 1.5 49,631 1

Hash Left Join (cost=12,507.12..146,971.07 rows=32,032 width=28) (actual time=32.807..103.633 rows=49,631 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact.c612605_opp_forecastcategory = t35_opportunityforecastcategorypicklistdim_2.sid)
14. 9.060 94.516 ↓ 1.5 49,631 1

Hash Left Join (cost=12,506.01..146,530.36 rows=32,032 width=24) (actual time=32.793..94.516 rows=49,631 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact.c612608_opp_currency_code = t166_claricurrencytypedim.sid)
15. 10.535 85.179 ↓ 1.5 49,631 1

Hash Join (cost=12,459.44..144,401.71 rows=32,032 width=20) (actual time=32.509..85.179 rows=49,631 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact.c612612_opp_territory_id = t70_territoryhierarchy_2.sid)
16. 44.812 73.894 ↓ 1.2 52,818 1

Bitmap Heap Scan on t122633_opp_line_item_fact (cost=12,245.75..143,833.90 rows=42,841 width=24) (actual time=31.746..73.894 rows=52,818 loops=1)

  • Recheck Cond: ((c612549_opp_close_date >= 20181001) AND (c612549_opp_close_date <= 20181230) AND (end_stamp > '1538377200000'::bigint) AND (start_stamp <= '1538377200000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 1578
  • Heap Blocks: exact=15454
17. 29.009 29.009 ↑ 1.6 55,242 1

Bitmap Index Scan on idx_t122633_opp_line_item_fact_c612549_opp_close_date_e30514041 (cost=0.00..12,234.59 rows=85,750 width=0) (actual time=29.009..29.009 rows=55,242 loops=1)

  • Index Cond: ((c612549_opp_close_date >= 20181001) AND (c612549_opp_close_date <= 20181230) AND (end_stamp > '1538377200000'::bigint) AND (start_stamp <= '1538377200000'::bigint))
18.          

SubPlan (forBitmap Heap Scan)

19. 0.073 0.073 ↑ 1.1 19 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.40 rows=20 width=4) (actual time=0.017..0.073 rows=19 loops=1)

20. 0.120 0.750 ↓ 1.5 875 1

Hash (cost=206.24..206.24 rows=596 width=4) (actual time=0.750..0.750 rows=875 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
21. 0.210 0.630 ↓ 1.5 875 1

HashAggregate (cost=200.28..206.24 rows=596 width=4) (actual time=0.548..0.630 rows=875 loops=1)

  • Group Key: t70_territoryhierarchy_2.sid
22. 0.329 0.420 ↑ 1.1 875 1

Bitmap Heap Scan on t70_territoryhierarchy t70_territoryhierarchy_2 (cost=34.54..197.91 rows=946 width=4) (actual time=0.103..0.420 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
23. 0.091 0.091 ↑ 1.1 875 1

Bitmap Index Scan on ix_t70_territoryhierarchy_ancestor_timestamp (cost=0.00..34.30 rows=946 width=0) (actual time=0.091..0.091 rows=875 loops=1)

  • Index Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
24. 0.010 0.277 ↑ 1.0 42 1

Hash (cost=46.05..46.05 rows=42 width=12) (actual time=0.277..0.277 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 0.267 0.267 ↑ 1.0 42 1

Seq Scan on t166_claricurrencytypedim (cost=0.00..46.05 rows=42 width=12) (actual time=0.014..0.267 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1722
26. 0.002 0.006 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.006..0.006 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on t35_opportunityforecastcategorypicklistdim t35_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=8) (actual time=0.002..0.004 rows=5 loops=1)

28. 0.000 4,204.344 ↑ 1.0 1 16,953

Subquery Scan on oli63_ft1 (cost=94.16..94.58 rows=1 width=28) (actual time=0.248..0.248 rows=1 loops=16,953)

  • Filter: (t122633_opp_line_item_fact.c612563_opportunity_sid = oli63_ft1.c1)
29. 33.906 4,204.344 ↑ 10.0 1 16,953

GroupAggregate (cost=94.16..94.46 rows=10 width=36) (actual time=0.247..0.248 rows=1 loops=16,953)

  • Group Key: t122633_opp_line_item_fact_1.c612563_opportunity_sid, t122633_opp_line_item_fact_1.c612549_opp_close_date, t122633_opp_line_item_fact_1.c612612_opp_territory_id, t122633_opp_line_item_fact_1.c612603_opp_stagename, t35_opportunityforecastcategorypicklistdim_3.correlated_app_order
30. 67.812 4,170.438 ↑ 2.5 4 16,953

Sort (cost=94.16..94.18 rows=10 width=36) (actual time=0.245..0.246 rows=4 loops=16,953)

  • Sort Key: t122633_opp_line_item_fact_1.c612549_opp_close_date, t122633_opp_line_item_fact_1.c612612_opp_territory_id, t122633_opp_line_item_fact_1.c612603_opp_stagename, t35_opportunityforecastcategorypicklistdim_3.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
31. 67.812 4,102.626 ↑ 2.5 4 16,953

Nested Loop Left Join (cost=45.40..93.99 rows=10 width=36) (actual time=0.194..0.242 rows=4 loops=16,953)

  • Join Filter: (t122633_opp_line_item_fact_1.c612605_opp_forecastcategory = t35_opportunityforecastcategorypicklistdim_3.sid)
  • Rows Removed by Join Filter: 15
32. 136.278 4,034.814 ↑ 2.5 4 16,953

Hash Right Join (cost=45.40..92.18 rows=10 width=36) (actual time=0.192..0.238 rows=4 loops=16,953)

  • Hash Cond: (t166_claricurrencytypedim_1.sid = t122633_opp_line_item_fact_1.c612608_opp_currency_code)
33. 3,695.100 3,695.100 ↑ 1.0 42 16,950

Seq Scan on t166_claricurrencytypedim t166_claricurrencytypedim_1 (cost=0.00..46.05 rows=42 width=12) (actual time=0.006..0.218 rows=42 loops=16,950)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1722
34. 16.953 203.436 ↑ 2.5 4 16,953

Hash (cost=45.27..45.27 rows=10 width=32) (actual time=0.012..0.012 rows=4 loops=16,953)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 186.483 186.483 ↑ 2.5 4 16,953

Index Scan using t122633_opp_line_item_fact_286_timestamp on t122633_opp_line_item_fact t122633_opp_line_item_fact_1 (cost=0.56..45.27 rows=10 width=32) (actual time=0.009..0.011 rows=4 loops=16,953)

  • Index Cond: ((t122633_opp_line_item_fact.c612563_opportunity_sid = c612563_opportunity_sid) AND (start_stamp <= '1546243199999'::bigint) AND (end_stamp > '1546243199999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
36. 0.000 0.000 ↑ 1.0 5 63,986

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=63,986)

37. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on t35_opportunityforecastcategorypicklistdim t35_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual time=0.003..0.004 rows=5 loops=1)

38.          

SubPlan (forNested Loop Left Join)

39. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_t70_territoryhierarchy_sid_ancestor_timestamp on t70_territoryhierarchy (cost=0.29..4.51 rows=1 width=0) (never executed)

  • Index Cond: ((sid = oli63_ft1.c5) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[]))
  • Heap Fetches: 0
40. 0.321 0.408 ↑ 1.1 875 1

Bitmap Heap Scan on t70_territoryhierarchy t70_territoryhierarchy_1 (cost=34.54..197.91 rows=946 width=4) (actual time=0.099..0.408 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
41. 0.087 0.087 ↑ 1.1 875 1

Bitmap Index Scan on ix_t70_territoryhierarchy_ancestor_timestamp (cost=0.00..34.30 rows=946 width=0) (actual time=0.087..0.087 rows=875 loops=1)

  • Index Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
42. 0.058 0.058 ↑ 1.1 11 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.24 rows=12 width=4) (actual time=0.013..0.058 rows=11 loops=1)

43. 0.036 0.036 ↑ 1.0 8 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.16 rows=8 width=4) (actual time=0.010..0.036 rows=8 loops=1)

44.          

CTE bucketec

45. 10.741 1,710.087 ↑ 1,512.8 29 1

GroupAggregate (cost=649,127.11..848,520.80 rows=43,871 width=116) (actual time=1,695.058..1,710.087 rows=29 loops=1)

  • Group Key: (CASE WHEN (t122633_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t122633_opp_line_item_fact_3.c612549_opp_close_date > 20181230) THEN 'pulledInBucket'::text WHEN (t122633_opp_line_item_fact_3.c612549_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 13) THEN 'won'::text WHEN (hashed SubPlan 14) THEN 'lost'::text ELSE 'newOpen'::text END), t122633_opp_line_item_fact_2.c612605_opp_forecastcategory
46. 84.566 1,699.346 ↑ 1.1 41,114 1

Sort (cost=649,126.21..649,235.88 rows=43,871 width=88) (actual time=1,695.016..1,699.346 rows=41,114 loops=1)

  • Sort Key: (CASE WHEN (t122633_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t122633_opp_line_item_fact_3.c612549_opp_close_date > 20181230) THEN 'pulledInBucket'::text WHEN (t122633_opp_line_item_fact_3.c612549_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 13) THEN 'won'::text WHEN (hashed SubPlan 14) THEN 'lost'::text ELSE 'newOpen'::text END), t122633_opp_line_item_fact_2.c612605_opp_forecastcategory
  • Sort Method: external merge Disk: 2088kB
47. 16.897 1,614.780 ↑ 1.1 41,114 1

Hash Left Join (cost=260,673.99..643,643.54 rows=43,871 width=88) (actual time=1,412.820..1,614.780 rows=41,114 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact_2.c612608_opp_currency_code = t166_claricurrencytypedim_2.sid)
48. 107.665 1,597.276 ↑ 1.1 41,114 1

Hash Left Join (cost=260,626.52..442,557.20 rows=43,871 width=40) (actual time=1,412.481..1,597.276 rows=41,114 loops=1)

  • Hash Cond: ((t122633_opp_line_item_fact_2.sid = t122633_opp_line_item_fact_3.sid) AND (t122633_opp_line_item_fact_2.sid = t122633_opp_line_item_fact_3.sid))
  • Filter: ((t122633_opp_line_item_fact_3.sid IS NULL) OR (t122633_opp_line_item_fact_3.c612549_opp_close_date < 20181001) OR (t122633_opp_line_item_fact_3.c612549_opp_close_date > 20181230) OR (NOT (alternatives: SubPlan 15 or hashed SubPlan 16)))
  • Rows Removed by Filter: 23612
49. 20.201 116.708 ↓ 1.4 64,726 1

Hash Join (cost=12,463.76..126,918.94 rows=46,314 width=24) (actual time=38.832..116.708 rows=64,726 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact_2.c612612_opp_territory_id = t70_territoryhierarchy_7.sid)
50. 64.398 95.759 ↓ 1.1 69,455 1

Bitmap Heap Scan on t122633_opp_line_item_fact t122633_opp_line_item_fact_2 (cost=12,250.07..126,193.26 rows=61,942 width=28) (actual time=38.068..95.759 rows=69,455 loops=1)

  • Recheck Cond: ((c612549_opp_close_date >= 20181001) AND (c612549_opp_close_date <= 20181230) AND (end_stamp > '1546243199999'::bigint) AND (start_stamp <= '1546243199999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1160
  • Heap Blocks: exact=33084
51. 31.361 31.361 ↓ 2.1 132,924 1

Bitmap Index Scan on idx_t122633_opp_line_item_fact_c612549_opp_close_date_e30514041 (cost=0.00..12,234.59 rows=61,991 width=0) (actual time=31.361..31.361 rows=132,924 loops=1)

  • Index Cond: ((c612549_opp_close_date >= 20181001) AND (c612549_opp_close_date <= 20181230) AND (end_stamp > '1546243199999'::bigint) AND (start_stamp <= '1546243199999'::bigint))
52. 0.087 0.748 ↓ 1.5 875 1

Hash (cost=206.24..206.24 rows=596 width=4) (actual time=0.748..0.748 rows=875 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
53. 0.269 0.661 ↓ 1.5 875 1

HashAggregate (cost=200.28..206.24 rows=596 width=4) (actual time=0.555..0.661 rows=875 loops=1)

  • Group Key: t70_territoryhierarchy_7.sid
54. 0.308 0.392 ↑ 1.1 875 1

Bitmap Heap Scan on t70_territoryhierarchy t70_territoryhierarchy_7 (cost=34.54..197.91 rows=946 width=4) (actual time=0.099..0.392 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
55. 0.084 0.084 ↑ 1.1 875 1

Bitmap Index Scan on ix_t70_territoryhierarchy_ancestor_timestamp (cost=0.00..34.30 rows=946 width=0) (actual time=0.084..0.084 rows=875 loops=1)

  • Index Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
56. 180.025 1,372.505 ↑ 1.5 901,942 1

Hash (cost=221,278.09..221,278.09 rows=1,352,111 width=16) (actual time=1,372.505..1,372.505 rows=901,942 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2298kB
57. 1,192.480 1,192.480 ↑ 1.5 901,942 1

Seq Scan on t122633_opp_line_item_fact t122633_opp_line_item_fact_3 (cost=0.00..221,278.09 rows=1,352,111 width=16) (actual time=0.166..1,192.480 rows=901,942 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1538377200000'::bigint) AND (end_stamp > '1538377200000'::bigint))
  • Rows Removed by Filter: 4412581
58.          

SubPlan (forHash Left Join)

59. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_t70_territoryhierarchy_sid_ancestor_timestamp on t70_territoryhierarchy t70_territoryhierarchy_5 (cost=0.29..4.51 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t122633_opp_line_item_fact_3.c612612_opp_territory_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[]))
  • Heap Fetches: 0
60. 0.307 0.398 ↑ 1.1 875 1

Bitmap Heap Scan on t70_territoryhierarchy t70_territoryhierarchy_6 (cost=34.54..197.91 rows=946 width=4) (actual time=0.102..0.398 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
61. 0.091 0.091 ↑ 1.1 875 1

Bitmap Index Scan on ix_t70_territoryhierarchy_ancestor_timestamp (cost=0.00..34.30 rows=946 width=0) (actual time=0.091..0.091 rows=875 loops=1)

  • Index Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
62. 0.013 0.285 ↑ 1.0 42 1

Hash (cost=46.05..46.05 rows=42 width=12) (actual time=0.285..0.285 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
63. 0.272 0.272 ↑ 1.0 42 1

Seq Scan on t166_claricurrencytypedim t166_claricurrencytypedim_2 (cost=0.00..46.05 rows=42 width=12) (actual time=0.018..0.272 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1722
64.          

SubPlan (forHash Left Join)

65. 0.002 0.002 ↑ 1.1 19 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.40 rows=20 width=4) (actual time=0.000..0.002 rows=19 loops=1)

66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_t70_territoryhierarchy_sid_ancestor_timestamp on t70_territoryhierarchy t70_territoryhierarchy_3 (cost=0.29..4.51 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t122633_opp_line_item_fact_3.c612612_opp_territory_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[]))
  • Heap Fetches: 0
67. 0.229 0.314 ↑ 1.1 875 1

Bitmap Heap Scan on t70_territoryhierarchy t70_territoryhierarchy_4 (cost=34.54..197.91 rows=946 width=4) (actual time=0.096..0.314 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
68. 0.085 0.085 ↑ 1.1 875 1

Bitmap Index Scan on ix_t70_territoryhierarchy_ancestor_timestamp (cost=0.00..34.30 rows=946 width=0) (actual time=0.085..0.085 rows=875 loops=1)

  • Index Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
69. 0.004 0.004 ↑ 1.1 11 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.24 rows=12 width=4) (actual time=0.002..0.004 rows=11 loops=1)

70. 0.002 0.002 ↑ 1.0 8 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.16 rows=8 width=4) (actual time=0.001..0.002 rows=8 loops=1)

71. 0.024 4,369.588 ↑ 34.8 23 1

Hash Join (cost=1.11..769.84 rows=801 width=96) (actual time=4,364.432..4,369.588 rows=23 loops=1)

  • Hash Cond: (cte0.c4 = t35_opportunityforecastcategorypicklistdim.sid)
72. 4,369.555 4,369.555 ↑ 1,392.6 23 1

CTE Scan on bucketac cte0 (cost=0.00..640.60 rows=32,030 width=92) (actual time=4,364.408..4,369.555 rows=23 loops=1)

73. 0.003 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
74. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t35_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=12) (actual time=0.005..0.006 rows=5 loops=1)

75. 0.018 1,710.168 ↑ 37.8 29 1

Subquery Scan on *SELECT* 2 (cost=1.11..1,067.73 rows=1,097 width=96) (actual time=1,695.107..1,710.168 rows=29 loops=1)

76. 0.028 1,710.150 ↑ 37.8 29 1

Hash Join (cost=1.11..1,054.02 rows=1,097 width=120) (actual time=1,695.098..1,710.150 rows=29 loops=1)

  • Hash Cond: (cte0_1.c6 = t35_opportunityforecastcategorypicklistdim_1.sid)
77. 1,710.103 1,710.103 ↑ 1,512.8 29 1

CTE Scan on bucketec cte0_1 (cost=0.00..877.42 rows=43,871 width=116) (actual time=1,695.062..1,710.103 rows=29 loops=1)

78. 0.005 0.019 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.019..0.019 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 0.014 0.014 ↑ 1.0 5 1

Seq Scan on t35_opportunityforecastcategorypicklistdim t35_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=12) (actual time=0.012..0.014 rows=5 loops=1)