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";
20 print "($plan_no, \'$title\',\n";
21 print " $escape'$plan')";
28 next if (!/^###### (.*$)/);
29 $title = "###### Plan $plan_no: $1";
31 } elsif ($state == 1) {
35 next if (!/^ { *\+$/);
37 $plan =~ s/^ (.*[^ ]) *\+$/$1\n/;
39 } elsif ($state == 2) {
44 $l =~ s/^ (.*[^ ]) *\+$/$1/;
46 $plan .= "\n" if ($state == 2);
47 } elsif ($state == 3) {
49 if ($plan =~ /'/ || $plan =~ /\\\"/) {
52 # Add escape char for '''
54 # Add escape char for '\"'
55 $plan =~ s/\\\"/\\\\\"/g;
57 # Remove "Total Runtime" line.
58 $plan =~ s/,\n *"Total Runtime":.*\n/\n/;
60 print ",\n($plan_no, \'$title\',\n";
61 print " $escape'$plan')";
73 \echo ###### set shortened JSON
74 UPDATE plans SET splan = pg_store_plans_shorten(lplan);
76 \echo ###### JSON properties round-trip test
78 where pg_store_plans_jsonplan(splan) <> lplan;
80 \pset format unaligned
82 \pset recordsep '\n\n=======\n'
83 \echo ###### format conversion tests
84 SELECT '### '||'yaml-short '||title||E'\n'||
85 pg_store_plans_yamlplan(splan)
86 FROM plans WHERE id BETWEEN 1 AND 3 or id = 1 ORDER BY id;
87 \echo ##################
88 SELECT '### '||'xml-short '||title||E'\n'||
89 pg_store_plans_xmlplan(splan)
90 FROM plans WHERE id BETWEEN 4 AND 6 or id = 1 ORDER BY id;
92 \echo ###### text format output test
93 SELECT '### '||'TEXT-short '||title||E'\n'||
94 pg_store_plans_textplan(splan)
95 FROM plans ORDER BY id;
97 \echo ###### long-json-as-a-source test
98 SELECT '### '||'yaml-long JSON '||title||E'\n'||
99 pg_store_plans_yamlplan(lplan)
100 FROM plans WHERE id = 1 ORDER BY id;
101 \echo ##################
102 SELECT '### '||'xml-long JSON '||title||E'\n'||
103 pg_store_plans_xmlplan(lplan)
104 FROM plans WHERE id = 1 ORDER BY id;
105 \echo ##################
106 SELECT '### '||'text-long JSON '||title||E'\n'||
107 pg_store_plans_xmlplan(lplan)
108 FROM plans WHERE id = 1 ORDER BY id;
110 \echo ###### chopped-source test
111 SELECT '### '||'inflate-chopped '||title||E'\n'||
112 pg_store_plans_jsonplan(substring(splan from 1 for char_length(splan) / 3))
113 FROM plans WHERE id BETWEEN 16 AND 18 ORDER BY id;
114 \echo ##################
115 SELECT '### '||'yaml-chopped '||title||E'\n'||
116 pg_store_plans_yamlplan(substring(splan from 1 for char_length(splan) / 3))
117 FROM plans WHERE id BETWEEN 19 AND 21 ORDER BY id;
118 \echo ##################
119 SELECT '### '||'xml-chopped '||title||E'\n'||
120 pg_store_plans_xmlplan(substring(splan from 1 for char_length(splan) / 3))
121 FROM plans WHERE id BETWEEN 22 AND 24 ORDER BY id;
122 \echo ##################
123 SELECT '### '||'text-chopped '||title||E'\n'||
124 pg_store_plans_textplan(substring(splan from 1 for char_length(splan) / 3))
125 FROM plans WHERE id BETWEEN 25 AND 27 ORDER BY id;
127 \echo ###### shorten test
128 SELECT '### '||'shorten '||title||E'\n'||
129 pg_store_plans_shorten(lplan)
130 FROM plans WHERE id = 0 ORDER BY id;
131 \echo ###### normalize test
132 SELECT '### '||'normalize '||title||E'\n'||
133 pg_store_plans_normalize(lplan)
134 FROM plans WHERE id BETWEEN 1 AND 3 ORDER BY id;
143 "Node Type": "Result",
144 "Node Type": "ModifyTable",
145 "Node Type": "Append",
146 "Node Type": "Merge Append",
147 "Node Type": "Recursive Union",
148 "Node Type": "BitmapAnd",
149 "Node Type": "BitmapOr",
150 "Node Type": "Seq Scan",
151 "Node Type": "Index Scan",
152 "Node Type": "Index Only Scan",
153 "Node Type": "Bitmap Index Scan",
154 "Node Type": "Bitmap Heap Scan",
155 "Node Type": "Tid Scan",
156 "Node Type": "Subquery Scan",
157 "Node Type": "Function Scan",
158 "Node Type": "Values Scan",
159 "Node Type": "CTE Scan",
160 "Node Type": "Workable Scan",
161 "Node Type": "Foreign Scan",
162 "Node Type": "Nested Loop",
163 "Node Type": "Merge Join",
164 "Node Type": "Hash Join",
165 "Node Type": "Materialize",
167 "Node Type": "Group",
168 "Node Type": "Aggregate",
169 "Node Type": "WindowAgg",
170 "Node Type": "Unique",
172 "Node Type": "SetOp",
173 "Node Type": "LockRows",
174 "Node Type": "Limit",
175 "Parent Relationship": "Outer",
176 "Parent Relationship": "Inner",
177 "Parent Relationship": "Subquery",
178 "Parent Relationship": "Member",
179 "Parent Relationship": "InitPlan",
180 "Parent Relationship": "SubPlan",
181 "Scan Direction": "Backward",
182 "Scan Direction": "NoMovement",
183 "Scan Direction": "Forward",
193 "Strategy": "Sorted",
194 "Strategy": "Hashed",
195 "Join Type": "Inner",
198 "Join Type": "Right",
201 "Command": "Intersect",
202 "Command": "Intersect All",
204 "Command": "Except All",
205 "Sort Method": "top-N heapsort",
206 "Sort Method": "quicksort",
207 "Sort Method": "external sort",
208 "Sort Method": "external merge",
209 "Sort Method": "still in progress",
217 "Operation": "Insert",
218 "Operation": "Delete",
219 "Operation": "Update",
225 "Constraint Name": 0,
231 "Actual Startup Time": 0,
232 "Actual Total Time": 0,
236 "Shared Hit Blocks": 0,
237 "Shared Read Blocks": 0,
238 "Shared Dirtied Blocks": 0,
239 "Shared Written Blocks": 0,
240 "Local Hit Blocks": 0,
241 "Local Read Blocks": 0,
242 "Local Dirtied Blocks": 0,
243 "Local Written Blocks": 0,
244 "Temp Read Blocks": 0,
245 "Temp Written Blocks": 0,
248 "Sort Space Used": 0,
249 "Sort Space Type": "Disk",
250 "Sort Space Type": "Memory",
251 "Peak Memory Usage": 0,
252 "Original Hash Batches": 0,
255 "Rows Removed by Filter": 0,
256 "Rows Removed by Index Recheck": 0,
259 "Unknown Key": "Unknown Value"