4 \echo ###### Prepare for the test
6 SET client_min_messages = 'error';
7 CREATE EXTENSION IF NOT EXISTS pg_store_plans;
8 DROP TABLE IF EXISTS plans;
9 CREATE TABLE plans (id int, title text, lplan text, splan text);
10 SET client_min_messages = 'notice';
13 \echo ###### insert original JSON plans
14 INSERT INTO plans (VALUES
18 $title = "###### Plan $plan_no: all properties";
19 setplan0(0); # Without "Unknown Key"
20 print "($plan_no, \'$title\',\n";
21 print " $escape'$plan')";
24 $title = "###### Plan $plan_no: all properties plus unknown key";
25 setplan0(1); # With "Unknown Key"
26 print ",($plan_no, \'$title\',\n";
27 print " $escape'$plan')";
34 next if (!/^###### (.*$)/);
35 $title = "###### Plan $plan_no: $1";
37 } elsif ($state == 1) {
41 next if (!/^ { *\+$/);
43 $plan =~ s/^ (.*[^ ]) *\+$/$1\n/;
45 } elsif ($state == 2) {
50 $l =~ s/^ (.*[^ ]) *\+$/$1/;
52 $plan .= "\n" if ($state == 2);
53 } elsif ($state == 3) {
55 if ($plan =~ /'/ || $plan =~ /\\\"/) {
58 # Add escape char for '''
60 # Add escape char for '\"'
61 $plan =~ s/\\\"/\\\\\"/g;
63 # Remove "Total Runtime" line.
64 $plan =~ s/,\n *"Total Runtime":.*\n/\n/;
66 print ",\n($plan_no, \'$title\',\n";
67 print " $escape'$plan')";
79 \echo ###### set shortened JSON
80 UPDATE plans SET splan = pg_store_plans_shorten(lplan);
82 \echo ###### tag abbreviation test
83 SELECT splan FROM plans WHERE id = -1;
85 \echo ###### JSON properties round-trip test
87 where pg_store_plans_jsonplan(splan) <> lplan;
89 \pset format unaligned
91 \pset recordsep '\n\n=======\n'
92 \echo ###### format conversion tests
93 SELECT '### '||'yaml-short '||title||E'\n'||
94 pg_store_plans_yamlplan(splan)
95 FROM plans WHERE id BETWEEN 1 AND 3 or id = 1 ORDER BY id;
96 \echo ##################
97 SELECT '### '||'xml-short '||title||E'\n'||
98 pg_store_plans_xmlplan(splan)
99 FROM plans WHERE id BETWEEN 4 AND 6 or id = 1 ORDER BY id;
101 \echo ###### text format output test
102 SELECT '### '||'TEXT-short '||title||E'\n'||
103 pg_store_plans_textplan(splan)
104 FROM plans ORDER BY id;
106 \echo ###### long-json-as-a-source test
107 SELECT '### '||'yaml-long JSON '||title||E'\n'||
108 pg_store_plans_yamlplan(lplan)
109 FROM plans WHERE id = 1 ORDER BY id;
110 \echo ##################
111 SELECT '### '||'xml-long JSON '||title||E'\n'||
112 pg_store_plans_xmlplan(lplan)
113 FROM plans WHERE id = 1 ORDER BY id;
114 \echo ##################
115 SELECT '### '||'text-long JSON '||title||E'\n'||
116 pg_store_plans_xmlplan(lplan)
117 FROM plans WHERE id = 1 ORDER BY id;
119 \echo ###### chopped-source test
120 SELECT '### '||'inflate-chopped '||title||E'\n'||
121 pg_store_plans_jsonplan(substring(splan from 1 for char_length(splan) / 3))
122 FROM plans WHERE id BETWEEN 16 AND 18 ORDER BY id;
123 \echo ##################
124 SELECT '### '||'yaml-chopped '||title||E'\n'||
125 pg_store_plans_yamlplan(substring(splan from 1 for char_length(splan) / 3))
126 FROM plans WHERE id BETWEEN 19 AND 21 ORDER BY id;
127 \echo ##################
128 SELECT '### '||'xml-chopped '||title||E'\n'||
129 pg_store_plans_xmlplan(substring(splan from 1 for char_length(splan) / 3))
130 FROM plans WHERE id BETWEEN 22 AND 24 ORDER BY id;
131 \echo ##################
132 SELECT '### '||'text-chopped '||title||E'\n'||
133 pg_store_plans_textplan(substring(splan from 1 for char_length(splan) / 3))
134 FROM plans WHERE id BETWEEN 25 AND 27 ORDER BY id;
136 \echo ###### shorten test
137 SELECT '### '||'shorten '||title||E'\n'||
138 pg_store_plans_shorten(lplan)
139 FROM plans WHERE id = -2 ORDER BY id;
140 \echo ###### normalize test
141 SELECT '### '||'normalize '||title||E'\n'||
142 pg_store_plans_normalize(lplan)
143 FROM plans WHERE id BETWEEN 1 AND 7 ORDER BY id;
148 my($addunknown) = @_;
153 "Node Type": "Result",
154 "Node Type": "ModifyTable",
155 "Node Type": "Append",
156 "Node Type": "Merge Append",
157 "Node Type": "Recursive Union",
158 "Node Type": "BitmapAnd",
159 "Node Type": "BitmapOr",
160 "Node Type": "Seq Scan",
161 "Node Type": "Index Scan",
162 "Node Type": "Index Only Scan",
163 "Node Type": "Bitmap Index Scan",
164 "Node Type": "Bitmap Heap Scan",
165 "Node Type": "Tid Scan",
166 "Node Type": "Subquery Scan",
167 "Node Type": "Function Scan",
168 "Node Type": "Values Scan",
169 "Node Type": "CTE Scan",
170 "Node Type": "Workable Scan",
171 "Node Type": "Foreign Scan",
172 "Node Type": "Nested Loop",
173 "Node Type": "Merge Join",
174 "Node Type": "Hash Join",
175 "Node Type": "Materialize",
177 "Node Type": "Group",
178 "Node Type": "Aggregate",
179 "Node Type": "WindowAgg",
180 "Node Type": "Unique",
182 "Node Type": "SetOp",
183 "Node Type": "LockRows",
184 "Node Type": "Limit",
185 "Parent Relationship": "Outer",
186 "Parent Relationship": "Inner",
187 "Parent Relationship": "Subquery",
188 "Parent Relationship": "Member",
189 "Parent Relationship": "InitPlan",
190 "Parent Relationship": "SubPlan",
191 "Scan Direction": "Backward",
192 "Scan Direction": "NoMovement",
193 "Scan Direction": "Forward",
203 "Strategy": "Sorted",
204 "Strategy": "Hashed",
205 "Join Type": "Inner",
208 "Join Type": "Right",
211 "Command": "Intersect",
212 "Command": "Intersect All",
214 "Command": "Except All",
215 "Sort Method": "top-N heapsort",
216 "Sort Method": "quicksort",
217 "Sort Method": "external sort",
218 "Sort Method": "external merge",
219 "Sort Method": "still in progress",
227 "Operation": "Insert",
228 "Operation": "Delete",
229 "Operation": "Update",
235 "Constraint Name": 0,
241 "Actual Startup Time": 0,
242 "Actual Total Time": 0,
246 "Shared Hit Blocks": 0,
247 "Shared Read Blocks": 0,
248 "Shared Dirtied Blocks": 0,
249 "Shared Written Blocks": 0,
250 "Local Hit Blocks": 0,
251 "Local Read Blocks": 0,
252 "Local Dirtied Blocks": 0,
253 "Local Written Blocks": 0,
254 "Temp Read Blocks": 0,
255 "Temp Written Blocks": 0,
258 "Sort Space Used": 0,
259 "Sort Space Type": "Disk",
260 "Sort Space Type": "Memory",
261 "Peak Memory Usage": 0,
262 "Original Hash Batches": 0,
265 "Rows Removed by Filter": 0,
266 "Rows Removed by Index Recheck": 0,
271 "Exact Heap Blocks": 0,
272 "Lossy Heap Blocks": 0,
273 "Rows Removed by Join Filter": 0
277 $plan .= ",\n \"Unknown Key\": \"Unknown Value\"";