explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VgJB : slow bt query

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=993,046.39..993,046.46 rows=26 width=984) (actual rows= loops=)

2.          

CTE supplierrank

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,718.06..1,718.19 rows=4 width=86) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,718.06..1,718.14 rows=4 width=78) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=1,718.06..1,718.07 rows=4 width=70) (actual rows= loops=)

  • Sort Key: sr_4.rank, s_5.name
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,658.18..1,718.02 rows=4 width=70) (actual rows= loops=)

  • Hash Cond: (s_5.id = sr_4.supplierid)
7. 0.000 0.000 ↓ 0.0

Index Scan using idx_supplier_view_name on supplier_view s_5 (cost=0.42..60.25 rows=4 width=30) (actual rows= loops=)

  • Index Cond: (upper((name)::text) = ANY (('{"COLT TECHNOLOGY SERVICES","ORANGE S.A.","SWISSCOM","VODAFONE GROUP PLC"}'::cstring)::text[]))
8. 0.000 0.000 ↓ 0.0

Hash (cost=1,657.75..1,657.75 rows=1 width=48) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Subquery Scan on sr_4 (cost=1,657.72..1,657.75 rows=1 width=48) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,657.72..1,657.74 rows=1 width=48) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=1,657.72..1,657.73 rows=1 width=40) (actual rows= loops=)

  • Sort Key: (sum(t_1.transactionsum)) DESC
12. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,657.69..1,657.71 rows=1 width=40) (actual rows= loops=)

  • Group Key: t_1.supplierid
13. 0.000 0.000 ↓ 0.0

Sort (cost=1,657.69..1,657.69 rows=1 width=14) (actual rows= loops=)

  • Sort Key: t_1.supplierid
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.59..1,657.68 rows=1 width=14) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using idx_supplier_view_name on supplier_view s_6 (cost=0.42..60.25 rows=4 width=8) (actual rows= loops=)

  • Index Cond: (upper((name)::text) = ANY (('{"COLT TECHNOLOGY SERVICES","ORANGE S.A.","SWISSCOM","VODAFONE GROUP PLC"}'::cstring)::text[]))
16. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on transaction_summary_view t_1 (cost=9.17..399.35 rows=1 width=14) (actual rows= loops=)

  • Recheck Cond: (supplierid = s_6.id)
  • Filter: ((('{"GLOBAL"}'::cstring)::character varying[] && ccpath) AND (('{"TELCO & SATELLITE"}'::cstring)::character varying[] && catpath) AND (((fiscalyear = 2,020) AND (fiscaltimeperiod <= 12)) OR ((fiscalyear = 2,019) AND (fiscaltimeperiod > 12))))
17. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_transaction_summary_view_supplierid (cost=0.00..9.17 rows=99 width=0) (actual rows= loops=)

  • Index Cond: (supplierid = s_6.id)
18.          

CTE contracts

19. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=4,825.11..4,825.56 rows=3 width=16) (actual rows= loops=)

  • Group Key: c_4.supplierid
20. 0.000 0.000 ↓ 0.0

Gather Merge (cost=4,825.11..4,825.52 rows=3 width=16) (actual rows= loops=)

  • Workers Planned: 1
21. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=3,825.10..3,825.17 rows=3 width=16) (actual rows= loops=)

  • Group Key: c_4.supplierid
22. 0.000 0.000 ↓ 0.0

Sort (cost=3,825.10..3,825.11 rows=5 width=8) (actual rows= loops=)

  • Sort Key: c_4.supplierid
23. 0.000 0.000 ↓ 0.0

Subquery Scan on c_4 (cost=0.00..3,825.04 rows=5 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on basecontract_view c_5 (cost=0.00..3,824.99 rows=3 width=1,044) (actual rows= loops=)

  • Filter: (((contracttype)::text <> 'NDA'::text) AND (upper((normalizedvendor)::text) = ANY (('{"COLT TECHNOLOGY SERVICES","ORANGE S.A.","SWISSCOM","VODAFONE GROUP PLC"}'::cstring)::text[])) AND ((CASE WHEN ((timezone('utc+12'::text, now()))::date < startdate) THEN 'pending'::text WHEN (((timezone('utc+12'::text, now()))::date >= startdate) AND ((timezone('utc+12'::text, now()))::date <= enddate) AND (enddate IS NOT NULL)) THEN 'active'::text WHEN evergreen THEN 'active'::text WHEN ((timezone('utc+12'::text, now()))::date > enddate) THEN 'expired'::text ELSE 'unknown'::text END = 'active'::text) OR (CASE WHEN ((timezone('utc+12'::text, now()))::date < startdate) THEN 'pending'::text WHEN (((timezone('utc+12'::text, now()))::date >= startdate) AND ((timezone('utc+12'::text, now()))::date <= enddate) AND (enddate IS NOT NULL)) THEN 'active'::text WHEN evergreen THEN 'active'::text WHEN ((timezone('utc+12'::text, now()))::date > enddate) THEN 'expired'::text ELSE 'unknown'::text END = 'unknown'::text) OR (evergreen IS TRUE)))
25.          

CTE dimjoin

26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,657.93..6,267.23 rows=63,468 width=278) (actual rows= loops=)

  • Hash Cond: (dim_1.id = agg.dimensionid)
27. 0.000 0.000 ↓ 0.0

Seq Scan on costcenter_view dim_1 (cost=0.00..4,442.68 rows=63,468 width=126) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=1,657.92..1,657.92 rows=1 width=168) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Subquery Scan on agg (cost=1,657.69..1,657.92 rows=1 width=168) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,657.69..1,657.91 rows=1 width=168) (actual rows= loops=)

  • Group Key: t_2.costcenterid
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,657.69..1,657.85 rows=1 width=48) (actual rows= loops=)

  • Join Filter: (t_2.supplierid = r.supplierid)
32. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,657.69..1,657.71 rows=1 width=48) (actual rows= loops=)

  • Group Key: t_2.costcenterid, t_2.supplierid
33. 0.000 0.000 ↓ 0.0

Sort (cost=1,657.69..1,657.69 rows=1 width=22) (actual rows= loops=)

  • Sort Key: t_2.costcenterid, t_2.supplierid
34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.59..1,657.68 rows=1 width=22) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Scan using idx_supplier_view_name on supplier_view s_7 (cost=0.42..60.25 rows=4 width=8) (actual rows= loops=)

  • Index Cond: (upper((name)::text) = ANY (('{"COLT TECHNOLOGY SERVICES","ORANGE S.A.","SWISSCOM","VODAFONE GROUP PLC"}'::cstring)::text[]))
36. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on transaction_summary_view t_2 (cost=9.17..399.35 rows=1 width=22) (actual rows= loops=)

  • Recheck Cond: (supplierid = s_7.id)
  • Filter: ((('{"GLOBAL"}'::cstring)::character varying[] && ccpath) AND (('{"TELCO & SATELLITE"}'::cstring)::character varying[] && catpath) AND (((fiscalyear = 2,020) AND (fiscaltimeperiod <= 12)) OR ((fiscalyear = 2,019) AND (fiscaltimeperiod > 12))))
37. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_transaction_summary_view_supplierid (cost=0.00..9.17 rows=99 width=0) (actual rows= loops=)

  • Index Cond: (supplierid = s_7.id)
38. 0.000 0.000 ↓ 0.0

CTE Scan on supplierrank r (cost=0.00..0.08 rows=4 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Sort (cost=980,235.41..980,236.18 rows=310 width=984) (actual rows= loops=)

  • Sort Key: (sum(dim2.dimensiontotal)) DESC
40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=980,171.14..980,226.57 rows=310 width=984) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,657.68..1,657.69 rows=1 width=32) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.59..1,657.68 rows=1 width=6) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Index Scan using idx_supplier_view_name on supplier_view s_4 (cost=0.42..60.25 rows=4 width=8) (actual rows= loops=)

  • Index Cond: (upper((name)::text) = ANY (('{"COLT TECHNOLOGY SERVICES","ORANGE S.A.","SWISSCOM","VODAFONE GROUP PLC"}'::cstring)::text[]))
44. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on transaction_summary_view t (cost=9.17..399.35 rows=1 width=14) (actual rows= loops=)

  • Recheck Cond: (supplierid = s_4.id)
  • Filter: ((('{"GLOBAL"}'::cstring)::character varying[] && ccpath) AND (('{"TELCO & SATELLITE"}'::cstring)::character varying[] && catpath) AND (((fiscalyear = 2,020) AND (fiscaltimeperiod <= 12)) OR ((fiscalyear = 2,019) AND (fiscaltimeperiod > 12))))
45. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_transaction_summary_view_supplierid (cost=0.00..9.17 rows=99 width=0) (actual rows= loops=)

  • Index Cond: (supplierid = s_4.id)
46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=978,513.46..978,559.57 rows=310 width=752) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.16..34.09 rows=1 width=528) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.16..34.05 rows=1 width=520) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.87..25.54 rows=1 width=390) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.58..17.02 rows=1 width=260) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..8.51 rows=1 width=130) (actual rows= loops=)

  • Join Filter: (sr.supplierid = c.supplierid)
52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..8.41 rows=1 width=130) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

CTE Scan on supplierrank sr (cost=0.00..0.10 rows=1 width=40) (actual rows= loops=)

  • Filter: ((rank - 1) = 0)
54. 0.000 0.000 ↓ 0.0

Index Scan using supplier_view_idx on supplier_view s (cost=0.29..8.31 rows=1 width=98) (actual rows= loops=)

  • Index Cond: (id = sr.supplierid)
55. 0.000 0.000 ↓ 0.0

CTE Scan on contracts c (cost=0.00..0.06 rows=3 width=16) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..8.51 rows=1 width=130) (actual rows= loops=)

  • Join Filter: (sr_1.supplierid = c_1.supplierid)
57. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..8.41 rows=1 width=130) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

CTE Scan on supplierrank sr_1 (cost=0.00..0.10 rows=1 width=40) (actual rows= loops=)

  • Filter: ((rank - 1) = 1)
59. 0.000 0.000 ↓ 0.0

Index Scan using supplier_view_idx on supplier_view s_1 (cost=0.29..8.31 rows=1 width=98) (actual rows= loops=)

  • Index Cond: (id = sr_1.supplierid)
60. 0.000 0.000 ↓ 0.0

CTE Scan on contracts c_1 (cost=0.00..0.06 rows=3 width=16) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..8.51 rows=1 width=130) (actual rows= loops=)

  • Join Filter: (sr_2.supplierid = c_2.supplierid)
62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..8.41 rows=1 width=130) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

CTE Scan on supplierrank sr_2 (cost=0.00..0.10 rows=1 width=40) (actual rows= loops=)

  • Filter: ((rank - 1) = 2)
64. 0.000 0.000 ↓ 0.0

Index Scan using supplier_view_idx on supplier_view s_2 (cost=0.29..8.31 rows=1 width=98) (actual rows= loops=)

  • Index Cond: (id = sr_2.supplierid)
65. 0.000 0.000 ↓ 0.0

CTE Scan on contracts c_2 (cost=0.00..0.06 rows=3 width=16) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..8.51 rows=1 width=130) (actual rows= loops=)

  • Join Filter: (sr_3.supplierid = c_3.supplierid)
67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..8.41 rows=1 width=130) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

CTE Scan on supplierrank sr_3 (cost=0.00..0.10 rows=1 width=40) (actual rows= loops=)

  • Filter: ((rank - 1) = 3)
69. 0.000 0.000 ↓ 0.0

Index Scan using supplier_view_idx on supplier_view s_3 (cost=0.29..8.31 rows=1 width=98) (actual rows= loops=)

  • Index Cond: (id = sr_3.supplierid)
70. 0.000 0.000 ↓ 0.0

CTE Scan on contracts c_3 (cost=0.00..0.06 rows=3 width=16) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Limit (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

CTE Scan on supplierrank (cost=0.00..0.08 rows=4 width=8) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

HashAggregate (cost=978,512.30..978,519.27 rows=310 width=232) (actual rows= loops=)

  • Group Key: dim.dimensionname, dim.dimensioncode, dim.parentid
74. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..958,839.64 rows=983,633 width=232) (actual rows= loops=)

  • Join Filter: (upper((dim.dimensioncode)::text) = ANY ((upper((dim2.dimpath)::text))::text[]))
75. 0.000 0.000 ↓ 0.0

CTE Scan on dimjoin dim2 (cost=0.00..1,269.36 rows=63,468 width=192) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

CTE Scan on dimjoin dim (cost=0.00..1,904.04 rows=317 width=72) (actual rows= loops=)

  • Filter: (('{"GLOBAL"}'::cstring)::character varying[] && ARRAY[(upper((dimensioncode)::text))::character varying]