10devel support
[plantuner.git] / plantuner.c
1 /*
2  * Copyright (c) 2009 Teodor Sigaev <teodor@sigaev.ru>
3  * All rights reserved.
4  *
5  * Redistribution and use in source and binary forms, with or without
6  * modification, are permitted provided that the following conditions
7  * are met:
8  * 1. Redistributions of source code must retain the above copyright
9  *              notice, this list of conditions and the following disclaimer.
10  * 2. Redistributions in binary form must reproduce the above copyright
11  *              notice, this list of conditions and the following disclaimer in the
12  *              documentation and/or other materials provided with the distribution.
13  * 3. Neither the name of the author nor the names of any co-contributors
14  *              may be used to endorse or promote products derived from this software
15  *              without specific prior written permission.
16  *
17  * THIS SOFTWARE IS PROVIDED BY CONTRIBUTORS ``AS IS'' AND ANY EXPRESS
18  * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
19  * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
20  * ARE DISCLAIMED. IN NO EVENT SHALL CONTRIBUTORS BE LIABLE FOR ANY
21  * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
22  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
23  * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
24  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
25  * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
26  * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
27  * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28  */
29
30 #include <postgres.h>
31
32 #include <fmgr.h>
33 #include <access/heapam.h>
34 #include <catalog/namespace.h>
35 #include <catalog/pg_class.h>
36 #include <nodes/pg_list.h>
37 #include <optimizer/plancat.h>
38 #include <storage/bufmgr.h>
39 #include <utils/builtins.h>
40 #include <utils/guc.h>
41 #include <utils/lsyscache.h>
42 #include <utils/rel.h>
43 #if PG_VERSION_NUM >= 100000
44 #include <utils/regproc.h>
45 #include <utils/varlena.h>
46 #endif
47
48 PG_MODULE_MAGIC;
49
50 static int      nDisabledIndexes = 0;
51 static Oid      *disabledIndexes = NULL;
52 static char *disableIndexesOutStr = "";
53
54 static int      nEnabledIndexes = 0;
55 static Oid      *enabledIndexes = NULL;
56 static char *enableIndexesOutStr = "";
57
58 get_relation_info_hook_type     prevHook = NULL;
59 static bool     fix_empty_table = false;
60
61
62 static const char *
63 indexesAssign(const char * newval, bool doit, GucSource source, bool isDisable)
64 {
65         char            *rawname;
66         List            *namelist;
67         ListCell        *l;
68         Oid                     *newOids = NULL;
69         int                     nOids = 0,
70                                 i = 0;
71
72         rawname = pstrdup(newval);
73
74         if (!SplitIdentifierString(rawname, ',', &namelist))
75                 goto cleanup;
76
77         if (doit)
78         {
79                 nOids = list_length(namelist);
80                 newOids = malloc(sizeof(Oid) * (nOids+1));
81                 if (!newOids)
82                         elog(ERROR,"could not allocate %d bytes",
83                                  (int)(sizeof(Oid) * (nOids+1)));
84         }
85
86         foreach(l, namelist)
87         {
88                 char    *curname = (char *) lfirst(l);
89 #if PG_VERSION_NUM >= 90200
90                 Oid             indexOid = RangeVarGetRelid(
91                                 makeRangeVarFromNameList(stringToQualifiedNameList(curname)),
92                                                                                         NoLock, true);
93 #else
94                 Oid             indexOid = RangeVarGetRelid(
95                                 makeRangeVarFromNameList(stringToQualifiedNameList(curname)),
96                                                                                         true);
97 #endif
98
99                 if (indexOid == InvalidOid)
100                 {
101 #if PG_VERSION_NUM >= 90100
102                         if (doit == false)
103 #endif
104                                 elog(WARNING,"'%s' does not exist", curname);
105                         continue;
106                 }
107                 else if ( get_rel_relkind(indexOid) != RELKIND_INDEX )
108                 {
109 #if PG_VERSION_NUM >= 90100
110                         if (doit == false)
111 #endif
112                                 elog(WARNING,"'%s' is not an index", curname);
113                         continue;
114                 }
115                 else if (doit)
116                 {
117                         newOids[i++] = indexOid;
118                 }
119         }
120
121         if (doit)
122         {
123                 if (isDisable)
124                 {
125                         nDisabledIndexes = nOids;
126                         disabledIndexes = newOids;
127                 }
128                 else
129                 {
130                         nEnabledIndexes = nOids;
131                         enabledIndexes = newOids;
132                 }
133         }
134
135         pfree(rawname);
136         list_free(namelist);
137
138         return newval;
139
140 cleanup:
141         if (newOids)
142                 free(newOids);
143         pfree(rawname);
144         list_free(namelist);
145         return NULL;
146 }
147
148 static const char *
149 assignDisabledIndexes(const char * newval, bool doit, GucSource source)
150 {
151         return indexesAssign(newval, doit, source, true);
152 }
153
154 static const char *
155 assignEnabledIndexes(const char * newval, bool doit, GucSource source)
156 {
157         return indexesAssign(newval, doit, source, false);
158 }
159
160 #if PG_VERSION_NUM >= 90100
161
162 static bool
163 checkDisabledIndexes(char **newval, void **extra, GucSource source)
164 {
165         char *val;
166
167         val = (char*)indexesAssign(*newval, false, source, true);
168
169         if (val)
170         {
171                 *newval = val;
172                 return true;
173         }
174
175         return false;
176 }
177
178 static bool
179 checkEnabledIndexes(char **newval, void **extra, GucSource source)
180 {
181         char *val;
182
183         val = (char*)indexesAssign(*newval, false, source, false);
184
185         if (val)
186         {
187                 *newval = val;
188                 return true;
189         }
190
191         return false;
192 }
193
194 static void
195 assignDisabledIndexesNew(const char *newval, void *extra)
196 {
197         assignDisabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
198 }
199
200 static void
201 assignEnabledIndexesNew(const char *newval, void *extra)
202 {
203         assignEnabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
204 }
205
206 #endif
207
208 static void
209 indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent,
210                         RelOptInfo *rel)
211 {
212         int i;
213
214         for(i=0;i<nDisabledIndexes;i++)
215         {
216                 ListCell   *l;
217
218                 foreach(l, rel->indexlist)
219                 {
220                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
221
222                         if (disabledIndexes[i] == info->indexoid)
223                         {
224                                 int j;
225
226                                 for(j=0; j<nEnabledIndexes; j++)
227                                         if (enabledIndexes[j] == info->indexoid)
228                                                 break;
229
230                                 if (j >= nEnabledIndexes)
231                                         rel->indexlist = list_delete_ptr(rel->indexlist, info);
232
233                                 break;
234                         }
235                 }
236         }
237 }
238
239 static void
240 execPlantuner(PlannerInfo *root, Oid relationObjectId, bool inhparent,
241                           RelOptInfo *rel)
242 {
243         Relation        relation;
244
245         relation = heap_open(relationObjectId, NoLock);
246         if (relation->rd_rel->relkind == RELKIND_RELATION)
247         {
248                 if (fix_empty_table && RelationGetNumberOfBlocks(relation) == 0)
249                 {
250                         /*
251                          * estimate_rel_size() could be too pessimistic for particular
252                          * workload
253                          */
254                         rel->pages = 0.0;
255                         rel->tuples = 0.0;
256                 }
257
258                 indexFilter(root, relationObjectId, inhparent, rel);
259         }
260         heap_close(relation, NoLock);
261
262         /*
263          * Call next hook if it exists
264          */
265         if (prevHook)
266                 prevHook(root, relationObjectId, inhparent, rel);
267 }
268
269 static const char*
270 IndexFilterShow(Oid* indexes, int nIndexes)
271 {
272         char    *val, *ptr;
273         int             i,
274                         len;
275
276         len = 1 /* \0 */ + nIndexes * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
277         ptr = val = palloc(len);
278
279         *ptr =(char)'\0';
280         for(i=0; i<nIndexes; i++)
281         {
282                 char    *relname = get_rel_name(indexes[i]);
283                 Oid             nspOid = get_rel_namespace(indexes[i]);
284                 char    *nspname = get_namespace_name(nspOid);
285
286                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
287                         continue;
288
289                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
290                                                                                                 (i==0) ? "" : ", ",
291                                                                                                 nspname,
292                                                                                                 relname);
293         }
294
295         return val;
296 }
297
298 static const char*
299 disabledIndexFilterShow(void)
300 {
301         return IndexFilterShow(disabledIndexes, nDisabledIndexes);
302 }
303
304 static const char*
305 enabledIndexFilterShow(void)
306 {
307         return IndexFilterShow(enabledIndexes, nEnabledIndexes);
308 }
309
310 void _PG_init(void);
311 void
312 _PG_init(void)
313 {
314         DefineCustomStringVariable(
315                 "plantuner.forbid_index",
316                 "List of forbidden indexes (deprecated)",
317                 "Listed indexes will not be used in queries (deprecated, use plantuner.disable_index)",
318                 &disableIndexesOutStr,
319                 "",
320                 PGC_USERSET,
321                 0,
322 #if PG_VERSION_NUM >= 90100
323                 checkDisabledIndexes,
324                 assignDisabledIndexesNew,
325 #else
326                 assignDisabledIndexes,
327 #endif
328                 disabledIndexFilterShow
329         );
330
331         DefineCustomStringVariable(
332                 "plantuner.disable_index",
333                 "List of disabled indexes",
334                 "Listed indexes will not be used in queries",
335                 &disableIndexesOutStr,
336                 "",
337                 PGC_USERSET,
338                 0,
339 #if PG_VERSION_NUM >= 90100
340                 checkDisabledIndexes,
341                 assignDisabledIndexesNew,
342 #else
343                 assignDisabledIndexes,
344 #endif
345                 disabledIndexFilterShow
346         );
347
348         DefineCustomStringVariable(
349                 "plantuner.enable_index",
350                 "List of enabled indexes (overload plantuner.disable_index)",
351                 "Listed indexes which could be used in queries even they are listed in plantuner.disable_index",
352                 &enableIndexesOutStr,
353                 "",
354                 PGC_USERSET,
355                 0,
356 #if PG_VERSION_NUM >= 90100
357                 checkEnabledIndexes,
358                 assignEnabledIndexesNew,
359 #else
360                 assignEnabledIndexes,
361 #endif
362                 enabledIndexFilterShow
363         );
364
365         DefineCustomBoolVariable(
366                 "plantuner.fix_empty_table",
367                 "Sets to zero estimations for empty tables",
368                 "Sets to zero estimations for empty or newly created tables",
369                 &fix_empty_table,
370 #if PG_VERSION_NUM >= 80400
371                 fix_empty_table,
372 #endif
373                 PGC_USERSET,
374 #if PG_VERSION_NUM >= 80400
375                 GUC_NOT_IN_SAMPLE,
376 #if PG_VERSION_NUM >= 90100
377                 NULL,
378 #endif
379 #endif
380                 NULL,
381                 NULL
382         );
383
384         if (get_relation_info_hook != execPlantuner )
385         {
386                 prevHook = get_relation_info_hook;
387                 get_relation_info_hook = execPlantuner;
388         }
389 }