explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6eEi

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 2,176.445 ↑ 35.8 53 1

Append (cost=1,494,963.31..1,496,807.82 rows=1,898 width=120) (actual time=401.724..2,176.445 rows=53 loops=1)

2.          

CTE picklist_0

3. 0.040 0.040 ↑ 1.1 11 1

Seq Scan on t34_opportunitystagenamepicklistdim (cost=0.00..14.40 rows=12 width=4) (actual time=0.008..0.040 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.033 0.033 ↑ 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.033 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.065 0.065 ↑ 1.1 19 1

Seq Scan on t34_opportunitystagenamepicklistdim t34_opportunitystagenamepicklistdim_2 (cost=0.00..15.76 rows=20 width=4) (actual time=0.017..0.065 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. 15.079 422.657 ↑ 1,334.7 24 1

GroupAggregate (cost=500,651.92..646,397.52 rows=32,032 width=92) (actual time=401.696..422.657 rows=24 loops=1)

  • Group Key: t122633_opp_line_item_fact.c612605_opp_forecastcategory, (CASE WHEN (t122633_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t122633_opp_line_item_fact_1.c612549_opp_close_date < 20181001) OR (t122633_opp_line_item_fact_1.c612549_opp_close_date > 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 = t35_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t35_opportunityforecastcategorypicklistdim_2.correlated_app_order > t35_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10. 69.583 407.578 ↓ 1.5 49,631 1

Sort (cost=500,651.47..500,731.55 rows=32,032 width=72) (actual time=401.652..407.578 rows=49,631 loops=1)

  • Sort Key: t122633_opp_line_item_fact.c612605_opp_forecastcategory, (CASE WHEN (t122633_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t122633_opp_line_item_fact_1.c612549_opp_close_date < 20181001) OR (t122633_opp_line_item_fact_1.c612549_opp_close_date > 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 = t35_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t35_opportunityforecastcategorypicklistdim_2.correlated_app_order > t35_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 2832kB
11. 28.862 337.995 ↓ 1.5 49,631 1

Hash Left Join (cost=12,555.82..498,254.32 rows=32,032 width=72) (actual time=33.805..337.995 rows=49,631 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact_1.c612605_opp_forecastcategory = t35_opportunityforecastcategorypicklistdim_3.sid)
12. 12.609 308.692 ↓ 1.5 49,631 1

Hash Left Join (cost=12,554.26..353,028.52 rows=32,032 width=64) (actual time=33.784..308.692 rows=49,631 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact_1.c612608_opp_currency_code = t166_claricurrencytypedim_1.sid)
13. 34.084 295.852 ↓ 1.5 49,631 1

Nested Loop Left Join (cost=12,507.68..350,899.86 rows=32,032 width=60) (actual time=33.542..295.852 rows=49,631 loops=1)

14. 10.679 112.875 ↓ 1.5 49,631 1

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

  • Hash Cond: (t122633_opp_line_item_fact.c612605_opp_forecastcategory = t35_opportunityforecastcategorypicklistdim_2.sid)
15. 10.257 102.190 ↓ 1.5 49,631 1

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

  • Hash Cond: (t122633_opp_line_item_fact.c612608_opp_currency_code = t166_claricurrencytypedim.sid)
16. 12.162 91.654 ↓ 1.5 49,631 1

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

  • Hash Cond: (t122633_opp_line_item_fact.c612612_opp_territory_id = t70_territoryhierarchy_2.sid)
17. 48.951 78.714 ↓ 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=32.437..78.714 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
18. 29.692 29.692 ↑ 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.692..29.692 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))
19.          

SubPlan (forBitmap Heap Scan)

20. 0.071 0.071 ↑ 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.071 rows=19 loops=1)

21. 0.118 0.778 ↓ 1.5 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
22. 0.230 0.660 ↓ 1.5 875 1

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

  • Group Key: t70_territoryhierarchy_2.sid
23. 0.340 0.430 ↑ 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.101..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
24. 0.090 0.090 ↑ 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.090..0.090 rows=875 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.268 0.268 ↑ 1.0 42 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1722
27. 0.003 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
28. 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)

29. 148.893 148.893 ↑ 1.0 1 49,631

Index Scan using idx_t122633_opp_line_item_fact_sid_end_stamp_start_stamp on t122633_opp_line_item_fact t122633_opp_line_item_fact_1 (cost=0.56..6.36 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=49,631)

  • Index Cond: ((t122633_opp_line_item_fact.sid = sid) AND (t122633_opp_line_item_fact.sid = sid) AND (end_stamp > '1546243199999'::bigint) AND (start_stamp <= '1546243199999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
30. 0.006 0.231 ↑ 1.0 42 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 0.225 0.225 ↑ 1.0 42 1

Seq Scan on t166_claricurrencytypedim t166_claricurrencytypedim_1 (cost=0.00..46.05 rows=42 width=12) (actual time=0.009..0.225 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1722
32. 0.003 0.005 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.002 0.002 ↑ 1.0 5 1

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

34.          

SubPlan (forHash Left Join)

35. 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 = t122633_opp_line_item_fact_1.c612612_opp_territory_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[]))
  • Heap Fetches: 0
36. 0.277 0.359 ↑ 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.359 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
37. 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))
38. 0.044 0.044 ↑ 1.1 11 1

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

39. 0.033 0.033 ↑ 1.0 8 1

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

40.          

CTE bucketec

41. 10.656 1,753.669 ↑ 1,512.8 29 1

GroupAggregate (cost=649,127.11..848,520.80 rows=43,871 width=116) (actual time=1,738.648..1,753.669 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
42. 84.313 1,743.013 ↑ 1.1 41,114 1

Sort (cost=649,126.21..649,235.88 rows=43,871 width=88) (actual time=1,738.608..1,743.013 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
43. 17.495 1,658.700 ↑ 1.1 41,114 1

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

  • Hash Cond: (t122633_opp_line_item_fact_2.c612608_opp_currency_code = t166_claricurrencytypedim_2.sid)
44. 107.735 1,640.548 ↑ 1.1 41,114 1

Hash Left Join (cost=260,626.52..442,557.20 rows=43,871 width=40) (actual time=1,457.080..1,640.548 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
45. 20.823 115.592 ↓ 1.4 64,726 1

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

  • Hash Cond: (t122633_opp_line_item_fact_2.c612612_opp_territory_id = t70_territoryhierarchy_7.sid)
46. 62.245 94.020 ↓ 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.457..94.020 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
47. 31.775 31.775 ↓ 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.775..31.775 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))
48. 0.105 0.749 ↓ 1.5 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
49. 0.229 0.644 ↓ 1.5 875 1

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

  • Group Key: t70_territoryhierarchy_7.sid
50. 0.327 0.415 ↑ 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.415 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
51. 0.088 0.088 ↑ 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.088..0.088 rows=875 loops=1)

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

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

  • Buckets: 131072 Batches: 32 Memory Usage: 2298kB
53. 1,238.916 1,238.916 ↑ 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.176..1,238.916 rows=901,942 loops=1)

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

SubPlan (forHash Left Join)

55. 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
56. 0.309 0.403 ↑ 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.105..0.403 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
57. 0.094 0.094 ↑ 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.094..0.094 rows=875 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
59. 0.276 0.276 ↑ 1.0 42 1

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

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

SubPlan (forHash Left Join)

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

62. 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
63. 0.278 0.363 ↑ 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.363 rows=875 loops=1)

  • Recheck Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=80
64. 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))
65. 0.001 0.001 ↑ 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.001 rows=11 loops=1)

66. 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)

67. 0.022 422.706 ↑ 33.4 24 1

Hash Join (cost=1.11..769.88 rows=801 width=96) (actual time=401.724..422.706 rows=24 loops=1)

  • Hash Cond: (cte0.c4 = t35_opportunityforecastcategorypicklistdim.sid)
68. 422.671 422.671 ↑ 1,334.7 24 1

CTE Scan on bucketac cte0 (cost=0.00..640.64 rows=32,032 width=92) (actual time=401.699..422.671 rows=24 loops=1)

69. 0.005 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
70. 0.008 0.008 ↑ 1.0 5 1

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

71. 0.013 1,753.729 ↑ 37.8 29 1

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

72. 0.020 1,753.716 ↑ 37.8 29 1

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

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

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

74. 0.004 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
75. 0.009 0.009 ↑ 1.0 5 1

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