explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c2u

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,139.582 ↑ 38.1 53 1

Append (cost=1,520,415.56..1,522,379.35 rows=2,020 width=120) (actual time=375.133..2,139.582 rows=53 loops=1)

2.          

CTE picklist_0

3. 0.039 0.039 ↑ 1.1 11 1

Seq Scan on t34_opportunitystagenamepicklistdim (cost=0.00..14.40 rows=12 width=4) (actual time=0.008..0.039 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.006..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.017..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. 15.654 396.674 ↑ 1,425.0 24 1

GroupAggregate (cost=497,152.98..652,762.98 rows=34,200 width=92) (actual time=375.106..396.674 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.680 381.020 ↓ 1.5 49,631 1

Sort (cost=497,152.53..497,238.03 rows=34,200 width=72) (actual time=375.065..381.020 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: 2840kB
11. 28.391 311.340 ↓ 1.5 49,631 1

Hash Left Join (cost=12,605.13..494,576.98 rows=34,200 width=72) (actual time=30.041..311.340 rows=49,631 loops=1)

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

Hash Left Join (cost=12,603.57..339,522.14 rows=34,200 width=64) (actual time=30.030..282.557 rows=49,631 loops=1)

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

Nested Loop Left Join (cost=12,556.99..337,252.61 rows=34,200 width=60) (actual time=29.808..269.537 rows=49,631 loops=1)

14. 11.430 110.995 ↓ 1.5 49,631 1

Hash Left Join (cost=12,556.56..141,391.96 rows=34,200 width=28) (actual time=29.794..110.995 rows=49,631 loops=1)

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

Hash Left Join (cost=12,555.45..140,921.57 rows=34,200 width=24) (actual time=29.783..99.559 rows=49,631 loops=1)

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

Hash Join (cost=12,508.87..138,652.04 rows=34,200 width=20) (actual time=29.494..88.402 rows=49,631 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact.c612612_opp_territory_id = t70_territoryhierarchy_2.sid)
17. 49.356 75.459 ↓ 1.2 52,818 1

Bitmap Heap Scan on t122633_opp_line_item_fact (cost=12,295.18..138,062.19 rows=45,224 width=24) (actual time=28.717..75.459 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=15034
18. 26.029 26.029 ↑ 1.7 54,645 1

Bitmap Index Scan on idx_t122633_opp_line_item_fact_c612549_opp_close_date_e30514041 (cost=0.00..12,283.43 rows=90,565 width=0) (actual time=26.029..26.029 rows=54,645 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.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.019..0.074 rows=19 loops=1)

21. 0.116 0.764 ↓ 1.5 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
22. 0.232 0.648 ↓ 1.5 875 1

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

  • Group Key: t70_territoryhierarchy_2.sid
23. 0.326 0.416 ↑ 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.100..0.416 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.007 0.283 ↑ 1.0 42 1

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

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

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

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

29. 148.893 148.893 ↑ 1.0 1 49,631

Index Scan using t122633_opp_line_item_fact_sidendstampunique on t122633_opp_line_item_fact t122633_opp_line_item_fact_1 (cost=0.43..5.72 rows=1 width=32) (actual time=0.002..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))
  • Filter: ((NOT deleted) AND (start_stamp <= '1546243199999'::bigint))
  • Rows Removed by Filter: 0
30. 0.007 0.217 ↑ 1.0 42 1

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

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

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

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

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.004..0.004 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.238 0.312 ↑ 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.098..0.312 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.074 0.074 ↑ 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.074..0.074 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.032 0.032 ↑ 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.032 rows=8 loops=1)

40.          

CTE bucketec

41. 10.711 1,742.779 ↑ 1,607.4 29 1

GroupAggregate (cost=655,742.41..867,607.59 rows=46,615 width=116) (actual time=1,727.422..1,742.779 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. 86.768 1,732.068 ↑ 1.1 41,114 1

Sort (cost=655,741.51..655,858.05 rows=46,615 width=88) (actual time=1,727.367..1,732.068 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.475 1,645.300 ↑ 1.1 41,114 1

Hash Left Join (cost=250,970.96..649,893.87 rows=46,615 width=88) (actual time=1,452.056..1,645.300 rows=41,114 loops=1)

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

Hash Left Join (cost=250,923.49..436,233.22 rows=46,615 width=40) (actual time=1,451.699..1,627.246 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.784 90.564 ↓ 1.3 64,726 1

Hash Join (cost=12,513.40..123,799.58 rows=49,271 width=24) (actual time=27.323..90.564 rows=64,726 loops=1)

  • Hash Cond: (t122633_opp_line_item_fact_2.c612612_opp_territory_id = t70_territoryhierarchy_7.sid)
46. 45.307 69.034 ↓ 1.1 69,455 1

Bitmap Heap Scan on t122633_opp_line_item_fact t122633_opp_line_item_fact_2 (cost=12,299.72..123,043.97 rows=65,153 width=28) (actual time=26.567..69.034 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=16803
47. 23.727 23.727 ↓ 1.1 73,420 1

Bitmap Index Scan on idx_t122633_opp_line_item_fact_c612549_opp_close_date_e30514041 (cost=0.00..12,283.43 rows=65,238 width=0) (actual time=23.727..23.727 rows=73,420 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.113 0.746 ↓ 1.5 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
49. 0.230 0.633 ↓ 1.5 875 1

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

  • Group Key: t70_territoryhierarchy_7.sid
50. 0.316 0.403 ↑ 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.098..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
51. 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))
52. 182.394 1,422.387 ↑ 1.5 901,942 1

Hash (cost=210,713.65..210,713.65 rows=1,392,962 width=16) (actual time=1,422.387..1,422.387 rows=901,942 loops=1)

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

Seq Scan on t122633_opp_line_item_fact t122633_opp_line_item_fact_3 (cost=0.00..210,713.65 rows=1,392,962 width=16) (actual time=0.174..1,239.993 rows=901,942 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1538377200000'::bigint) AND (end_stamp > '1538377200000'::bigint))
  • Rows Removed by Filter: 4425376
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.271 0.378 ↑ 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.119..0.378 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.107 0.107 ↑ 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.107..0.107 rows=875 loops=1)

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

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

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

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

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

SubPlan (forHash Left Join)

61. 0.003 0.003 ↑ 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.003 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.184 0.261 ↑ 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.086..0.261 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.077 0.077 ↑ 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.077..0.077 rows=875 loops=1)

  • Index Cond: ((c412_ancestor_territory_id = ANY ('{305,987,252}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
65. 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)

66. 0.003 0.003 ↑ 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.003 rows=8 loops=1)

67. 0.023 396.724 ↑ 35.6 24 1

Hash Join (cost=1.11..821.91 rows=855 width=96) (actual time=375.132..396.724 rows=24 loops=1)

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

CTE Scan on bucketac cte0 (cost=0.00..684.00 rows=34,200 width=92) (actual time=375.109..396.689 rows=24 loops=1)

69. 0.003 0.012 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 0.009 0.009 ↑ 1.0 5 1

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

71. 0.020 1,742.854 ↑ 40.2 29 1

Subquery Scan on *SELECT* 2 (cost=1.11..1,134.43 rows=1,165 width=96) (actual time=1,727.460..1,742.854 rows=29 loops=1)

72. 0.027 1,742.834 ↑ 40.2 29 1

Hash Join (cost=1.11..1,119.87 rows=1,165 width=120) (actual time=1,727.452..1,742.834 rows=29 loops=1)

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

CTE Scan on bucketec cte0_1 (cost=0.00..932.30 rows=46,615 width=116) (actual time=1,727.424..1,742.793 rows=29 loops=1)

74. 0.005 0.014 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.014..0.014 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.007..0.009 rows=5 loops=1)