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
  44 PG_MODULE_MAGIC;
  45
  46 static int      nIndexesOut = 0;
  47 static Oid      *indexesOut = NULL;
  48 get_relation_info_hook_type     prevHook = NULL;
  49 static bool     fix_empty_table = false;
  50
  51 static char *indexesOutStr = "";
  52
  53 static const char *
  54 indexesOutAssign(const char * newval, bool doit, GucSource source)
  55 {
  56         char       *rawname;
  57         List       *namelist;
  58         ListCell   *l;
  59         Oid                     *newOids = NULL;
  60         int                     nOids = 0,
  61                                 i = 0;
  62
  63         rawname = pstrdup(newval);
  64
  65         if (!SplitIdentifierString(rawname, ',', &namelist))
  66                 goto cleanup;
  67
  68         if (doit)
  69         {
  70                 nOids = list_length(namelist);
  71                 newOids = malloc(sizeof(Oid) * (nOids+1));
  72                 if (!newOids)
  73                         elog(ERROR,"could not allocate %d bytes", (int)(sizeof(Oid) * (nOids+1)));
  74         }
  75
  76         foreach(l, namelist)
  77         {
  78                 char            *curname = (char *) lfirst(l);
  79                 Oid                     indexOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), true);
  80
  81                 if (indexOid == InvalidOid)
  82                 {
  83 #if PG_VERSION_NUM >= 90100
  84                         if (doit == false)
  85 #endif
  86                                 elog(WARNING,"'%s' does not exist", curname);
  87                         continue;
  88                 }
  89                 else if ( get_rel_relkind(indexOid) != RELKIND_INDEX )
  90                 {
  91 #if PG_VERSION_NUM >= 90100
  92                         if (doit == false)
  93 #endif
  94                                 elog(WARNING,"'%s' is not an index", curname);
  95                         continue;
  96                 }
  97                 else if (doit)
  98                 {
  99                         newOids[i++] = indexOid;
 100                 }
 101         }
 102
 103         if (doit)
 104         {
 105                 nIndexesOut = nOids;
 106                 indexesOut = newOids;
 107         }
 108
 109         pfree(rawname);
 110         list_free(namelist);
 111
 112         return newval;
 113
 114 cleanup:
 115         if (newOids)
 116                 free(newOids);
 117         pfree(rawname);
 118         list_free(namelist);
 119         return NULL;
 120 }
 121
 122 #if PG_VERSION_NUM >= 90100
 123
 124 static bool
 125 indexesOutCheck(char **newval, void **extra, GucSource source)
 126 {
 127         char *val;
 128
 129         val = (char*)indexesOutAssign(*newval, false, source);
 130
 131         if (val)
 132         {
 133                 *newval = val;
 134                 return true;
 135         }
 136
 137         return false;
 138 }
 139 static void
 140 indexesOutAssignNew(const char *newval, void *extra)
 141 {
 142         indexesOutAssign(newval, true, PGC_S_USER /* doesn't matter */);
 143 }
 144
 145 #endif
 146
 147 static void
 148 indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent, RelOptInfo *rel) {
 149         int i;
 150
 151         for(i=0;i<nIndexesOut;i++)
 152         {
 153                 ListCell   *l;
 154
 155                 foreach(l, rel->indexlist)
 156                 {
 157                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 158
 159                         if (indexesOut[i] == info->indexoid)
 160                         {
 161                                 rel->indexlist = list_delete_ptr(rel->indexlist, info);
 162                                 break;
 163                         }
 164                 }
 165         }
 166
 167         if (fix_empty_table && rel)
 168         {
 169
 170
 171         }
 172
 173 }
 174
 175 static void
 176 execPlantuner(PlannerInfo *root, Oid relationObjectId, bool inhparent, RelOptInfo *rel) {
 177         Relation        relation;
 178
 179         relation = heap_open(relationObjectId, NoLock);
 180         if (relation->rd_rel->relkind == RELKIND_RELATION)
 181         {
 182                 if (fix_empty_table && RelationGetNumberOfBlocks(relation) == 0)
 183                 {
 184                         /*
 185                          * estimate_rel_size() could be too pessimistic for particular
 186                          * workload
 187                          */
 188                         rel->pages = 0.0;
 189                         rel->tuples = 0.0;
 190                 }
 191
 192                 indexFilter(root, relationObjectId, inhparent, rel);
 193         }
 194         heap_close(relation, NoLock);
 195
 196         /*
 197          * Call next hook if it exists
 198          */
 199         if (prevHook)
 200                 prevHook(root, relationObjectId, inhparent, rel);
 201 }
 202
 203 static const char*
 204 IndexFilterShow(void)
 205 {
 206         char    *val, *ptr;
 207         int     i,
 208                         len;
 209
 210         len = 1 /* \0 */ + nIndexesOut * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
 211         ptr = val = palloc(len);
 212
 213         *ptr ='\0';
 214         for(i=0; i<nIndexesOut; i++)
 215         {
 216                 char    *relname = get_rel_name(indexesOut[i]);
 217                 Oid     nspOid = get_rel_namespace(indexesOut[i]);
 218                 char    *nspname = get_namespace_name(nspOid);
 219
 220                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
 221                         continue;
 222
 223                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
 224                                                                                                 (i==0) ? "" : ", ",
 225                                                                                                 nspname,
 226                                                                                                 relname);
 227         }
 228
 229         return val;
 230 }
 231
 232 void _PG_init(void);
 233 void
 234 _PG_init(void)
 235 {
 236     DefineCustomStringVariable(
 237                 "plantuner.forbid_index",
 238                 "List of forbidden indexes",
 239                 "Listed indexes will not be used in queries",
 240                 &indexesOutStr,
 241                 "",
 242                 PGC_USERSET,
 243                 0,
 244 #if PG_VERSION_NUM >= 90100
 245                 indexesOutCheck,
 246                 indexesOutAssignNew,
 247 #else
 248                 indexesOutAssign,
 249 #endif
 250                 IndexFilterShow
 251         );
 252
 253     DefineCustomBoolVariable(
 254                 "plantuner.fix_empty_table",
 255                 "Sets to zero estimations for empty tables",
 256                 "Sets to zero estimations for empty or newly created tables",
 257                 &fix_empty_table,
 258 #if PG_VERSION_NUM >= 80400
 259                 fix_empty_table,
 260 #endif
 261                 PGC_USERSET,
 262 #if PG_VERSION_NUM >= 80400
 263                 GUC_NOT_IN_SAMPLE,
 264 #if PG_VERSION_NUM >= 90100
 265                 NULL,
 266 #endif
 267 #endif
 268                 NULL,
 269                 NULL
 270         );
 271
 272         if (get_relation_info_hook != execPlantuner )
 273         {
 274                 prevHook = get_relation_info_hook;
 275                 get_relation_info_hook = execPlantuner;
 276         }
 277 }