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 1/2";
19 setplan0(0); # Without "Unknown Key"
20 print "($plan_no, \'$title\',\n";
21 print " $escape'$plan')";
24 $title = "###### Plan $plan_no: all properties 2/2";
26 print ",($plan_no, \'$title\',\n";
27 print " $escape'$plan')";
30 $title = "###### Plan $plan_no: some properties plus unknown key";
31 setplan0(1); # With "Unknown Key"
32 print ",($plan_no, \'$title\',\n";
33 print " $escape'$plan')";
41 next if (!/^###### (.*$)/);
42 $title = "###### Plan $plan_no: $1";
44 } elsif ($state == 1) {
45 # edit auto_explain's result
46 next if (/^psql:makeplanfile.sql/);
51 next if (!/^( *)\{ *\+?$/);
54 $plan =~ s/^ (.*[^ ]) *\+$/$1\n/;
58 } elsif ($state == 2) {
59 # edit auto_explain's result
60 next if (/^ "Query Text":/);
62 if (/^$indent} *\+?$/) {
66 $l =~ s/^ (.*[^ ]) *\+$/$1/;
68 $plan .= "\n" if ($state == 2);
69 } elsif ($state == 3) {
71 if ($plan =~ /'/ || $plan =~ /\\\"/) {
74 # Add escape char for '''
76 # Add escape char for '\"'
77 $plan =~ s/\\\"/\\\\\"/g;
79 # Remove "Total Runtime" line.
80 $plan =~ s/,\n *"Total Runtime":.*\n/\n/;
82 print ",\n($plan_no, \'$title\',\n";
83 print " $escape'$plan')";
95 \echo ###### set shortened JSON
96 UPDATE plans SET splan = pg_store_plans_shorten(lplan);
98 \echo ###### tag abbreviation test
99 SELECT splan FROM plans WHERE id in (-1, -2);
101 \echo ###### JSON properties round-trip test: !!! This shouldn''''t return a row
103 where pg_store_plans_jsonplan(splan) <> lplan;
105 \pset format unaligned
107 \pset recordsep '\n\n=======\n'
108 \echo ###### format conversion tests
109 SELECT '### '||'yaml-short '||title||E'\n'||
110 pg_store_plans_yamlplan(splan)
111 FROM plans WHERE id BETWEEN 1 AND 3 or id = 1 ORDER BY id;
112 \echo ##################
113 SELECT '### '||'xml-short '||title||E'\n'||
114 pg_store_plans_xmlplan(splan)
115 FROM plans WHERE id BETWEEN 4 AND 6 or id = 1 ORDER BY id;
117 \echo ###### text format output test
118 SELECT '### '||'TEXT-short '||title||E'\n'||
119 pg_store_plans_textplan(splan)
120 FROM plans WHERE id >= 0 ORDER BY id;
123 \echo ###### long-json-as-a-source test
124 SELECT '### '||'yaml-long JSON '||title||E'\n'||
125 pg_store_plans_yamlplan(lplan)
126 FROM plans WHERE id = 1 ORDER BY id;
127 \echo ##################
128 SELECT '### '||'xml-long JSON '||title||E'\n'||
129 pg_store_plans_xmlplan(lplan)
130 FROM plans WHERE id = 1 ORDER BY id;
131 \echo ##################
132 SELECT '### '||'text-long JSON '||title||E'\n'||
133 pg_store_plans_xmlplan(lplan)
134 FROM plans WHERE id = 1 ORDER BY id;
136 \echo ###### chopped-source test
137 SELECT '### '||'inflate-chopped '||title||E'\n'||
138 pg_store_plans_jsonplan(substring(splan from 1 for char_length(splan) / 3))
139 FROM plans WHERE id BETWEEN 16 AND 18 ORDER BY id;
140 \echo ##################
141 SELECT '### '||'yaml-chopped '||title||E'\n'||
142 pg_store_plans_yamlplan(substring(splan from 1 for char_length(splan) / 3))
143 FROM plans WHERE id BETWEEN 19 AND 21 ORDER BY id;
144 \echo ##################
145 SELECT '### '||'xml-chopped '||title||E'\n'||
146 pg_store_plans_xmlplan(substring(splan from 1 for char_length(splan) / 3))
147 FROM plans WHERE id BETWEEN 22 AND 24 ORDER BY id;
148 \echo ##################
149 SELECT '### '||'text-chopped '||title||E'\n'||
150 pg_store_plans_textplan(substring(splan from 1 for char_length(splan) / 3))
151 FROM plans WHERE id BETWEEN 25 AND 27 ORDER BY id;
153 \echo ###### shorten test
154 SELECT '### '||'shorten '||title||E'\n'||
155 pg_store_plans_shorten(lplan)
156 FROM plans WHERE id = -2 ORDER BY id;
157 \echo ###### normalize test
158 SELECT '### '||'normalize '||title||E'\n'||
159 pg_store_plans_normalize(lplan)
160 FROM plans ORDER BY id;
165 my($addunknown) = @_;
170 "Node Type": "Result",
171 "Node Type": "ModifyTable",
172 "Node Type": "Append",
173 "Node Type": "Merge Append",
174 "Node Type": "Recursive Union",
175 "Node Type": "BitmapAnd",
176 "Node Type": "BitmapOr",
177 "Node Type": "Seq Scan",
178 "Node Type": "Index Scan",
179 "Node Type": "Index Only Scan",
180 "Node Type": "Bitmap Index Scan",
181 "Node Type": "Bitmap Heap Scan",
182 "Node Type": "Tid Scan",
183 "Node Type": "Subquery Scan",
184 "Node Type": "Function Scan",
185 "Node Type": "Values Scan",
186 "Node Type": "CTE Scan",
187 "Node Type": "WorkTable Scan",
188 "Node Type": "Foreign Scan",
189 "Node Type": "Nested Loop",
190 "Node Type": "Merge Join",
191 "Node Type": "Hash Join",
192 "Node Type": "Materialize",
194 "Node Type": "Group",
195 "Node Type": "Aggregate",
196 "Node Type": "WindowAgg",
197 "Node Type": "Unique",
199 "Node Type": "SetOp",
200 "Node Type": "LockRows",
201 "Node Type": "Limit",
202 "Node Type": "Sample Scan",
203 "Node Type": "Gather",
204 "Node Type": "ProjectSet",
205 "Node Type": "Table Function Scan",
206 "Node Type": "Named Tuplestore Scan",
207 "Node Type": "Gather Merge",
208 "Parent Relationship": "Outer",
209 "Parent Relationship": "Inner",
210 "Parent Relationship": "Subquery",
211 "Parent Relationship": "Member",
212 "Parent Relationship": "InitPlan",
213 "Parent Relationship": "SubPlan",
214 "Scan Direction": "Backward",
215 "Scan Direction": "NoMovement",
216 "Scan Direction": "Forward",
226 "Strategy": "Sorted",
227 "Strategy": "Hashed",
229 "Join Type": "Inner",
232 "Join Type": "Right",
235 "Command": "Intersect",
236 "Command": "Intersect All",
238 "Command": "Except All",
239 "Sort Method": "top-N heapsort",
240 "Sort Method": "quicksort",
241 "Sort Method": "external sort",
242 "Sort Method": "external merge",
243 "Sort Method": "still in progress",
246 "Grouping Sets": "a",
250 "Parallel Aware": "true",
251 "Workers Planned": "0",
252 "Workers Launched": "0",
254 "Worker Number": "0",
255 "Async Capable": "false",
262 "Conflict Resolution": "a",
263 "Conflict Arbiter Indexes": "a",
264 "Tuples Inserted": 0,
265 "Conflicting Tuples": 0,
266 "Target Tables": "a",
267 "Operation": "Insert",
268 "Operation": "Delete",
269 "Operation": "Update",
275 "Constraint Name": 0,
281 "Actual Startup Time": 0,
282 "Actual Total Time": 0,
286 "Shared Hit Blocks": 0,
287 "Shared Read Blocks": 0,
288 "Shared Dirtied Blocks": 0,
289 "Shared Written Blocks": 0,
290 "Local Hit Blocks": 0,
291 "Local Read Blocks": 0,
292 "Local Dirtied Blocks": 0,
293 "Local Written Blocks": 0,
294 "Temp Read Blocks": 0,
295 "Temp Written Blocks": 0,
298 "Sort Space Used": 0,
299 "Sort Space Type": "Disk",
300 "Sort Space Type": "Memory",
301 "Peak Memory Usage": 0,
302 "Original Hash Batches": 0,
303 "Original Hash Buckets": 0
307 $plan .= ",\n \"Unknown Key\": \"Unknown Value\"";
314 my($addunknown) = @_;
319 "Rows Removed by Filter": 0,
320 "Rows Removed by Index Recheck": 0,
325 "Exact Heap Blocks": 0,
326 "Lossy Heap Blocks": 0,
327 "Rows Removed by Join Filter": 0,
328 "Target Tables": "dummy",
329 "Conflict Resolution": "NOTHING",
330 "Conflict Arbiter Indexes": "ia",
331 "Tuples Inserted": 123,
332 "Conflicting Tuples": 234,
333 "Sampling Method": "system",
334 "Sampling Parameters": ["''10''::real"],
335 "Repeatable Seed": "''0''::double precision",
341 # Avoid trailing new line