explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tgit

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 9,893.694 ↑ 1.0 400 1

Limit (cost=2,534,254.13..2,534,255.13 rows=400 width=27) (actual time=9,893.648..9,893.694 rows=400 loops=1)

2.          

CTE docd_cte_0

3. 1.197 33.023 ↑ 2.1 616 1

Unique (cost=18,964.47..18,976.83 rows=1,278 width=4) (actual time=30.569..33.023 rows=616 loops=1)

4. 4.678 31.826 ↓ 8.3 20,437 1

Sort (cost=18,964.47..18,970.65 rows=2,472 width=4) (actual time=30.568..31.826 rows=20,437 loops=1)

  • Sort Key: t564_distinctcdo_opp_closedate_ownerid.c1890_ownerid
  • Sort Method: quicksort Memory: 1726kB
5. 3.259 27.148 ↓ 8.3 20,437 1

Nested Loop (cost=8,130.87..18,825.15 rows=2,472 width=4) (actual time=15.341..27.148 rows=20,437 loops=1)

6. 1.061 15.753 ↓ 1.1 2,712 1

HashAggregate (cost=8,130.45..8,154.58 rows=2,413 width=4) (actual time=15.325..15.753 rows=2,712 loops=1)

  • Group Key: t398_userrolehierarchy.sid
7. 0.193 14.692 ↓ 1.3 3,114 1

Append (cost=1,616.97..8,124.42 rows=2,413 width=4) (actual time=3.135..14.692 rows=3,114 loops=1)

8. 3.881 14.305 ↓ 1.3 3,005 1

Hash Semi Join (cost=1,616.97..7,842.89 rows=2,402 width=4) (actual time=3.135..14.305 rows=3,005 loops=1)

  • Hash Cond: (t398_userrolehierarchy.c1390_user_role_id = t629_userroledim.sid)
9. 7.661 10.113 ↓ 1.1 22,943 1

Bitmap Heap Scan on t398_userrolehierarchy (cost=1,092.36..7,223.21 rows=21,457 width=8) (actual time=2.796..10.113 rows=22,943 loops=1)

  • Recheck Cond: ((c1391_ancestor_role_id = 30) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=2657
10. 2.452 2.452 ↓ 1.1 22,959 1

Bitmap Index Scan on idx_t398_userrolehierarchy_c1391_ancestor_role_id_end_s36986549 (cost=0.00..1,086.99 rows=21,457 width=0) (actual time=2.452..2.452 rows=22,959 loops=1)

  • Index Cond: ((c1391_ancestor_role_id = 30) AND (end_stamp = '32503680000000'::bigint))
11. 0.055 0.311 ↑ 2.5 331 1

Hash (cost=514.13..514.13 rows=839 width=4) (actual time=0.311..0.311 rows=331 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
12. 0.195 0.256 ↑ 2.5 331 1

Bitmap Heap Scan on t629_userroledim (cost=41.14..514.13 rows=839 width=4) (actual time=0.074..0.256 rows=331 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c1972_portaltype IS NULL) OR (c1972_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 190
  • Heap Blocks: exact=41
13. 0.061 0.061 ↑ 1.6 521 1

Bitmap Index Scan on idx_6_t629_userroledim_sid_timestamp_partial (cost=0.00..40.93 rows=852 width=0) (actual time=0.061..0.061 rows=521 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
14. 0.164 0.194 ↓ 9.9 109 1

Bitmap Heap Scan on t628_userdim (cost=5.12..257.40 rows=11 width=4) (actual time=0.051..0.194 rows=109 loops=1)

  • Recheck Cond: ((c1951_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1954_usertype = 'Standard'::text))
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=73
15. 0.030 0.030 ↓ 1.7 121 1

Bitmap Index Scan on idx_8_t628_userdim_c1951_userroleid_end_stamp_start_stamp_null (cost=0.00..5.12 rows=70 width=0) (actual time=0.030..0.030 rows=121 loops=1)

  • Index Cond: ((c1951_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
16. 8.136 8.136 ↓ 2.0 8 2,712

Index Only Scan using t564_distinctcdo_opp_closedate_ownerid__distinct_unique on t564_distinctcdo_opp_closedate_ownerid (cost=0.42..4.37 rows=4 width=4) (actual time=0.003..0.003 rows=8 loops=2,712)

  • Index Cond: ((c1890_ownerid = t398_userrolehierarchy.sid) AND (c1891_closedate >= 20191001) AND (c1891_closedate <= 20191231))
  • Heap Fetches: 834
17.          

CTE picklist_1

18. 0.021 0.021 ↑ 1.0 2 1

Seq Scan on t563_opportunitystagenamepicklistdim (cost=0.00..5.81 rows=2 width=4) (actual time=0.012..0.021 rows=2 loops=1)

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

CTE picklist_2

20. 0.033 0.033 ↑ 1.1 17 1

Seq Scan on t563_opportunitystagenamepicklistdim t563_opportunitystagenamepicklistdim_1 (cost=0.00..7.11 rows=18 width=4) (actual time=0.007..0.033 rows=17 loops=1)

  • Filter: (correlated_value = ANY ('{"10 - Disqualified","16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","16 - Closed / Invalid or Duplicate Oppor","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","14 - Closed / Mid Cycle Qualify Ou","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: 48
21.          

CTE picklist_4

22. 0.055 0.055 ↑ 1.1 19 1

Seq Scan on t563_opportunitystagenamepicklistdim t563_opportunitystagenamepicklistdim_2 (cost=0.00..7.28 rows=20 width=4) (actual time=0.015..0.055 rows=19 loops=1)

  • Filter: (correlated_value = ANY ('{"10 - Disqualified","16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","16 - Closed / Invalid or Duplicate Oppor","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","14 - Closed / Mid Cycle Qualify Ou","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: 46
23.          

CTE topsids

24. 0.033 9,888.583 ↑ 1.0 400 1

Limit (cost=2,502,501.58..2,502,509.45 rows=400 width=12) (actual time=9,888.426..9,888.583 rows=400 loops=1)

25.          

CTE bucketac

26. 312.187 3,426.448 ↓ 4.1 373,591 1

Nested Loop Left Join (cost=974,617.34..982,034.36 rows=90,922 width=116) (actual time=2,698.053..3,426.448 rows=373,591 loops=1)

27.          

CTE nullgroupbyvaluecte

28. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on t563_opportunitystagenamepicklistdim t563_opportunitystagenamepicklistdim_3 (cost=0.00..5.81 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 65
29. 80.126 3,114.132 ↓ 4.1 373,591 1

Hash Left Join (cost=974,582.32..977,907.84 rows=90,922 width=44) (actual time=2,698.024..3,114.132 rows=373,591 loops=1)

  • Hash Cond: (t531_oppfact_2.c1780_opp_stagename = t563_opportunitystagenamepicklistdim_5.sid)
30. 84.561 3,033.981 ↓ 4.1 373,591 1

Hash Left Join (cost=974,575.86..976,651.20 rows=90,922 width=40) (actual time=2,697.990..3,033.981 rows=373,591 loops=1)

  • Hash Cond: (t531_oppfact_1.c1780_opp_stagename = t563_opportunitystagenamepicklistdim_4.sid)
31. 116.827 2,949.382 ↓ 4.1 373,591 1

Merge Left Join (cost=974,569.39..975,394.56 rows=90,922 width=36) (actual time=2,697.925..2,949.382 rows=373,591 loops=1)

  • Merge Cond: ((t531_oppfact_2.c1782_opp_currency_code = t534_clari_conversion_rate.sid) AND (t531_oppfact_2.c1786_opp_close_date = t534_clari_conversion_rate.rate_date))
32. 314.702 2,786.825 ↓ 4.1 373,591 1

Sort (cost=973,047.86..973,275.16 rows=90,922 width=32) (actual time=2,685.218..2,786.825 rows=373,591 loops=1)

  • Sort Key: t531_oppfact_2.c1782_opp_currency_code, t531_oppfact_2.c1786_opp_close_date
  • Sort Method: external merge Disk: 15320kB
33. 4.281 2,472.123 ↓ 4.1 373,591 1

Nested Loop Left Join (cost=30.34..963,382.37 rows=90,922 width=32) (actual time=33.387..2,472.123 rows=373,591 loops=1)

34. 37.506 973.478 ↓ 4.1 373,591 1

Nested Loop (cost=29.77..287,354.59 rows=90,922 width=8) (actual time=33.369..973.478 rows=373,591 loops=1)

35. 0.414 33.532 ↓ 3.1 616 1

HashAggregate (cost=28.76..30.76 rows=200 width=4) (actual time=33.253..33.532 rows=616 loops=1)

  • Group Key: cte0_6.c1
36. 33.118 33.118 ↑ 2.1 616 1

CTE Scan on docd_cte_0 cte0_6 (cost=0.00..25.56 rows=1,278 width=4) (actual time=30.570..33.118 rows=616 loops=1)

37. 902.382 902.440 ↓ 2.9 606 616

Index Scan using ix_t531_oppfact_notnullowner_closedate_timestamp on t531_oppfact t531_oppfact_1 (cost=1.01..1,434.52 rows=210 width=12) (actual time=0.032..1.465 rows=606 loops=616)

  • Index Cond: ((c1791_opp_ownerid = cte0_6.c1) AND (c1786_opp_close_date >= 20191001) AND (c1786_opp_close_date <= 20191231) AND (end_stamp > '1574236800000'::bigint) AND (start_stamp <= '1574236800000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 787
38.          

SubPlan (for Index Scan)

39. 0.058 0.058 ↑ 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.058 rows=19 loops=1)

40. 1,494.364 1,494.364 ↑ 1.0 1 373,591

Index Scan using t531_oppfact_sidendstampunique on t531_oppfact t531_oppfact_2 (cost=0.56..7.43 rows=1 width=28) (actual time=0.003..0.004 rows=1 loops=373,591)

  • Index Cond: ((t531_oppfact_1.sid = sid) AND (t531_oppfact_1.sid = sid) AND (end_stamp > '1574927999999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1574927999999'::bigint))
  • Rows Removed by Filter: 0
41. 41.024 45.730 ↓ 24.3 389,129 1

Sort (cost=1,521.53..1,561.51 rows=15,992 width=16) (actual time=12.434..45.730 rows=389,129 loops=1)

  • Sort Key: t534_clari_conversion_rate.sid, t534_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 1545kB
42. 1.125 4.706 ↓ 1.0 16,575 1

Append (cost=0.00..404.89 rows=15,992 width=16) (actual time=0.024..4.706 rows=16,575 loops=1)

43. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on t534_clari_conversion_rate (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)
44. 3.578 3.578 ↓ 1.0 16,575 1

Seq Scan on t534_clari_conversion_rate_usd (cost=0.00..404.89 rows=15,991 width=16) (actual time=0.020..3.578 rows=16,575 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
45. 0.014 0.038 ↑ 1.0 65 1

Hash (cost=5.65..5.65 rows=65 width=8) (actual time=0.038..0.038 rows=65 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
46. 0.024 0.024 ↑ 1.0 65 1

Seq Scan on t563_opportunitystagenamepicklistdim t563_opportunitystagenamepicklistdim_4 (cost=0.00..5.65 rows=65 width=8) (actual time=0.006..0.024 rows=65 loops=1)

47. 0.008 0.025 ↑ 1.0 65 1

Hash (cost=5.65..5.65 rows=65 width=8) (actual time=0.025..0.025 rows=65 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
48. 0.017 0.017 ↑ 1.0 65 1

Seq Scan on t563_opportunitystagenamepicklistdim t563_opportunitystagenamepicklistdim_5 (cost=0.00..5.65 rows=65 width=8) (actual time=0.002..0.017 rows=65 loops=1)

49. 0.000 0.000 ↓ 0.0 0 373,591

CTE Scan on nullgroupbyvaluecte cte0_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=373,591)

50.          

SubPlan (for Nested Loop Left Join)

51. 0.066 0.066 ↑ 2.1 616 1

CTE Scan on docd_cte_0 cte0_1 (cost=0.00..25.56 rows=1,278 width=4) (actual time=0.000..0.066 rows=616 loops=1)

52. 0.024 0.024 ↑ 1.0 2 1

CTE Scan on picklist_1 cte0_2 (cost=0.00..0.04 rows=2 width=4) (actual time=0.013..0.024 rows=2 loops=1)

53. 0.039 0.039 ↑ 1.1 17 1

CTE Scan on picklist_2 cte0_3 (cost=0.00..0.36 rows=18 width=4) (actual time=0.009..0.039 rows=17 loops=1)

54.          

CTE bucketec

55. 244.882 6,179.264 ↑ 1.8 99,299 1

Nested Loop Left Join (cost=306,610.39..1,510,964.09 rows=176,257 width=116) (actual time=1,678.848..6,179.264 rows=99,299 loops=1)

  • Filter: ((t531_oppfact_4.sid IS NULL) OR (t531_oppfact_4.c1786_opp_close_date < 20191001) OR (t531_oppfact_4.c1786_opp_close_date > 20191231) OR (NOT (hashed SubPlan 14)) OR (t531_oppfact_4.c1791_opp_ownerid IS NULL))
  • Rows Removed by Filter: 843642
56. 298.954 2,162.504 ↓ 5.2 942,941 1

Merge Left Join (cost=306,551.87..308,070.21 rows=180,291 width=24) (actual time=1,512.608..2,162.504 rows=942,941 loops=1)

  • Merge Cond: ((t531_oppfact_3.c1782_opp_currency_code = t534_clari_conversion_rate_1.sid) AND (t531_oppfact_3.c1786_opp_close_date = t534_clari_conversion_rate_1.rate_date))
57. 682.107 1,766.172 ↓ 5.2 942,941 1

Sort (cost=305,030.33..305,481.06 rows=180,291 width=24) (actual time=1,500.108..1,766.172 rows=942,941 loops=1)

  • Sort Key: t531_oppfact_3.c1782_opp_currency_code, t531_oppfact_3.c1786_opp_close_date
  • Sort Method: external merge Disk: 35032kB
58. 140.596 1,084.065 ↓ 5.2 942,941 1

Nested Loop (cost=29.32..285,591.46 rows=180,291 width=24) (actual time=0.220..1,084.065 rows=942,941 loops=1)

59. 0.320 0.373 ↓ 3.1 616 1

HashAggregate (cost=28.76..30.76 rows=200 width=4) (actual time=0.194..0.373 rows=616 loops=1)

  • Group Key: cte0_11.c1
60. 0.053 0.053 ↑ 2.1 616 1

CTE Scan on docd_cte_0 cte0_11 (cost=0.00..25.56 rows=1,278 width=4) (actual time=0.002..0.053 rows=616 loops=1)

61. 943.096 943.096 ↓ 3.7 1,531 616

Index Scan using ix_t531_oppfact_notnullowner_closedate_timestamp on t531_oppfact t531_oppfact_3 (cost=0.56..1,423.64 rows=416 width=28) (actual time=0.008..1.531 rows=1,531 loops=616)

  • Index Cond: ((c1791_opp_ownerid = cte0_11.c1) AND (c1786_opp_close_date >= 20191001) AND (c1786_opp_close_date <= 20191231) AND (end_stamp > '1574927999999'::bigint) AND (start_stamp <= '1574927999999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 24
62. 92.659 97.378 ↓ 59.9 958,316 1

Sort (cost=1,521.53..1,561.51 rows=15,992 width=16) (actual time=12.171..97.378 rows=958,316 loops=1)

  • Sort Key: t534_clari_conversion_rate_1.sid, t534_clari_conversion_rate_1.rate_date
  • Sort Method: quicksort Memory: 1545kB
63. 0.930 4.719 ↓ 1.0 16,575 1

Append (cost=0.00..404.89 rows=15,992 width=16) (actual time=0.021..4.719 rows=16,575 loops=1)

64. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on t534_clari_conversion_rate t534_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
65. 3.785 3.785 ↓ 1.0 16,575 1

Seq Scan on t534_clari_conversion_rate_usd t534_clari_conversion_rate_usd_1 (cost=0.00..404.89 rows=15,991 width=16) (actual time=0.017..3.785 rows=16,575 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
66. 3,771.764 3,771.764 ↑ 1.0 1 942,941

Index Scan using t531_oppfact_sidendstampunique on t531_oppfact t531_oppfact_4 (cost=0.56..6.63 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=942,941)

  • Index Cond: ((t531_oppfact_3.sid = sid) AND (t531_oppfact_3.sid = sid) AND (end_stamp > '1574236800000'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1574236800000'::bigint))
  • Rows Removed by Filter: 0
67.          

SubPlan (for Nested Loop Left Join)

68. 0.046 0.046 ↑ 2.1 616 1

CTE Scan on docd_cte_0 cte0_7 (cost=0.00..25.56 rows=1,278 width=4) (actual time=0.001..0.046 rows=616 loops=1)

69. 0.002 0.002 ↑ 1.0 2 1

CTE Scan on picklist_1 cte0_8 (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)

70. 0.001 0.001 ↑ 1.1 17 1

CTE Scan on picklist_2 cte0_9 (cost=0.00..0.36 rows=18 width=4) (actual time=0.001..0.001 rows=17 loops=1)

71. 0.065 0.065 ↑ 2.1 616 1

CTE Scan on docd_cte_0 cte0_10 (cost=0.00..25.56 rows=1,278 width=4) (actual time=0.001..0.065 rows=616 loops=1)

72. 0.049 9,888.550 ↑ 2.1 400 1

Merge Append (cost=9,503.13..9,519.96 rows=855 width=12) (actual time=9,888.426..9,888.550 rows=400 loops=1)

  • Sort Key: cte0_12.c4 DESC NULLS LAST
73. 0.004 3,635.158 ↓ 0.0 0 1

Sort (cost=2,531.06..2,532.20 rows=455 width=12) (actual time=3,635.158..3,635.158 rows=0 loops=1)

  • Sort Key: cte0_12.c4 DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
74. 61.736 3,635.154 ↓ 0.0 0 1

Hash Join (cost=6.02..2,506.42 rows=455 width=12) (actual time=3,635.154..3,635.154 rows=0 loops=1)

  • Hash Cond: ((cte0_12.c5 = t563_opportunitystagenamepicklistdim_6.sid) AND (cte0_12.c6 = "*VALUES*".column2))
75. 3,573.349 3,573.349 ↓ 4.1 373,591 1

CTE Scan on bucketac cte0_12 (cost=0.00..1,818.44 rows=90,922 width=48) (actual time=2,698.055..3,573.349 rows=373,591 loops=1)

76. 0.002 0.069 ↑ 1.0 2 1

Hash (cost=5.99..5.99 rows=2 width=36) (actual time=0.069..0.069 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
77. 0.028 0.067 ↑ 1.0 2 1

Hash Join (cost=0.08..5.99 rows=2 width=36) (actual time=0.050..0.067 rows=2 loops=1)

  • Hash Cond: (t563_opportunitystagenamepicklistdim_6.correlated_value = "*VALUES*".column1)
78. 0.007 0.007 ↑ 1.0 65 1

Seq Scan on t563_opportunitystagenamepicklistdim t563_opportunitystagenamepicklistdim_6 (cost=0.00..5.65 rows=65 width=23) (actual time=0.002..0.007 rows=65 loops=1)

79. 0.007 0.032 ↑ 1.0 2 1

Hash (cost=0.05..0.05 rows=2 width=64) (actual time=0.032..0.032 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 0.001 0.025 ↑ 1.0 2 1

Unique (cost=0.04..0.05 rows=2 width=64) (actual time=0.024..0.025 rows=2 loops=1)

81. 0.018 0.024 ↑ 1.0 2 1

Sort (cost=0.04..0.04 rows=2 width=64) (actual time=0.024..0.024 rows=2 loops=1)

  • Sort Key: "*VALUES*".column1, "*VALUES*".column2
  • Sort Method: quicksort Memory: 25kB
82. 0.006 0.006 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=64) (actual time=0.005..0.006 rows=2 loops=1)

83. 0.031 6,253.343 ↑ 1.0 400 1

Limit (cost=6,972.06..6,973.06 rows=400 width=12) (actual time=6,253.266..6,253.343 rows=400 loops=1)

84. 2.843 6,253.312 ↑ 110.2 400 1

Sort (cost=6,972.06..7,082.22 rows=44,064 width=12) (actual time=6,253.265..6,253.312 rows=400 loops=1)

  • Sort Key: cte0_13.c4 DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
85. 23.296 6,250.469 ↑ 3.5 12,577 1

Hash Join (cost=0.13..4,847.32 rows=44,064 width=12) (actual time=1,691.953..6,250.469 rows=12,577 loops=1)

  • Hash Cond: ((cte0_13.c5 = "*VALUES*_1".column1) AND (cte0_13.c6 = "*VALUES*_1".column2))
86. 6,227.158 6,227.158 ↑ 1.8 99,299 1

CTE Scan on bucketec cte0_13 (cost=0.00..3,525.14 rows=176,257 width=76) (actual time=1,678.850..6,227.158 rows=99,299 loops=1)

87. 0.003 0.015 ↑ 1.0 3 1

Hash (cost=0.08..0.08 rows=3 width=64) (actual time=0.015..0.015 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
88. 0.006 0.012 ↑ 1.0 3 1

HashAggregate (cost=0.05..0.08 rows=3 width=64) (actual time=0.012..0.012 rows=3 loops=1)

  • Group Key: "*VALUES*_1".column1, "*VALUES*_1".column2
89. 0.006 0.006 ↑ 1.0 3 1

Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=64) (actual time=0.004..0.006 rows=3 loops=1)

90. 0.103 9,893.671 ↑ 18.2 400 1

Sort (cost=12,747.65..12,765.90 rows=7,297 width=27) (actual time=9,893.648..9,893.671 rows=400 loops=1)

  • Sort Key: cte0.c2 DESC NULLS LAST
  • Sort Method: quicksort Memory: 56kB
91. 0.362 9,893.568 ↑ 18.2 400 1

Nested Loop (cost=1.13..12,395.80 rows=7,297 width=27) (actual time=9,888.467..9,893.568 rows=400 loops=1)

92. 0.155 9,891.606 ↑ 4.4 400 1

Nested Loop (cost=0.56..8,005.20 rows=1,756 width=16) (actual time=9,888.443..9,891.606 rows=400 loops=1)

93. 9,888.651 9,888.651 ↑ 1.0 400 1

CTE Scan on topsids cte0 (cost=0.00..8.00 rows=400 width=12) (actual time=9,888.428..9,888.651 rows=400 loops=1)

94. 2.800 2.800 ↑ 4.0 1 400

Index Scan using idx_t531_oppfact_sid_end_stamp_start_stamp on t531_oppfact (cost=0.56..19.95 rows=4 width=4) (actual time=0.005..0.007 rows=1 loops=400)

  • Index Cond: ((sid = cte0.c1) AND (end_stamp > '1574927999999'::bigint) AND (start_stamp <= '1574927999999'::bigint))
  • Filter: (NOT deleted)
95. 1.600 1.600 ↑ 22.0 1 400

Index Scan using t530_oppdim_sidendstampunique on t530_oppdim (cost=0.56..2.28 rows=22 width=23) (actual time=0.004..0.004 rows=1 loops=400)

  • Index Cond: ((sid = t531_oppfact.sid) AND (end_stamp > '1574927999999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1574927999999'::bigint))
  • Rows Removed by Filter: 0
Planning time : 9.289 ms
Execution time : 9,908.374 ms