explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gcE8

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,388.040 ↑ 38.6 52 1

Append (cost=3,919,336.66..3,921,287.67 rows=2,007 width=120) (actual time=754.982..2,388.040 rows=52 loops=1)

2.          

CTE picklist_0

3. 0.052 0.052 ↑ 1.1 11 1

Seq Scan on t34_opportunitystagenamepicklistdim (cost=0.00..14.40 rows=12 width=4) (actual time=0.011..0.052 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.032 0.032 ↑ 1.0 8 1

Seq Scan on t34_opportunitystagenamepicklistdim t34_opportunitystagenamepicklistdim_1 (cost=0.00..13.72 rows=8 width=4) (actual time=0.007..0.032 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.958 760.162 ↑ 1,477.0 23 1

GroupAggregate (cost=2,903,324.74..3,057,718.41 rows=33,970 width=92) (actual time=754.955..760.162 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. 7.706 756.204 ↑ 2.0 16,953 1

Sort (cost=2,903,324.29..2,903,409.22 rows=33,971 width=56) (actual time=754.950..756.204 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. 20.120 748.498 ↑ 2.0 16,953 1

Nested Loop Left Join (cost=140,902.78..2,900,767.63 rows=33,971 width=56) (actual time=127.628..748.498 rows=16,953 loops=1)

12. 25.711 134.588 ↑ 2.0 16,953 1

HashAggregate (cost=140,826.06..141,165.77 rows=33,971 width=28) (actual time=126.883..134.588 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.879 108.877 ↓ 1.5 49,631 1

Hash Left Join (cost=12,429.31..140,401.42 rows=33,971 width=28) (actual time=30.927..108.877 rows=49,631 loops=1)

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

Hash Left Join (cost=12,428.20..139,934.17 rows=33,971 width=24) (actual time=30.906..98.991 rows=49,631 loops=1)

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

Hash Join (cost=12,381.62..137,679.53 rows=33,971 width=20) (actual time=30.651..89.184 rows=49,631 loops=1)

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

Bitmap Heap Scan on t122633_opp_line_item_fact (cost=12,167.93..137,092.20 rows=44,921 width=24) (actual time=29.902..77.155 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=15013
17. 27.209 27.209 ↑ 1.6 54,536 1

Bitmap Index Scan on idx_t122633_opp_line_item_fact_c612549_opp_close_date_e30514041 (cost=0.00..12,156.25 rows=89,958 width=0) (actual time=27.209..27.209 rows=54,536 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.074 0.074 ↑ 1.1 19 1

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

20. 0.152 0.736 ↓ 1.5 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
21. 0.252 0.584 ↓ 1.5 875 1

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

  • Group Key: t70_territoryhierarchy_2.sid
22. 0.252 0.332 ↑ 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.090..0.332 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.080 0.080 ↑ 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.080..0.080 rows=875 loops=1)

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

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.003 0.003 ↑ 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.003 rows=5 loops=1)

28. 0.000 593.355 ↑ 1.0 1 16,953

Subquery Scan on oli63_ft1 (cost=76.27..76.69 rows=1 width=28) (actual time=0.035..0.035 rows=1 loops=16,953)

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

GroupAggregate (cost=76.27..76.57 rows=10 width=36) (actual time=0.035..0.035 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. 33.906 559.449 ↑ 2.5 4 16,953

Sort (cost=76.27..76.29 rows=10 width=36) (actual time=0.033..0.033 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 525.543 ↑ 2.5 4 16,953

Nested Loop Left Join (cost=45.71..76.10 rows=10 width=36) (actual time=0.027..0.031 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. 16.953 457.731 ↑ 2.5 4 16,953

Merge Right Join (cost=45.71..74.29 rows=10 width=36) (actual time=0.026..0.027 rows=4 loops=16,953)

  • Merge Cond: (t166_claricurrencytypedim_1.sid = t122633_opp_line_item_fact_1.c612608_opp_currency_code)
33. 254.295 254.295 ↑ 4.7 9 16,953

Index Only Scan using ix_t166_claricurrencytypedim_sid_toisocode_rate on t166_claricurrencytypedim t166_claricurrencytypedim_1 (cost=0.28..49.93 rows=42 width=12) (actual time=0.008..0.015 rows=9 loops=16,953)

  • Index Cond: (to_iso_code = 'USD'::text)
  • Heap Fetches: 0
34. 33.906 186.483 ↑ 2.5 4 16,953

Sort (cost=45.43..45.46 rows=10 width=32) (actual time=0.010..0.011 rows=4 loops=16,953)

  • Sort Key: t122633_opp_line_item_fact_1.c612608_opp_currency_code
  • Sort Method: quicksort Memory: 25kB
35. 152.577 152.577 ↑ 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.007..0.009 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.005 0.005 ↑ 1.0 5 1

Seq Scan on t35_opportunityforecastcategorypicklistdim t35_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual time=0.004..0.005 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.264 0.346 ↑ 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.091..0.346 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.082 0.082 ↑ 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.082..0.082 rows=875 loops=1)

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

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

43. 0.035 0.035 ↑ 1.0 8 1

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

44.          

CTE bucketec

45. 10.670 1,627.752 ↑ 1,597.2 29 1

GroupAggregate (cost=651,053.41..861,573.26 rows=46,319 width=116) (actual time=1,612.768..1,627.752 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. 86.175 1,617.082 ↑ 1.1 41,114 1

Sort (cost=651,052.51..651,168.30 rows=46,319 width=88) (actual time=1,612.725..1,617.082 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. 17.703 1,530.907 ↑ 1.1 41,114 1

Hash Left Join (cost=249,246.86..645,243.94 rows=46,319 width=88) (actual time=1,337.728..1,530.907 rows=41,114 loops=1)

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

Hash Left Join (cost=249,199.39..432,939.71 rows=46,319 width=40) (actual time=1,337.419..1,512.638 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.585 89.584 ↓ 1.3 64,726 1

Hash Join (cost=12,386.13..122,943.76 rows=48,957 width=24) (actual time=28.011..89.584 rows=64,726 loops=1)

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

Bitmap Heap Scan on t122633_opp_line_item_fact t122633_opp_line_item_fact_2 (cost=12,172.44..122,191.60 rows=64,738 width=28) (actual time=27.168..68.167 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=16711
51. 24.427 24.427 ↓ 1.1 72,680 1

Bitmap Index Scan on idx_t122633_opp_line_item_fact_c612549_opp_close_date_e30514041 (cost=0.00..12,156.25 rows=64,823 width=0) (actual time=24.427..24.427 rows=72,680 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.122 0.832 ↓ 1.5 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
53. 0.280 0.710 ↓ 1.5 875 1

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

  • Group Key: t70_territoryhierarchy_7.sid
54. 0.328 0.430 ↑ 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.112..0.430 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.102 0.102 ↑ 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.102..0.102 rows=875 loops=1)

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

Hash (cost=209,301.83..209,301.83 rows=1,383,629 width=16) (actual time=1,307.560..1,307.560 rows=901,942 loops=1)

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

Seq Scan on t122633_opp_line_item_fact t122633_opp_line_item_fact_3 (cost=0.00..209,301.83 rows=1,383,629 width=16) (actual time=0.155..1,118.766 rows=901,942 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1538377200000'::bigint) AND (end_stamp > '1538377200000'::bigint))
  • Rows Removed by Filter: 4417096
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.324 0.422 ↑ 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.108..0.422 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.098 0.098 ↑ 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.098..0.098 rows=875 loops=1)

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

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

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

Seq Scan on t166_claricurrencytypedim t166_claricurrencytypedim_2 (cost=0.00..46.05 rows=42 width=12) (actual time=0.017..0.253 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.001..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.219 0.300 ↑ 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.090..0.300 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.081 0.081 ↑ 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.081..0.081 rows=875 loops=1)

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

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

70. 0.001 0.001 ↑ 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.001 rows=8 loops=1)

71. 0.016 760.203 ↑ 36.9 23 1

Hash Join (cost=1.11..816.39 rows=849 width=96) (actual time=754.982..760.203 rows=23 loops=1)

  • Hash Cond: (cte0.c4 = t35_opportunityforecastcategorypicklistdim.sid)
72. 760.174 760.174 ↑ 1,477.0 23 1

CTE Scan on bucketac cte0 (cost=0.00..679.40 rows=33,970 width=92) (actual time=754.957..760.174 rows=23 loops=1)

73. 0.007 0.013 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.013..0.013 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.004..0.006 rows=5 loops=1)

75. 0.025 1,627.829 ↑ 39.9 29 1

Subquery Scan on *SELECT* 2 (cost=1.11..1,127.24 rows=1,158 width=96) (actual time=1,612.812..1,627.829 rows=29 loops=1)

76. 0.021 1,627.804 ↑ 39.9 29 1

Hash Join (cost=1.11..1,112.77 rows=1,158 width=120) (actual time=1,612.803..1,627.804 rows=29 loops=1)

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

CTE Scan on bucketec cte0_1 (cost=0.00..926.38 rows=46,319 width=116) (actual time=1,612.772..1,627.767 rows=29 loops=1)

78. 0.005 0.016 ↑ 1.0 5 1

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

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

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