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 }