explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dwco

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 7,099.286 ↑ 599.7 38 1

Nested Loop (cost=3,605,459.84..7,212,021.81 rows=22,788 width=96) (actual time=4,012.659..7,099.286 rows=38 loops=1)

2.          

CTE multitimecte

3. 0.006 0.006 ↑ 1.0 2 1

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

4.          

CTE picklist_0

5. 0.270 0.270 ↑ 1.8 4 1

Seq Scan on t67_opportunitystagenamepicklistdim (cost=0.00..33.00 rows=7 width=4) (actual time=0.039..0.270 rows=4 loops=1)

  • Filter: (correlated_value = ANY ('{"Booked Run Rate","Closed Won",Closed,"07 \\u2013 Subscription Provisioned \\u2013 100%","07 \\u2013 Order Booked \\u2013 100%","07 ? Order Booked ? 100%","07 \\uFFFD Order Booked \\uFFFD 100%"}'::text[]))
  • Rows Removed by Filter: 476
6.          

CTE picklist_2

7. 0.380 0.380 ↑ 1.3 15 1

Seq Scan on t67_opportunitystagenamepicklistdim t67_opportunitystagenamepicklistdim_1 (cost=0.00..40.80 rows=20 width=4) (actual time=0.017..0.380 rows=15 loops=1)

  • Filter: (correlated_value = ANY ('{"00 - Lost/Active - 0%","00- Lost/Inactive- 0%.","0 \\u2013 Lost/Inactive \\u2013 0%","00 \\u2013 Lost/Inactive \\u2013 0%","00 - Lost/Inactive - 0%","Closed Admin","00- Lost/Inactive - 0%","00 \\u00E2\\u0080\\u0093 Lost/Inactive \\u00E2\\u0080\\u0093 0%","00 Closed Influencer Partner Registered",0,"Closed Duplicate Opportunity","Closed Lost","00 \\uFFFD Lost/Inactive \\uFFFD 0%","00 \\u00D0 Lost/Inactive \\u00D0 0%",00-Lost/Inactive-0%,0%,"Closed Dead Opportunity","Closed NFR","Lost/Inactive - 0%","0 - Idle Account & Stage"}'::text[]))
  • Rows Removed by Filter: 465
8.          

CTE picklist_3

9. 0.484 0.484 ↑ 1.4 19 1

Seq Scan on t67_opportunitystagenamepicklistdim t67_opportunitystagenamepicklistdim_2 (cost=0.00..45.00 rows=27 width=4) (actual time=0.020..0.484 rows=19 loops=1)

  • Filter: (correlated_value = ANY ('{"00 - Lost/Active - 0%","00- Lost/Inactive- 0%.","0 \\u2013 Lost/Inactive \\u2013 0%","00 \\u2013 Lost/Inactive \\u2013 0%","00- Lost/Inactive - 0%","07 ? Order Booked ? 100%","Closed Duplicate Opportunity","Closed Lost","00 \\uFFFD Lost/Inactive \\uFFFD 0%","Closed Dead Opportunity","Closed NFR","07 \\uFFFD Order Booked \\uFFFD 100%","00 - Lost/Inactive - 0%","Closed Admin","00 \\u00E2\\u0080\\u0093 Lost/Inactive \\u00E2\\u0080\\u0093 0%","00 Closed Influencer Partner Registered",0,"Booked Run Rate","Closed Won","00 \\u00D0 Lost/Inactive \\u00D0 0%",00-Lost/Inactive-0%,0%,Closed,"07 \\u2013 Subscription Provisioned \\u2013 100%","07 \\u2013 Order Booked \\u2013 100%","Lost/Inactive - 0%","0 - Idle Account & Stage"}'::text[]))
  • Rows Removed by Filter: 461
10. 0.009 0.009 ↑ 1.0 2 1

CTE Scan on multitimecte multitimecte_alias (cost=0.00..0.04 rows=2 width=8) (actual time=0.004..0.009 rows=2 loops=1)

11. 0.014 7,099.256 ↑ 599.7 19 2

Append (cost=3,605,341.02..3,605,723.59 rows=11,394 width=96) (actual time=3,497.633..3,549.628 rows=19 loops=2)

12.          

CTE openwont1

13. 195.781 3,796.954 ↑ 1.5 48,228 2

Hash Left Join (cost=101,003.36..2,726,587.54 rows=74,381 width=34) (actual time=278.389..1,898.477 rows=48,228 loops=2)

  • Hash Cond: (t55_oppfact_1.c651_opp_currency_code = t232_claricurrencytypedim_1.sid)
14. 77.289 3,597.972 ↑ 1.5 48,228 2

Nested Loop Left Join (cost=100,939.42..2,389,670.64 rows=74,381 width=56) (actual time=275.933..1,798.986 rows=48,228 loops=2)

15. 61.813 2,459.678 ↑ 1.5 48,228 2

Hash Left Join (cost=100,938.85..1,767,764.09 rows=74,381 width=28) (actual time=275.910..1,229.839 rows=48,228 loops=2)

  • Hash Cond: (t55_oppfact.c651_opp_currency_code = t232_claricurrencytypedim.sid)
16. 1,862.820 2,397.192 ↑ 1.5 48,228 2

Bitmap Heap Scan on t55_oppfact (cost=100,875.22..1,762,214.87 rows=74,381 width=24) (actual time=275.323..1,198.596 rows=48,228 loops=2)

  • Recheck Cond: ((c630_opp_close_date >= 20181229) AND (c630_opp_close_date <= 20190329) AND (start_stamp <= $8) AND (end_stamp > $8))
  • Rows Removed by Index Recheck: 1002929
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (c653_opp_territory_id IS NULL)))
  • Rows Removed by Filter: 92955
  • Heap Blocks: exact=50910 lossy=60853
17. 531.056 531.056 ↑ 1.6 141,184 2

Bitmap Index Scan on idx_t55_oppfact_closedate_stagename_timestamp (cost=0.00..100,856.18 rows=222,486 width=0) (actual time=265.528..265.528 rows=141,184 loops=2)

  • Index Cond: ((c630_opp_close_date >= 20181229) AND (c630_opp_close_date <= 20190329) AND (start_stamp <= $8) AND (end_stamp > $8))
18.          

SubPlan (forBitmap Heap Scan)

19. 0.391 0.391 ↑ 1.3 15 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.40 rows=20 width=4) (actual time=0.020..0.391 rows=15 loops=1)

20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_t134_territoryhierarchy_sid_c796_ancestor_territory36986179 on t134_territoryhierarchy t134_territoryhierarchy_2 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t55_oppfact.c653_opp_territory_id) AND (c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Fetches: 0
21. 2.206 2.925 ↑ 1.2 4,825 1

Bitmap Heap Scan on t134_territoryhierarchy t134_territoryhierarchy_3 (cost=193.72..1,557.56 rows=5,590 width=4) (actual time=0.768..2.925 rows=4,825 loops=1)

  • Recheck Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=337
22. 0.719 0.719 ↑ 1.2 4,825 1

Bitmap Index Scan on idx_t134_territoryhierarchy_c796_ancestor_territory_id_36986179 (cost=0.00..192.32 rows=5,590 width=0) (actual time=0.719..0.719 rows=4,825 loops=1)

  • Index Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
23. 0.011 0.673 ↑ 1.0 49 1

Hash (cost=63.01..63.01 rows=49 width=12) (actual time=0.673..0.673 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
24. 0.662 0.662 ↑ 1.0 49 1

Seq Scan on t232_claricurrencytypedim (cost=0.00..63.01 rows=49 width=12) (actual time=0.025..0.662 rows=49 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 2352
25. 1,061.005 1,061.005 ↑ 1.0 1 96,455

Index Scan using t55_oppfact_sidendstampunique on t55_oppfact t55_oppfact_1 (cost=0.57..8.35 rows=1 width=28) (actual time=0.010..0.011 rows=1 loops=96,455)

  • Index Cond: ((t55_oppfact.sid = sid) AND (t55_oppfact.sid = sid) AND (end_stamp > '1559717999999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1559717999999'::bigint))
  • Rows Removed by Filter: 0
26. 0.015 0.571 ↑ 1.0 49 1

Hash (cost=63.01..63.01 rows=49 width=12) (actual time=0.570..0.571 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.556 0.556 ↑ 1.0 49 1

Seq Scan on t232_claricurrencytypedim t232_claricurrencytypedim_1 (cost=0.00..63.01 rows=49 width=12) (actual time=0.019..0.556 rows=49 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 2352
28.          

SubPlan (forHash Left Join)

29. 0.002 0.002 ↑ 1.8 4 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.14 rows=7 width=4) (actual time=0.001..0.002 rows=4 loops=1)

30. 0.000 0.000 ↑ 1.8 4 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.000 rows=4 loops=1)

31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_t134_territoryhierarchy_sid_c796_ancestor_territory36986179 on t134_territoryhierarchy (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t55_oppfact_1.c653_opp_territory_id) AND (c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Fetches: 0
32. 2.029 2.628 ↑ 1.2 4,825 1

Bitmap Heap Scan on t134_territoryhierarchy t134_territoryhierarchy_1 (cost=193.72..1,557.56 rows=5,590 width=4) (actual time=0.644..2.628 rows=4,825 loops=1)

  • Recheck Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=337
33. 0.599 0.599 ↑ 1.2 4,825 1

Bitmap Index Scan on idx_t134_territoryhierarchy_c796_ancestor_territory_id_36986179 (cost=0.00..192.32 rows=5,590 width=0) (actual time=0.599..0.599 rows=4,825 loops=1)

  • Index Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
34.          

CTE openwont1ids

35. 137.270 4,023.318 ↓ 241.1 48,228 2

HashAggregate (cost=1,673.57..1,675.57 rows=200 width=4) (actual time=1,992.726..2,011.659 rows=48,228 loops=2)

  • Group Key: cte0_5.c1
36. 3,886.048 3,886.048 ↑ 1.5 48,228 2

CTE Scan on openwont1 cte0_5 (cost=0.00..1,487.62 rows=74,381 width=4) (actual time=278.427..1,943.024 rows=48,228 loops=2)

37.          

CTE bucketab

38. 59.502 96.578 ↑ 531.3 14 2

HashAggregate (cost=2,603.34..2,677.72 rows=7,438 width=68) (actual time=48.270..48.289 rows=14 loops=2)

  • Group Key: cte0_6.c5, CASE WHEN cte0_6.c6 THEN CASE WHEN cte0_6.c4 THEN 'convertedWon'::text ELSE 'convertedOpen'::text END ELSE CASE WHEN cte0_6.c4 THEN 'unconvertedWon'::text ELSE 'unconvertedOpen'::text END END, cte0_6.c3
39. 37.076 37.076 ↑ 1.5 48,228 2

CTE Scan on openwont1 cte0_6 (cost=0.00..1,487.62 rows=74,381 width=64) (actual time=0.034..18.538 rows=48,228 loops=2)

40.          

CTE bucketd

41. 6.018 7,002.468 ↑ 791.2 5 2

GroupAggregate (cost=856,687.21..874,400.20 rows=3,956 width=120) (actual time=3,497.616..3,501.234 rows=5 loops=2)

  • Group Key: ($8), (CASE WHEN (t55_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 15)) THEN CASE WHEN (t55_oppfact_3.c630_opp_close_date > 20190329) THEN 'pulledIn'::text WHEN (t55_oppfact_3.c630_opp_close_date < 20181229) THEN 'pushedIn'::text WHEN ((t55_oppfact_3.c630_opp_close_date >= 20181229) AND (t55_oppfact_3.c630_opp_close_date <= 20190329) AND ((alternatives: SubPlan 16 or hashed SubPlan 17) OR (t55_oppfact_3.c653_opp_territory_id IS NULL))) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
42. 9.474 6,996.450 ↓ 1.8 6,974 2

Sort (cost=856,686.60..856,696.49 rows=3,956 width=60) (actual time=3,497.353..3,498.225 rows=6,974 loops=2)

  • Sort Key: (CASE WHEN (t55_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 15)) THEN CASE WHEN (t55_oppfact_3.c630_opp_close_date > 20190329) THEN 'pulledIn'::text WHEN (t55_oppfact_3.c630_opp_close_date < 20181229) THEN 'pushedIn'::text WHEN ((t55_oppfact_3.c630_opp_close_date >= 20181229) AND (t55_oppfact_3.c630_opp_close_date <= 20190329) AND ((alternatives: SubPlan 16 or hashed SubPlan 17) OR (t55_oppfact_3.c653_opp_territory_id IS NULL))) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 733kB
43. 22.715 6,986.976 ↓ 1.8 6,974 2

Nested Loop Left Join (cost=6.40..856,450.23 rows=3,956 width=60) (actual time=2,116.975..3,493.488 rows=6,974 loops=2)

44. 99.914 6,723.712 ↓ 1.8 6,974 2

Nested Loop Left Join (cost=5.22..805,421.99 rows=3,956 width=20) (actual time=2,116.949..3,361.856 rows=6,974 loops=2)

  • Join Filter: (t55_oppfact_2.c651_opp_currency_code = t232_claricurrencytypedim_2.sid)
  • Rows Removed by Join Filter: 334752
45. 2.768 6,568.006 ↓ 1.8 6,974 2

Nested Loop (cost=5.22..802,451.20 rows=3,956 width=16) (actual time=2,116.585..3,284.003 rows=6,974 loops=2)

46. 0.021 0.294 ↑ 1.8 4 2

HashAggregate (cost=0.16..0.23 rows=7 width=4) (actual time=0.140..0.147 rows=4 loops=2)

  • Group Key: cte0_9.c1
47. 0.273 0.273 ↑ 1.8 4 1

CTE Scan on picklist_0 cte0_9 (cost=0.00..0.14 rows=7 width=4) (actual time=0.042..0.273 rows=4 loops=1)

48. 2,499.065 6,564.944 ↓ 3.1 1,744 8

Index Scan using idx_t55_oppfact_closedate_stagename_timestamp on t55_oppfact t55_oppfact_2 (cost=5.07..114,630.20 rows=565 width=20) (actual time=609.064..820.618 rows=1,744 loops=8)

  • Index Cond: ((c630_opp_close_date >= 20181229) AND (c630_opp_close_date <= 20190329) AND (c644_opp_stagename = cte0_9.c1) AND (start_stamp <= '1559717999999'::bigint) AND (end_stamp > '1559717999999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 20)) AND ((alternatives: SubPlan 18 or hashed SubPlan 19) OR (c653_opp_territory_id IS NULL)))
  • Rows Removed by Filter: 12349
49.          

SubPlan (forIndex Scan)

50. 4,062.912 4,062.912 ↓ 241.1 48,228 2

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=1,993.060..2,031.456 rows=48,228 loops=2)

51. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_t134_territoryhierarchy_sid_c796_ancestor_territory36986179 on t134_territoryhierarchy t134_territoryhierarchy_6 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t55_oppfact_2.c653_opp_territory_id) AND (c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Fetches: 0
52. 2.215 2.967 ↑ 1.2 4,825 1

Bitmap Heap Scan on t134_territoryhierarchy t134_territoryhierarchy_7 (cost=193.72..1,557.56 rows=5,590 width=4) (actual time=0.799..2.967 rows=4,825 loops=1)

  • Recheck Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=337
53. 0.752 0.752 ↑ 1.2 4,825 1

Bitmap Index Scan on idx_t134_territoryhierarchy_c796_ancestor_territory_id_36986179 (cost=0.00..192.32 rows=5,590 width=0) (actual time=0.752..0.752 rows=4,825 loops=1)

  • Index Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
54. 55.101 55.792 ↑ 1.0 49 13,948

Materialize (cost=0.00..63.26 rows=49 width=12) (actual time=0.000..0.004 rows=49 loops=13,948)

55. 0.691 0.691 ↑ 1.0 49 1

Seq Scan on t232_claricurrencytypedim t232_claricurrencytypedim_2 (cost=0.00..63.01 rows=49 width=12) (actual time=0.031..0.691 rows=49 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 2352
56. 237.116 237.116 ↑ 1.0 1 13,948

Index Scan using t55_oppfact_sidendstampunique on t55_oppfact t55_oppfact_3 (cost=0.57..8.44 rows=1 width=16) (actual time=0.014..0.017 rows=1 loops=13,948)

  • Index Cond: ((t55_oppfact_2.sid = sid) AND (t55_oppfact_2.sid = sid) AND (end_stamp > $8))
  • Filter: ((NOT deleted) AND (start_stamp <= $8))
  • Rows Removed by Filter: 5
57.          

SubPlan (forNested Loop Left Join)

58. 0.494 0.494 ↑ 1.4 19 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.54 rows=27 width=4) (actual time=0.022..0.494 rows=19 loops=1)

59. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_t134_territoryhierarchy_sid_c796_ancestor_territory36986179 on t134_territoryhierarchy t134_territoryhierarchy_4 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t55_oppfact_3.c653_opp_territory_id) AND (c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Fetches: 0
60. 2.332 2.939 ↑ 1.2 4,825 1

Bitmap Heap Scan on t134_territoryhierarchy t134_territoryhierarchy_5 (cost=193.72..1,557.56 rows=5,590 width=4) (actual time=0.654..2.939 rows=4,825 loops=1)

  • Recheck Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=337
61. 0.607 0.607 ↑ 1.2 4,825 1

Bitmap Index Scan on idx_t134_territoryhierarchy_c796_ancestor_territory_id_36986179 (cost=0.00..192.32 rows=5,590 width=0) (actual time=0.607..0.607 rows=4,825 loops=1)

  • Index Cond: ((c796_ancestor_territory_id = 2117) AND (end_stamp = '32503680000000'::bigint))
62. 0.038 7,002.526 ↑ 791.2 5 2

Subquery Scan on *SELECT* 1 (cost=0.00..128.57 rows=3,956 width=96) (actual time=3,497.632..3,501.263 rows=5 loops=2)

63. 7,002.488 7,002.488 ↑ 791.2 5 2

CTE Scan on bucketd cte0 (cost=0.00..79.12 rows=3,956 width=120) (actual time=3,497.620..3,501.244 rows=5 loops=2)

64. 0.065 96.716 ↑ 531.3 14 2

Hash Left Join (cost=1.11..179.62 rows=7,438 width=96) (actual time=48.325..48.358 rows=14 loops=2)

  • Hash Cond: (cte0_1.c6 = t68_opportunityforecastcategorypicklistdim.sid)
65. 96.632 96.632 ↑ 531.3 14 2

CTE Scan on bucketab cte0_1 (cost=0.00..148.76 rows=7,438 width=68) (actual time=48.288..48.316 rows=14 loops=2)

66. 0.006 0.019 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=36) (actual time=0.019..0.019 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.013 0.013 ↑ 1.0 5 1

Seq Scan on t68_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=36) (actual time=0.009..0.013 rows=5 loops=1)

Planning time : 15.790 ms
Execution time : 7,104.305 ms