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 = i;
 126                         if (disabledIndexes)
 127                                 free(disabledIndexes);
 128                         disabledIndexes = newOids;
 129                 }
 130                 else
 131                 {
 132                         nEnabledIndexes = i;
 133                         if (enabledIndexes)
 134                                 free(enabledIndexes);
 135                         enabledIndexes = newOids;
 136                 }
 137         }
 138
 139         pfree(rawname);
 140         list_free(namelist);
 141
 142         return newval;
 143
 144 cleanup:
 145         if (newOids)
 146                 free(newOids);
 147         pfree(rawname);
 148         list_free(namelist);
 149         return NULL;
 150 }
 151
 152 static const char *
 153 assignDisabledIndexes(const char * newval, bool doit, GucSource source)
 154 {
 155         return indexesAssign(newval, doit, source, true);
 156 }
 157
 158 static const char *
 159 assignEnabledIndexes(const char * newval, bool doit, GucSource source)
 160 {
 161         return indexesAssign(newval, doit, source, false);
 162 }
 163
 164 #if PG_VERSION_NUM >= 90100
 165
 166 static bool
 167 checkDisabledIndexes(char **newval, void **extra, GucSource source)
 168 {
 169         char *val;
 170
 171         val = (char*)indexesAssign(*newval, false, source, true);
 172
 173         if (val)
 174         {
 175                 *newval = val;
 176                 return true;
 177         }
 178
 179         return false;
 180 }
 181
 182 static bool
 183 checkEnabledIndexes(char **newval, void **extra, GucSource source)
 184 {
 185         char *val;
 186
 187         val = (char*)indexesAssign(*newval, false, source, false);
 188
 189         if (val)
 190         {
 191                 *newval = val;
 192                 return true;
 193         }
 194
 195         return false;
 196 }
 197
 198 static void
 199 assignDisabledIndexesNew(const char *newval, void *extra)
 200 {
 201         assignDisabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 202 }
 203
 204 static void
 205 assignEnabledIndexesNew(const char *newval, void *extra)
 206 {
 207         assignEnabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 208 }
 209
 210 #endif
 211
 212 static void
 213 indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 214                         RelOptInfo *rel)
 215 {
 216         int i;
 217
 218         for(i=0;i<nDisabledIndexes;i++)
 219         {
 220                 ListCell   *l;
 221
 222                 foreach(l, rel->indexlist)
 223                 {
 224                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 225
 226                         if (disabledIndexes[i] == info->indexoid)
 227                         {
 228                                 int j;
 229
 230                                 for(j=0; j<nEnabledIndexes; j++)
 231                                         if (enabledIndexes[j] == info->indexoid)
 232                                                 break;
 233
 234                                 if (j >= nEnabledIndexes)
 235                                         rel->indexlist = list_delete_ptr(rel->indexlist, info);
 236
 237                                 break;
 238                         }
 239                 }
 240         }
 241 }
 242
 243 static void
 244 execPlantuner(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 245                           RelOptInfo *rel)
 246 {
 247         Relation        relation;
 248
 249         relation = heap_open(relationObjectId, NoLock);
 250         if (relation->rd_rel->relkind == RELKIND_RELATION)
 251         {
 252                 if (fix_empty_table && RelationGetNumberOfBlocks(relation) == 0)
 253                 {
 254                         /*
 255                          * estimate_rel_size() could be too pessimistic for particular
 256                          * workload
 257                          */
 258                         rel->pages = 0.0;
 259                         rel->tuples = 0.0;
 260                 }
 261
 262                 indexFilter(root, relationObjectId, inhparent, rel);
 263         }
 264         heap_close(relation, NoLock);
 265
 266         /*
 267          * Call next hook if it exists
 268          */
 269         if (prevHook)
 270                 prevHook(root, relationObjectId, inhparent, rel);
 271 }
 272
 273 static const char*
 274 IndexFilterShow(Oid* indexes, int nIndexes)
 275 {
 276         char    *val, *ptr;
 277         int             i,
 278                         len;
 279
 280         len = 1 /* \0 */ + nIndexes * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
 281         ptr = val = palloc(len);
 282
 283         *ptr =(char)'\0';
 284         for(i=0; i<nIndexes; i++)
 285         {
 286                 char    *relname = get_rel_name(indexes[i]);
 287                 Oid             nspOid = get_rel_namespace(indexes[i]);
 288                 char    *nspname = get_namespace_name(nspOid);
 289
 290                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
 291                         continue;
 292
 293                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
 294                                                                                                 (i==0) ? "" : ", ",
 295                                                                                                 nspname,
 296                                                                                                 relname);
 297         }
 298
 299         return val;
 300 }
 301
 302 static const char*
 303 disabledIndexFilterShow(void)
 304 {
 305         return IndexFilterShow(disabledIndexes, nDisabledIndexes);
 306 }
 307
 308 static const char*
 309 enabledIndexFilterShow(void)
 310 {
 311         return IndexFilterShow(enabledIndexes, nEnabledIndexes);
 312 }
 313
 314 void _PG_init(void);
 315 void
 316 _PG_init(void)
 317 {
 318         DefineCustomStringVariable(
 319                 "plantuner.forbid_index",
 320                 "List of forbidden indexes (deprecated)",
 321                 "Listed indexes will not be used in queries (deprecated, use plantuner.disable_index)",
 322                 &disableIndexesOutStr,
 323                 "",
 324                 PGC_USERSET,
 325                 0,
 326 #if PG_VERSION_NUM >= 90100
 327                 checkDisabledIndexes,
 328                 assignDisabledIndexesNew,
 329 #else
 330                 assignDisabledIndexes,
 331 #endif
 332                 disabledIndexFilterShow
 333         );
 334
 335         DefineCustomStringVariable(
 336                 "plantuner.disable_index",
 337                 "List of disabled indexes",
 338                 "Listed indexes will not be used in queries",
 339                 &disableIndexesOutStr,
 340                 "",
 341                 PGC_USERSET,
 342                 0,
 343 #if PG_VERSION_NUM >= 90100
 344                 checkDisabledIndexes,
 345                 assignDisabledIndexesNew,
 346 #else
 347                 assignDisabledIndexes,
 348 #endif
 349                 disabledIndexFilterShow
 350         );
 351
 352         DefineCustomStringVariable(
 353                 "plantuner.enable_index",
 354                 "List of enabled indexes (overload plantuner.disable_index)",
 355                 "Listed indexes which could be used in queries even they are listed in plantuner.disable_index",
 356                 &enableIndexesOutStr,
 357                 "",
 358                 PGC_USERSET,
 359                 0,
 360 #if PG_VERSION_NUM >= 90100
 361                 checkEnabledIndexes,
 362                 assignEnabledIndexesNew,
 363 #else
 364                 assignEnabledIndexes,
 365 #endif
 366                 enabledIndexFilterShow
 367         );
 368
 369         DefineCustomBoolVariable(
 370                 "plantuner.fix_empty_table",
 371                 "Sets to zero estimations for empty tables",
 372                 "Sets to zero estimations for empty or newly created tables",
 373                 &fix_empty_table,
 374 #if PG_VERSION_NUM >= 80400
 375                 fix_empty_table,
 376 #endif
 377                 PGC_USERSET,
 378 #if PG_VERSION_NUM >= 80400
 379                 GUC_NOT_IN_SAMPLE,
 380 #if PG_VERSION_NUM >= 90100
 381                 NULL,
 382 #endif
 383 #endif
 384                 NULL,
 385                 NULL
 386         );
 387
 388         if (get_relation_info_hook != execPlantuner )
 389         {
 390                 prevHook = get_relation_info_hook;
 391                 get_relation_info_hook = execPlantuner;
 392         }
 393 }