explain.depesz.com

PostgreSQL's explain analyze made readable

Result: djI5

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 5,699.643 ↑ 1,547.4 57 1

Append (cost=2,549,151.58..2,557,612.69 rows=88,204 width=120) (actual time=2,678.662..5,699.643 rows=57 loops=1)

2.          

CTE picklist_0

3. 0.018 0.018 ↑ 1.0 2 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_2 (cost=0.00..4.80 rows=2 width=4) (actual time=0.012..0.018 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"Closed Won","Trial Won"}'::text[]))
  • Rows Removed by Filter: 62
4.          

CTE picklist_1

5. 0.027 0.027 ↑ 1.1 14 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_3 (cost=0.00..5.84 rows=15 width=4) (actual time=0.006..0.027 rows=14 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 50
6.          

CTE picklist_4

7. 0.036 0.036 ↑ 1.1 16 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_4 (cost=0.00..6.00 rows=17 width=4) (actual time=0.011..0.036 rows=16 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","Closed Won","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Trial Won","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 48
8.          

CTE ownercte

9. 2.829 17.608 ↑ 2.1 570 1

HashAggregate (cost=3,201.10..3,212.98 rows=1,188 width=4) (actual time=17.556..17.608 rows=570 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 4.027 14.779 ↓ 1.2 19,843 1

Hash Join (cost=1,136.51..3,160.69 rows=16,163 width=4) (actual time=5.186..14.779 rows=19,843 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t209_userrolehierarchy.sid)
11. 5.780 7.339 ↓ 1.2 19,843 1

Bitmap Heap Scan on distinctclosedateowner (cost=346.09..2,198.54 rows=16,163 width=4) (actual time=1.756..7.339 rows=19,843 loops=1)

  • Recheck Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
  • Heap Blocks: exact=1353
12. 1.559 1.559 ↓ 1.2 19,843 1

Bitmap Index Scan on ix_distinctclosedateowner_closedate (cost=0.00..342.05 rows=16,163 width=0) (actual time=1.559..1.559 rows=19,843 loops=1)

  • Index Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
13. 0.301 3.413 ↓ 1.4 2,111 1

Hash (cost=771.30..771.30 rows=1,529 width=4) (actual time=3.413..3.413 rows=2,111 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
14. 0.788 3.112 ↓ 1.4 2,111 1

HashAggregate (cost=756.01..771.30 rows=1,529 width=4) (actual time=2.862..3.112 rows=2,111 loops=1)

  • Group Key: t209_userrolehierarchy.sid
15. 0.631 2.324 ↓ 1.0 2,560 1

Hash Join (cost=231.84..749.67 rows=2,535 width=4) (actual time=0.853..2.324 rows=2,560 loops=1)

  • Hash Cond: (t209_userrolehierarchy.c808_user_role_id = t37_userroledim.sid)
16. 0.879 1.231 ↓ 1.0 2,560 1

Bitmap Heap Scan on t209_userrolehierarchy (cost=171.12..654.15 rows=2,535 width=8) (actual time=0.383..1.231 rows=2,560 loops=1)

  • Recheck Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=302
17. 0.352 0.352 ↓ 1.0 2,583 1

Bitmap Index Scan on ix_t209_userrolehierarchy_ancestor_timestamp (cost=0.00..170.49 rows=2,535 width=0) (actual time=0.352..0.352 rows=2,583 loops=1)

  • Index Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
18. 0.077 0.462 ↑ 1.2 437 1

Hash (cost=54.43..54.43 rows=503 width=4) (actual time=0.462..0.462 rows=437 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
19. 0.385 0.385 ↑ 1.2 437 1

Seq Scan on t37_userroledim (cost=0.00..54.43 rows=503 width=4) (actual time=0.012..0.385 rows=437 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c419_portaltype IS NULL) OR (c419_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 442
20.          

CTE bucketac

21. 90.408 2,848.655 ↑ 2,690.1 35 1

GroupAggregate (cost=1,032,336.44..1,036,808.75 rows=94,154 width=92) (actual time=2,678.610..2,848.655 rows=35 loops=1)

  • Group Key: t202_oppfact.c758_opp_stagename, (CASE WHEN (t202_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t202_oppfact_1.c781_opp_close_date < 20181001) OR (t202_oppfact_1.c781_opp_close_date > 20181231)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order = t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'unchanged'::text WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order > t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
22. 262.782 2,758.247 ↓ 2.9 274,457 1

Sort (cost=1,032,309.32..1,032,544.71 rows=94,154 width=72) (actual time=2,678.526..2,758.247 rows=274,457 loops=1)

  • Sort Key: t202_oppfact.c758_opp_stagename, (CASE WHEN (t202_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t202_oppfact_1.c781_opp_close_date < 20181001) OR (t202_oppfact_1.c781_opp_close_date > 20181231)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order = t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'unchanged'::text WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order > t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 17296kB
23. 166.971 2,495.465 ↓ 2.9 274,457 1

Hash Left Join (cost=1,015,592.13..1,020,666.92 rows=94,154 width=72) (actual time=2,106.931..2,495.465 rows=274,457 loops=1)

  • Hash Cond: (t202_oppfact_1.c758_opp_stagename = t205_opportunitystagenamepicklistdim_6.sid)
24. 64.677 2,328.378 ↓ 2.9 274,457 1

Hash Left Join (cost=1,015,559.58..1,017,692.05 rows=94,154 width=60) (actual time=2,106.669..2,328.378 rows=274,457 loops=1)

  • Hash Cond: (t202_oppfact.c758_opp_stagename = t205_opportunitystagenamepicklistdim_5.sid)
25. 91.024 2,263.669 ↓ 2.9 274,457 1

Merge Left Join (cost=1,015,554.14..1,016,391.99 rows=94,154 width=56) (actual time=2,106.625..2,263.669 rows=274,457 loops=1)

  • Merge Cond: ((t202_oppfact_1.c765_opp_currency_code = cur290_t1_r1.sid) AND (t202_oppfact_1.c781_opp_close_date = cur290_t1_r1.rate_date))
26. 328.467 2,141.274 ↓ 2.9 274,457 1

Sort (cost=1,014,245.71..1,014,481.10 rows=94,154 width=52) (actual time=2,097.252..2,141.274 rows=274,457 loops=1)

  • Sort Key: t202_oppfact_1.c765_opp_currency_code, t202_oppfact_1.c781_opp_close_date
  • Sort Method: external merge Disk: 18784kB
27. 155.648 1,812.807 ↓ 2.9 274,457 1

Nested Loop Left Join (cost=350,619.61..1,003,247.30 rows=94,154 width=52) (actual time=655.554..1,812.807 rows=274,457 loops=1)

28. 90.114 833.788 ↓ 2.9 274,457 1

Merge Left Join (cost=350,619.04..351,456.90 rows=94,154 width=24) (actual time=655.539..833.788 rows=274,457 loops=1)

  • Merge Cond: ((t202_oppfact.c765_opp_currency_code = cur290_t0_r0.sid) AND (t202_oppfact.c781_opp_close_date = cur290_t0_r0.rate_date))
29. 213.440 708.101 ↓ 2.9 274,457 1

Sort (cost=349,310.62..349,546.00 rows=94,154 width=24) (actual time=645.895..708.101 rows=274,457 loops=1)

  • Sort Key: t202_oppfact.c765_opp_currency_code, t202_oppfact.c781_opp_close_date
  • Sort Method: external merge Disk: 10160kB
30. 35.539 494.661 ↓ 2.9 274,457 1

Nested Loop (cost=27.68..339,600.21 rows=94,154 width=24) (actual time=17.940..494.661 rows=274,457 loops=1)

31. 0.229 17.942 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=17.850..17.942 rows=570 loops=1)

  • Group Key: ownercte_1.c1
32. 17.713 17.713 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_1 (cost=0.00..23.76 rows=1,188 width=4) (actual time=17.556..17.713 rows=570 loops=1)

33. 441.136 441.180 ↓ 2.0 482 570

Index Scan using ix_t202_oppfact_owner_closedate_stage_timestamp on t202_oppfact (cost=0.95..1,695.41 rows=245 width=28) (actual time=0.013..0.774 rows=482 loops=570)

  • Index Cond: ((c725_opp_ownerid = ownercte_1.c1) AND (c781_opp_close_date >= 20181001) AND (c781_opp_close_date <= 20181231) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 311
34.          

SubPlan (forIndex Scan)

35. 0.044 0.044 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.34 rows=17 width=4) (actual time=0.011..0.044 rows=16 loops=1)

36. 31.758 35.573 ↓ 20.0 287,606 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.421..35.573 rows=287,606 loops=1)

  • Sort Key: cur290_t0_r0.sid, cur290_t0_r0.rate_date
  • Sort Method: quicksort Memory: 1057kB
37. 0.839 3.815 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.018..3.815 rows=14,351 loops=1)

38. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
39. 2.974 2.974 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.015..2.974 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
40. 823.371 823.371 ↑ 1.0 1 274,457

Index Scan using ix_t202_oppfact_sid_timestamp on t202_oppfact t202_oppfact_1 (cost=0.56..6.91 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=274,457)

  • Index Cond: ((t202_oppfact.sid = sid) AND (t202_oppfact.sid = sid) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
41. 27.714 31.371 ↓ 20.0 287,719 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.148..31.371 rows=287,719 loops=1)

  • Sort Key: cur290_t1_r1.sid, cur290_t1_r1.rate_date
  • Sort Method: quicksort Memory: 1057kB
42. 0.900 3.657 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.015..3.657 rows=14,351 loops=1)

43. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
44. 2.755 2.755 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t1_r1_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.013..2.755 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
45. 0.010 0.032 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=8) (actual time=0.032..0.032 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
46. 0.022 0.022 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_5 (cost=0.00..4.64 rows=64 width=8) (actual time=0.004..0.022 rows=64 loops=1)

47. 0.011 0.022 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=8) (actual time=0.022..0.022 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
48. 0.011 0.011 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_6 (cost=0.00..4.64 rows=64 width=8) (actual time=0.001..0.011 rows=64 loops=1)

49.          

SubPlan (forHash Left Join)

50. 0.043 0.043 ↑ 2.1 570 1

CTE Scan on ownercte (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.002..0.043 rows=570 loops=1)

51. 0.021 0.021 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.04 rows=2 width=4) (actual time=0.014..0.021 rows=2 loops=1)

52. 0.030 0.030 ↑ 1.1 14 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.30 rows=15 width=4) (actual time=0.008..0.030 rows=14 loops=1)

53.          

CTE bucketec

54. 17.130 2,850.795 ↑ 8,249.3 22 1

GroupAggregate (cost=1,501,394.70..1,509,107.77 rows=181,484 width=116) (actual time=2,826.305..2,850.795 rows=22 loops=1)

  • Group Key: (CASE WHEN (t202_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t202_oppfact_3.c781_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t202_oppfact_3.c781_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t202_oppfact_2.c758_opp_stagename
55. 95.834 2,833.665 ↑ 2.8 64,760 1

Sort (cost=1,501,367.21..1,501,820.92 rows=181,484 width=88) (actual time=2,826.287..2,833.665 rows=64,760 loops=1)

  • Sort Key: (CASE WHEN (t202_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t202_oppfact_3.c781_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t202_oppfact_3.c781_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t202_oppfact_2.c758_opp_stagename
  • Sort Method: external merge Disk: 3576kB
56. 162.393 2,737.831 ↑ 2.8 64,760 1

Nested Loop Left Join (cost=358,848.17..1,476,828.05 rows=181,484 width=88) (actual time=753.240..2,737.831 rows=64,760 loops=1)

  • Filter: ((t202_oppfact_3.sid IS NULL) OR (t202_oppfact_3.c781_opp_close_date < 20181001) OR (t202_oppfact_3.c781_opp_close_date > 20181231) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 449712
57. 153.530 1,031.981 ↓ 2.8 514,472 1

Merge Left Join (cost=358,793.38..360,350.14 rows=186,883 width=24) (actual time=695.337..1,031.981 rows=514,472 loops=1)

  • Merge Cond: ((t202_oppfact_2.c765_opp_currency_code = cur290_t0_r0_2.sid) AND (t202_oppfact_2.c781_opp_close_date = cur290_t0_r0_2.rate_date))
58. 330.923 825.104 ↓ 2.8 514,472 1

Sort (cost=357,484.95..357,952.16 rows=186,883 width=24) (actual time=685.847..825.104 rows=514,472 loops=1)

  • Sort Key: t202_oppfact_2.c765_opp_currency_code, t202_oppfact_2.c781_opp_close_date
  • Sort Method: external merge Disk: 19032kB
59. 102.895 494.181 ↓ 2.8 514,472 1

Nested Loop (cost=27.30..337,285.69 rows=186,883 width=24) (actual time=0.203..494.181 rows=514,472 loops=1)

60. 0.214 0.266 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=0.163..0.266 rows=570 loops=1)

  • Group Key: ownercte_4.c1
61. 0.052 0.052 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_4 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.000..0.052 rows=570 loops=1)

62. 391.020 391.020 ↓ 1.9 903 570

Index Scan using ix_t202_oppfact_owner_closedate_stage_timestamp on t202_oppfact t202_oppfact_2 (cost=0.56..1,681.41 rows=487 width=28) (actual time=0.009..0.686 rows=903 loops=570)

  • Index Cond: ((c725_opp_ownerid = ownercte_4.c1) AND (c781_opp_close_date >= 20181001) AND (c781_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4
63. 49.663 53.347 ↓ 36.7 527,621 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.227..53.347 rows=527,621 loops=1)

  • Sort Key: cur290_t0_r0_2.sid, cur290_t0_r0_2.rate_date
  • Sort Method: quicksort Memory: 1057kB
64. 0.865 3.684 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.019..3.684 rows=14,351 loops=1)

65. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
66. 2.816 2.816 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_3 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.015..2.816 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
67. 1,543.416 1,543.416 ↑ 1.0 1 514,472

Index Scan using ix_t202_oppfact_sid_timestamp on t202_oppfact t202_oppfact_3 (cost=0.56..5.94 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=514,472)

  • Index Cond: ((t202_oppfact_2.sid = sid) AND (t202_oppfact_2.sid = sid) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Filter: (NOT deleted)
68.          

SubPlan (forNested Loop Left Join)

69. 0.001 0.001 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.34 rows=17 width=4) (actual time=0.000..0.001 rows=16 loops=1)

70. 0.000 0.000 ↓ 0.0 0

CTE Scan on ownercte ownercte_2 (cost=0.00..23.76 rows=1,188 width=4) (never executed)

71. 0.001 0.001 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.001 rows=2 loops=1)

72. 0.002 0.002 ↑ 1.1 14 1

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

73. 0.037 0.037 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_3 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.037 rows=570 loops=1)

74. 0.022 2,848.737 ↑ 860.8 35 1

Hash Join (cost=5.44..2,542.89 rows=30,129 width=107) (actual time=2,678.662..2,848.737 rows=35 loops=1)

  • Hash Cond: (cte0.c4 = t205_opportunitystagenamepicklistdim.sid)
75. 2,848.678 2,848.678 ↑ 2,690.1 35 1

CTE Scan on bucketac cte0 (cost=0.00..1,883.08 rows=94,154 width=92) (actual time=2,678.614..2,848.678 rows=35 loops=1)

76. 0.010 0.037 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.037..0.037 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
77. 0.027 0.027 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim (cost=0.00..4.64 rows=64 width=23) (actual time=0.009..0.027 rows=64 loops=1)

78. 0.011 2,850.900 ↑ 2,639.8 22 1

Subquery Scan on *SELECT* 2 (cost=5.44..5,622.37 rows=58,075 width=107) (actual time=2,826.363..2,850.900 rows=22 loops=1)

79. 0.042 2,850.889 ↑ 2,639.8 22 1

Hash Join (cost=5.44..4,896.44 rows=58,075 width=131) (actual time=2,826.357..2,850.889 rows=22 loops=1)

  • Hash Cond: (cte0_1.c6 = t205_opportunitystagenamepicklistdim_1.sid)
80. 2,850.810 2,850.810 ↑ 8,249.3 22 1

CTE Scan on bucketec cte0_1 (cost=0.00..3,629.68 rows=181,484 width=116) (actual time=2,826.307..2,850.810 rows=22 loops=1)

81. 0.012 0.037 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.037..0.037 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
82. 0.025 0.025 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_1 (cost=0.00..4.64 rows=64 width=23) (actual time=0.008..0.025 rows=64 loops=1)