explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s333

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

Aggregate (cost=28,353.29..28,353.30 rows=1 width=32) (actual rows= loops=)

2.          

CTE values

3. 0.000 0.000 ↓ 0.0

Sort (cost=28,352.20..28,352.20 rows=1 width=1,413) (actual rows= loops=)

  • Sort Key: ((trends."2020-02-02" IS NULL)), (lower((trends."Brand")::text)), (lower((trends."Product")::text)), (lower((trends."Part Number")::text)), (lower((trends."Merchant")::text)), trends."Price Type" DESC
4.          

CTE category_versions

5. 0.000 0.000 ↓ 0.0

Seq Scan on category_versions (cost=0.00..1.79 rows=1 width=13) (actual rows= loops=)

  • Filter: (id = 13)
6.          

CTE specifications

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.71..11,447.95 rows=4,346 width=292) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.29..5,126.61 rows=6,295 width=4) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

CTE Scan on category_versions category_versions_1 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on product_versions_with_deleted (cost=174.29..5,063.64 rows=6,295 width=8) (actual rows= loops=)

  • Recheck Cond: (category_version_id = category_versions_1.id)
  • Filter: (deleted_at IS NULL)
11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_product_versions_on_category_version_id (cost=0.00..172.72 rows=9,106 width=0) (actual rows= loops=)

  • Index Cond: (category_version_id = category_versions_1.id)
12. 0.000 0.000 ↓ 0.0

Index Scan using index_specifications_on_product_version_id on specifications_with_deleted (cost=0.42..0.99 rows=1 width=676) (actual rows= loops=)

  • Index Cond: (product_version_id = product_versions_with_deleted.id)
  • Filter: (deleted_at IS NULL)
13.          

CTE data

14. 0.000 0.000 ↓ 0.0

Function Scan on crosstab ct (cost=0.00..10.00 rows=1,000 width=332) (actual rows= loops=)

15.          

CTE product_statuses

16. 0.000 0.000 ↓ 0.0

Unique (cost=11,346.03..11,374.39 rows=5,672 width=16) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=11,346.03..11,360.21 rows=5,672 width=16) (actual rows= loops=)

  • Sort Key: product_statuses.product_version_id, product_statuses.date DESC
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=175.14..10,992.39 rows=5,672 width=16) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.71..8,380.63 rows=4,391 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.29..5,126.61 rows=6,295 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on category_versions category_versions_2 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on product_versions_with_deleted product_versions_with_deleted_1 (cost=174.29..5,063.64 rows=6,295 width=12) (actual rows= loops=)

  • Recheck Cond: (category_version_id = category_versions_2.id)
  • Filter: (deleted_at IS NULL)
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_product_versions_on_category_version_id (cost=0.00..172.72 rows=9,106 width=0) (actual rows= loops=)

  • Index Cond: (category_version_id = category_versions_2.id)
24. 0.000 0.000 ↓ 0.0

Index Scan using products_pkey on products_with_deleted (cost=0.42..0.52 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = product_versions_with_deleted_1.product_id)
  • Filter: (deleted_at IS NULL)
25. 0.000 0.000 ↓ 0.0

Index Scan using index_product_statuses_on_product_version_id on product_statuses (cost=0.42..0.56 rows=3 width=16) (actual rows= loops=)

  • Index Cond: (product_version_id = product_versions_with_deleted_1.id)
  • Filter: (date <= '2020-02-08'::date)
26.          

CTE trends

27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,230.30..5,518.03 rows=1 width=721) (actual rows= loops=)

  • Join Filter: (product_statuses_1.product_version_id = product_versions_with_deleted_2.id)
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,230.30..5,333.68 rows=1 width=665) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,230.16..5,333.50 rows=1 width=654) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,229.88..5,333.20 rows=1 width=649) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,229.45..5,332.68 rows=1 width=649) (actual rows= loops=)

  • Hash Cond: (specifications.product_version_id = product_versions_with_deleted_2.id)
32. 0.000 0.000 ↓ 0.0

CTE Scan on specifications (cost=0.00..86.92 rows=4,346 width=292) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=5,229.23..5,229.23 rows=18 width=365) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,205.30..5,229.23 rows=18 width=365) (actual rows= loops=)

  • Hash Cond: (data.product_version_id = product_versions_with_deleted_2.id)
35. 0.000 0.000 ↓ 0.0

CTE Scan on data (cost=0.00..20.00 rows=1,000 width=328) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=5,126.61..5,126.61 rows=6,295 width=37) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=174.29..5,126.61 rows=6,295 width=37) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

CTE Scan on category_versions category_versions_3 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on product_versions_with_deleted product_versions_with_deleted_2 (cost=174.29..5,063.64 rows=6,295 width=41) (actual rows= loops=)

  • Recheck Cond: (category_version_id = category_versions_3.id)
  • Filter: (deleted_at IS NULL)
40. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_product_versions_on_category_version_id (cost=0.00..172.72 rows=9,106 width=0) (actual rows= loops=)

  • Index Cond: (category_version_id = category_versions_3.id)
41. 0.000 0.000 ↓ 0.0

Index Scan using products_pkey on products_with_deleted products_with_deleted_1 (cost=0.42..0.52 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = product_versions_with_deleted_2.product_id)
  • Filter: (deleted_at IS NULL)
42. 0.000 0.000 ↓ 0.0

Index Scan using brands_pkey on brands brands_1 (cost=0.28..0.30 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (id = products_with_deleted_1.brand_id)
43. 0.000 0.000 ↓ 0.0

Index Scan using merchants_pkey on merchants merchants_1 (cost=0.15..0.18 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (id = data.merchant_id)
44. 0.000 0.000 ↓ 0.0

CTE Scan on product_statuses product_statuses_1 (cost=0.00..113.44 rows=5,672 width=36) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

CTE Scan on trends (cost=0.00..0.03 rows=1 width=1,413) (actual rows= loops=)

46.          

CTE merchants

47. 0.000 0.000 ↓ 0.0

Result (cost=0.07..0.08 rows=1 width=32) (actual rows= loops=)

48.          

Initplan (for Result)

49. 0.000 0.000 ↓ 0.0

Sort (cost=0.06..0.07 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower((merchant_values.name)::text))
50. 0.000 0.000 ↓ 0.0

Subquery Scan on merchant_values (cost=0.03..0.05 rows=1 width=64) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=548) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=548) (actual rows= loops=)

  • Sort Key: values_1."Merchant
53. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_1 (cost=0.00..0.02 rows=1 width=548) (actual rows= loops=)

54.          

CTE brands

55. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=32) (actual rows= loops=)

56.          

Initplan (for Result)

57. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower((values_2."Brand")::text))
59. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_2 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

60.          

CTE products

61. 0.000 0.000 ↓ 0.0

Result (cost=0.08..0.09 rows=1 width=32) (actual rows= loops=)

62.          

Initplan (for Result)

63. 0.000 0.000 ↓ 0.0

Sort (cost=0.08..0.08 rows=1 width=128) (actual rows= loops=)

  • Sort Key: (lower((product_values.brand)::text)), (lower((product_values.name)::text)), (lower((product_values.part_number)::text))
64. 0.000 0.000 ↓ 0.0

Subquery Scan on product_values (cost=0.04..0.07 rows=1 width=128) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Unique (cost=0.04..0.05 rows=1 width=100) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=0.04..0.04 rows=1 width=100) (actual rows= loops=)

  • Sort Key: ((split_part(values_3.id, ':'::text, 1))::integer)
67. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_3 (cost=0.00..0.03 rows=1 width=100) (actual rows= loops=)

68.          

CTE specs

69. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.63..0.64 rows=1 width=32) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Append (cost=0.05..0.61 rows=9 width=64) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Result (cost=0.05..0.06 rows=1 width=64) (actual rows= loops=)

72.          

Initplan (for Result)

73. 0.000 0.000 ↓ 0.0

Unique (cost=0.04..0.05 rows=1 width=72) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Sort (cost=0.04..0.05 rows=1 width=72) (actual rows= loops=)

  • Sort Key: (("substring"(values_4."Display Size", '^[0-9.]+'::text))::double precision), ("substring"(values_4."Display Size", '[^0-9.]*$'::text))
75. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_4 (cost=0.00..0.03 rows=1 width=72) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

77.          

Initplan (for Result)

78. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_5."Form Factor"))
80. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_5 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

82.          

Initplan (for Result)

83. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_6."GPU"))
85. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_6 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

87.          

Initplan (for Result)

88. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_7."Market Segment"))
90. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_7 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

92.          

Initplan (for Result)

93. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_8."Operating System"))
95. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_8 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

97.          

Initplan (for Result)

98. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_9."Processor"))
100. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_9 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Result (cost=0.05..0.06 rows=1 width=64) (actual rows= loops=)

102.          

Initplan (for Result)

103. 0.000 0.000 ↓ 0.0

Unique (cost=0.04..0.05 rows=1 width=72) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Sort (cost=0.04..0.05 rows=1 width=72) (actual rows= loops=)

  • Sort Key: (("substring"(values_10."Std RAM", '^[0-9.]+'::text))::double precision), ("substring"(values_10."Std RAM", '[^0-9.]*$'::text))
105. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_10 (cost=0.00..0.03 rows=1 width=72) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

107.          

Initplan (for Result)

108. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_11."Storage"))
110. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_11 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

111. 0.000 0.000 ↓ 0.0

Result (cost=0.04..0.05 rows=1 width=64) (actual rows= loops=)

112.          

Initplan (for Result)

113. 0.000 0.000 ↓ 0.0

Unique (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

  • Sort Key: (lower(values_12."Touchscreen Display"))
115. 0.000 0.000 ↓ 0.0

CTE Scan on "values" values_12 (cost=0.00..0.02 rows=1 width=64) (actual rows= loops=)

116. 0.000 0.000 ↓ 0.0

Append (cost=0.03..0.22 rows=3 width=64) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.03..0.04 rows=1 width=64) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

CTE Scan on "values" (cost=0.00..0.02 rows=1 width=24) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=64) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..0.12 rows=1 width=64) (actual rows= loops=)

121. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..0.08 rows=1 width=96) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..0.05 rows=1 width=64) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

CTE Scan on merchants (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

CTE Scan on brands (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

125. 0.000 0.000 ↓ 0.0

CTE Scan on products (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

126. 0.000 0.000 ↓ 0.0

CTE Scan on specs (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)