Post

Postgres -- Planner

Posted
By Xiong Ding 11 min read

Q1. What plan contains? How is it executed?

You know what? The part I love most about Postgres codebase is its comments. Very informative and I learned a lot from them.

What does a query planner or optimizer do? It determines

  1. Scan method
  2. Join method
  3. Join order

https://github.com/postgres/postgres/blob/a3e6c6f929912f928fa405909d17bcbf0c1b03ee/src/backend/optimizer/plan/planner.c#L279

https://www.postgresql.org/docs/16/custom-scan.html

Single table relation

  1. Seq Scan
  2. Bitmap Index Scan & Parallel Bitmap Heap Scan
  3. Index Scan
  4. Index Only Scan

Join

  • innner
  • full, left, right
  • semi: a semi-join returns values from the left side of the relation that has a match with the right
  • anti:

Semi-join Optimization

Semi-join optimization is a step that converts top-level subqueries to joins. For example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Create table my_table
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Create table other_table
CREATE TABLE other_table (
    id INT PRIMARY KEY,
    description VARCHAR(100)
);

-- Insert sample data into my_table
INSERT INTO my_table (name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('David');

-- Insert sample data into other_table
INSERT INTO other_table (id, description) VALUES
(1, 'Engineer'),
(2, 'Doctor'),
(3, 'Artist'),
(4, 'Teacher');

And a query

1

select t1.id, t1.name from my_table t1 where t1.id in (select id from other_table t2 where t2.id in (1, 3));
Before optimization 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326

   {QUERY
   :commandType 1
   :querySource 0
   :canSetTag true
   :utilityStmt <>
   :resultRelation 0
   :hasAggs false
   :hasWindowFuncs false
   :hasTargetSRFs false
   :hasSubLinks true
   :hasDistinctOn false
   :hasRecursive false
   :hasModifyingCTE false
   :hasForUpdate false
   :hasRowSecurity false
   :isReturn false
   :cteList <>
   :rtable (
      {RANGETBLENTRY
      :alias
         {ALIAS
         :aliasname t1
         :colnames <>
         }
      :eref
         {ALIAS
         :aliasname t1
         :colnames ("id" "name")
         }
      :rtekind 0
      :relid 24582
      :inh true
      :relkind r
      :rellockmode 1
      :perminfoindex 1
      :tablesample <>
      :lateral false
      :inFromCl true
      :securityQuals <>
      }
   )
   :rteperminfos (
      {RTEPERMISSIONINFO
      :relid 24582
      :inh true
      :requiredPerms 2
      :checkAsUser 0
      :selectedCols (b 8 9)
      :insertedCols (b)
      :updatedCols (b)
      }
   )
   :jointree
      {FROMEXPR
      :fromlist (
         {RANGETBLREF
         :rtindex 1
         }
      )
      :quals
         {SUBLINK
         :subLinkType 2
         :subLinkId 0
         :testexpr
            {OPEXPR
            :opno 96
            :opfuncid 65
            :opresulttype 16
            :opretset false
            :opcollid 0
            :inputcollid 0
            :args (
               {VAR
               :varno 1
               :varattno 1
               :vartype 23
               :vartypmod -1
               :varcollid 0
               :varnullingrels (b)
               :varlevelsup 0
               :varnosyn 1
               :varattnosyn 1
               :location 45
               }
               {PARAM
               :paramkind 2
               :paramid 1
               :paramtype 23
               :paramtypmod -1
               :paramcollid 0
               :location -1
               }
            )
            :location 51
            }
         :operName ("=")
         :subselect
            {QUERY
            :commandType 1
            :querySource 0
            :canSetTag true
            :utilityStmt <>
            :resultRelation 0
            :hasAggs false
            :hasWindowFuncs false
            :hasTargetSRFs false
            :hasSubLinks false
            :hasDistinctOn false
            :hasRecursive false
            :hasModifyingCTE false
            :hasForUpdate false
            :hasRowSecurity false
            :isReturn false
            :cteList <>
            :rtable (
               {RANGETBLENTRY
               :alias
                  {ALIAS
                  :aliasname t2
                  :colnames <>
                  }
               :eref
                  {ALIAS
                  :aliasname t2
                  :colnames ("id" "description")
                  }
               :rtekind 0
               :relid 24588
               :inh true
               :relkind r
               :rellockmode 1
               :perminfoindex 1
               :tablesample <>
               :lateral false
               :inFromCl true
               :securityQuals <>
               }
            )
            :rteperminfos (
               {RTEPERMISSIONINFO
               :relid 24588
               :inh true
               :requiredPerms 2
               :checkAsUser 0
               :selectedCols (b 8)
               :insertedCols (b)
               :updatedCols (b)
               }
            )
            :jointree
               {FROMEXPR
               :fromlist (
                  {RANGETBLREF
                  :rtindex 1
                  }
               )
               :quals
                  {SCALARARRAYOPEXPR
                  :opno 96
                  :opfuncid 65
                  :hashfuncid 0
                  :negfuncid 0
                  :useOr true
                  :inputcollid 0
                  :args (
                     {VAR
                     :varno 1
                     :varattno 1
                     :vartype 23
                     :vartypmod -1
                     :varcollid 0
                     :varnullingrels (b)
                     :varlevelsup 0
                     :varnosyn 1
                     :varattnosyn 1
                     :location 91
                     }
                     {ARRAYEXPR
                     :array_typeid 1007
                     :array_collid 0
                     :element_typeid 23
                     :elements (
                        {CONST
                        :consttype 23
                        :consttypmod -1
                        :constcollid 0
                        :constlen 4
                        :constbyval true
                        :constisnull false
                        :location 101
                        :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
                        }
                        {CONST
                        :consttype 23
                        :consttypmod -1
                        :constcollid 0
                        :constlen 4
                        :constbyval true
                        :constisnull false
                        :location 104
                        :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
                        }
                     )
                     :multidims false
                     :location -1
                     }
                  )
                  :location 97
                  }
               }
            :mergeActionList <>
            :mergeTargetRelation 0
            :mergeJoinCondition <>
            :targetList (
               {TARGETENTRY
               :expr
                  {VAR
                  :varno 1
                  :varattno 1
                  :vartype 23
                  :vartypmod -1
                  :varcollid 0
                  :varnullingrels (b)
                  :varlevelsup 0
                  :varnosyn 1
                  :varattnosyn 1
                  :location 62
                  }
               :resno 1
               :resname id
               :ressortgroupref 0
               :resorigtbl 24588
               :resorigcol 1
               :resjunk false
               }
            )
            :override 0
            :onConflict <>
            :returningList <>
            :groupClause <>
            :groupDistinct false
            :groupingSets <>
            :havingQual <>
            :windowClause <>
            :distinctClause <>
            :sortClause <>
            :limitOffset <>
            :limitCount <>
            :limitOption 0
            :rowMarks <>
            :setOperations <>
            :constraintDeps <>
            :withCheckOptions <>
            :stmt_location 0
            :stmt_len 0
            }
         :location 51
         }
      }
   :mergeActionList <>
   :mergeTargetRelation 0
   :mergeJoinCondition <>
   :targetList (
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 1
         :vartype 23
         :vartypmod -1
         :varcollid 0
         :varnullingrels (b)
         :varlevelsup 0
         :varnosyn 1
         :varattnosyn 1
         :location 7
         }
      :resno 1
      :resname id
      :ressortgroupref 0
      :resorigtbl 24582
      :resorigcol 1
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 2
         :vartype 1043
         :vartypmod 54
         :varcollid 100
         :varnullingrels (b)
         :varlevelsup 0
         :varnosyn 1
         :varattnosyn 2
         :location 14
         }
      :resno 2
      :resname name
      :ressortgroupref 0
      :resorigtbl 24582
      :resorigcol 2
      :resjunk false
      }
   )
   :override 0
   :onConflict <>
   :returningList <>
   :groupClause <>
   :groupDistinct false
   :groupingSets <>
   :havingQual <>
   :windowClause <>
   :distinctClause <>
   :sortClause <>
   :limitOffset <>
   :limitCount <>
   :limitOption 0
   :rowMarks <>
   :setOperations <>
   :constraintDeps <>
   :withCheckOptions <>
   :stmt_location 0
   :stmt_len 107
   }
After optimization 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277

   {QUERY
   :commandType 1
   :querySource 0
   :canSetTag true
   :utilityStmt <>
   :resultRelation 0
   :hasAggs false
   :hasWindowFuncs false
   :hasTargetSRFs false
   :hasSubLinks true
   :hasDistinctOn false
   :hasRecursive false
   :hasModifyingCTE false
   :hasForUpdate false
   :hasRowSecurity false
   :isReturn false
   :cteList <>
   :rtable (
      {RANGETBLENTRY
      :alias
         {ALIAS
         :aliasname t1
         :colnames <>
         }
      :eref
         {ALIAS
         :aliasname t1
         :colnames ("id" "name")
         }
      :rtekind 0
      :relid 24582
      :inh false
      :relkind r
      :rellockmode 1
      :perminfoindex 1
      :tablesample <>
      :lateral false
      :inFromCl true
      :securityQuals <>
      }
      {RANGETBLENTRY
      :alias
         {ALIAS
         :aliasname ANY_subquery
         :colnames <>
         }
      :eref
         {ALIAS
         :aliasname ANY_subquery
         :colnames ("id")
         }
      :rtekind 1
      :subquery <>
      :security_barrier false
      :relid 0
      :inh false
      :relkind <>
      :rellockmode 0
      :perminfoindex 0
      :lateral false
      :inFromCl false
      :securityQuals <>
      }
      {RANGETBLENTRY
      :alias
         {ALIAS
         :aliasname t2
         :colnames <>
         }
      :eref
         {ALIAS
         :aliasname t2
         :colnames ("id" "description")
         }
      :rtekind 0
      :relid 24588
      :inh false
      :relkind r
      :rellockmode 1
      :perminfoindex 2
      :tablesample <>
      :lateral false
      :inFromCl true
      :securityQuals <>
      }
   )
   :rteperminfos (
      {RTEPERMISSIONINFO
      :relid 24582
      :inh true
      :requiredPerms 2
      :checkAsUser 0
      :selectedCols (b 8 9)
      :insertedCols (b)
      :updatedCols (b)
      }
      {RTEPERMISSIONINFO
      :relid 24588
      :inh true
      :requiredPerms 2
      :checkAsUser 0
      :selectedCols (b 8)
      :insertedCols (b)
      :updatedCols (b)
      }
   )
   :jointree
      {FROMEXPR
      :fromlist (
         {JOINEXPR
         :jointype 4
         :isNatural false
         :larg
            {FROMEXPR
            :fromlist (
               {RANGETBLREF
               :rtindex 1
               }
            )
            :quals <>
            }
         :rarg
            {FROMEXPR
            :fromlist (
               {RANGETBLREF
               :rtindex 3
               }
            )
            :quals (
               {SCALARARRAYOPEXPR
               :opno 96
               :opfuncid 65
               :hashfuncid 0
               :negfuncid 0
               :useOr true
               :inputcollid 0
               :args (
                  {VAR
                  :varno 3
                  :varattno 1
                  :vartype 23
                  :vartypmod -1
                  :varcollid 0
                  :varnullingrels (b)
                  :varlevelsup 0
                  :varnosyn 3
                  :varattnosyn 1
                  :location 91
                  }
                  {CONST
                  :consttype 1007
                  :consttypmod -1
                  :constcollid 0
                  :constlen -1
                  :constbyval false
                  :constisnull false
                  :location -1
                  :constvalue 32 [ -128 0 0 0 1 0 0 0 0 0 0 0 23 0 0 0 2 0 0 0
                   1 0 0 0 1 0 0 0 3 0 0 0 ]
                  }
               )
               :location 97
               }
            )
            }
         :usingClause <>
         :join_using_alias <>
         :quals (
            {OPEXPR
            :opno 96
            :opfuncid 65
            :opresulttype 16
            :opretset false
            :opcollid 0
            :inputcollid 0
            :args (
               {VAR
               :varno 1
               :varattno 1
               :vartype 23
               :vartypmod -1
               :varcollid 0
               :varnullingrels (b)
               :varlevelsup 0
               :varnosyn 1
               :varattnosyn 1
               :location 45
               }
               {VAR
               :varno 3
               :varattno 1
               :vartype 23
               :vartypmod -1
               :varcollid 0
               :varnullingrels (b)
               :varlevelsup 0
               :varnosyn 3
               :varattnosyn 1
               :location 62
               }
            )
            :location 51
            }
         )
         :alias <>
         :rtindex 0
         }
      )
      :quals <>
      }
   :mergeActionList <>
   :mergeTargetRelation 0
   :mergeJoinCondition <>
   :targetList (
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 1
         :vartype 23
         :vartypmod -1
         :varcollid 0
         :varnullingrels (b)
         :varlevelsup 0
         :varnosyn 1
         :varattnosyn 1
         :location 7
         }
      :resno 1
      :resname id
      :ressortgroupref 0
      :resorigtbl 24582
      :resorigcol 1
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 2
         :vartype 1043
         :vartypmod 54
         :varcollid 100
         :varnullingrels (b)
         :varlevelsup 0
         :varnosyn 1
         :varattnosyn 2
         :location 14
         }
      :resno 2
      :resname name
      :ressortgroupref 0
      :resorigtbl 24582
      :resorigcol 2
      :resjunk false
      }
   )
   :override 0
   :onConflict <>
   :returningList <>
   :groupClause <>
   :groupDistinct false
   :groupingSets <>
   :havingQual <>
   :windowClause <>
   :distinctClause <>
   :sortClause <>
   :limitOffset <>
   :limitCount <>
   :limitOption 0
   :rowMarks <>
   :setOperations <>
   :constraintDeps <>
   :withCheckOptions <>
   :stmt_location 0
   :stmt_len 107
   }

pg_hint_plan

According to installation guide, https://github.com/ossc-db/pg_hint_plan/blob/master/docs/installation.md

1
2

$ tar xzvf pg_hint_plan-1.x.x.tar.gz
$ cd pg_hint_plan-1.x.x

Then we need to make some change to the Makefile.

1
2

# PG_CONFIG = pg_config
PG_CONFIG = ~/code/postgres/build_dir/install_dir/bin/pg_config

1
2
3

$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -Wmissing-format-attribute -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -g -ggdb -Og -g3 -fno-omit-frame-pointer  -fvisibility=hidden -I. -I./ -I/Users/xiongding/code/postgres/build_dir/install_dir/include/server -I/Users/xiongding/code/postgres/build_dir/install_dir/include/internal  -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX14.5.sdk    -c -o pg_hint_plan.o pg_hint_plan.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -Wmissing-format-attribute -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -g -ggdb -Og -g3 -fno-omit-frame-pointer  -fvisibility=hidden pg_hint_plan.o -L/Users/xiongding/code/postgres/build_dir/install_dir/lib -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX14.5.sdk   -Wl,-dead_strip_dylibs   -fvisibility=hidden -bundle -bundle_loader /Users/xiongding/code/postgres/build_dir/install_dir/bin/postgres -o pg_hint_plan.dylib

1
2
3
4
5
6
7

$ make install
/opt/homebrew/bin/gmkdir -p '/Users/xiongding/code/postgres/build_dir/install_dir/share/extension'
/opt/homebrew/bin/gmkdir -p '/Users/xiongding/code/postgres/build_dir/install_dir/share/extension'
/opt/homebrew/bin/gmkdir -p '/Users/xiongding/code/postgres/build_dir/install_dir/lib'
/opt/homebrew/bin/ginstall -c -m 644 .//pg_hint_plan.control '/Users/xiongding/code/postgres/build_dir/install_dir/share/extension/'
/opt/homebrew/bin/ginstall -c -m 644 .//pg_hint_plan--1.3.0.sql .//pg_hint_plan--1.3.0--1.3.1.sql .//pg_hint_plan--1.3.1--1.3.2.sql .//pg_hint_plan--1.3.2--1.3.3.sql .//pg_hint_plan--1.3.3--1.3.4.sql .//pg_hint_plan--1.3.5--1.3.6.sql .//pg_hint_plan--1.3.4--1.3.5.sql .//pg_hint_plan--1.3.6--1.3.7.sql .//pg_hint_plan--1.3.7--1.3.8.sql .//pg_hint_plan--1.3.8--1.3.9.sql .//pg_hint_plan--1.3.9--1.3.10.sql .//pg_hint_plan--1.3.10--1.4.sql .//pg_hint_plan--1.4--1.4.1.sql .//pg_hint_plan--1.4.1--1.4.2.sql .//pg_hint_plan--1.4.2--1.4.3.sql .//pg_hint_plan--1.4.3--1.5.sql .//pg_hint_plan--1.5--1.5.1.sql .//pg_hint_plan--1.5.1--1.5.2.sql .//pg_hint_plan--1.5.2--1.6.0.sql .//pg_hint_plan--1.6.0--1.6.1.sql  '/Users/xiongding/code/postgres/build_dir/install_dir/share/extension/'
/opt/homebrew/bin/ginstall -c -m 755  pg_hint_plan.dylib '/Users/xiongding/code/postgres/build_dir/install_dir/lib/'

Then load it

1
2

postgres=# LOAD 'pg_hint_plan';
LOAD
database, postgres
postgres
This post is licensed under CC BY 4.0 by the author.