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 <miscadmin.h>
  34 #include <access/heapam.h>
  35 #include <access/xact.h>
  36 #include <catalog/namespace.h>
  37 #include <catalog/pg_class.h>
  38 #include <nodes/pg_list.h>
  39 #include <optimizer/plancat.h>
  40 #include <storage/bufmgr.h>
  41 #include <utils/builtins.h>
  42 #include <utils/guc.h>
  43 #include <utils/lsyscache.h>
  44 #include <utils/rel.h>
  45 #if PG_VERSION_NUM >= 100000
  46 #include <utils/regproc.h>
  47 #include <utils/varlena.h>
  48 #endif
  49
  50 PG_MODULE_MAGIC;
  51
  52 static int      nDisabledIndexes = 0;
  53 static Oid      *disabledIndexes = NULL;
  54 static char *disableIndexesOutStr = "";
  55
  56 static int      nEnabledIndexes = 0;
  57 static Oid      *enabledIndexes = NULL;
  58 static char *enableIndexesOutStr = "";
  59
  60 get_relation_info_hook_type     prevHook = NULL;
  61 static bool     fix_empty_table = false;
  62
  63 static bool     plantuner_enable_inited = false;
  64 static bool     plantuner_disable_inited = false;
  65
  66 static const char *
  67 indexesAssign(const char * newval, bool doit, GucSource source, bool isDisable)
  68 {
  69         char            *rawname;
  70         List            *namelist;
  71         ListCell        *l;
  72         Oid                     *newOids = NULL;
  73         int                     nOids = 0,
  74                                 i = 0;
  75
  76         rawname = pstrdup(newval);
  77
  78         if (!SplitIdentifierString(rawname, ',', &namelist))
  79                 goto cleanup;
  80
  81         /*
  82          * follow work could be done only in normal processing because of
  83          * accsess to system catalog
  84          */
  85         if (MyBackendId == InvalidBackendId || !IsUnderPostmaster ||
  86                 MyAuxProcType != NotAnAuxProcess ||     !IsTransactionState())
  87         {
  88                 /* reset init state */
  89                 if (isDisable)
  90                         plantuner_disable_inited = false;
  91                 else
  92                         plantuner_enable_inited = false;
  93
  94                 return newval;
  95         }
  96
  97         if (doit)
  98         {
  99                 nOids = list_length(namelist);
 100                 newOids = malloc(sizeof(Oid) * (nOids+1));
 101                 if (!newOids)
 102                         elog(ERROR,"could not allocate %d bytes",
 103                                  (int)(sizeof(Oid) * (nOids+1)));
 104         }
 105
 106         if (isDisable)
 107                 plantuner_disable_inited = true;
 108         else
 109                 plantuner_enable_inited = true;
 110
 111         foreach(l, namelist)
 112         {
 113                 char    *curname = (char *) lfirst(l);
 114 #if PG_VERSION_NUM >= 90200
 115                 Oid             indexOid = RangeVarGetRelid(
 116                                 makeRangeVarFromNameList(stringToQualifiedNameList(curname)),
 117                                                                                         NoLock, true);
 118 #else
 119                 Oid             indexOid = RangeVarGetRelid(
 120                                 makeRangeVarFromNameList(stringToQualifiedNameList(curname)),
 121                                                                                         true);
 122 #endif
 123
 124                 if (indexOid == InvalidOid)
 125                 {
 126 #if PG_VERSION_NUM >= 90100
 127                         if (doit == false)
 128 #endif
 129                                 elog(WARNING,"'%s' does not exist", curname);
 130                         continue;
 131                 }
 132                 else if ( get_rel_relkind(indexOid) != RELKIND_INDEX )
 133                 {
 134 #if PG_VERSION_NUM >= 90100
 135                         if (doit == false)
 136 #endif
 137                                 elog(WARNING,"'%s' is not an index", curname);
 138                         continue;
 139                 }
 140                 else if (doit)
 141                 {
 142                         newOids[i++] = indexOid;
 143                 }
 144         }
 145
 146         if (doit)
 147         {
 148                 if (isDisable)
 149                 {
 150                         nDisabledIndexes = i;
 151                         if (disabledIndexes)
 152                                 free(disabledIndexes);
 153                         disabledIndexes = newOids;
 154                 }
 155                 else
 156                 {
 157                         nEnabledIndexes = i;
 158                         if (enabledIndexes)
 159                                 free(enabledIndexes);
 160                         enabledIndexes = newOids;
 161                 }
 162         }
 163
 164         pfree(rawname);
 165         list_free(namelist);
 166
 167         return newval;
 168
 169 cleanup:
 170         if (newOids)
 171                 free(newOids);
 172         pfree(rawname);
 173         list_free(namelist);
 174         return NULL;
 175 }
 176
 177 static const char *
 178 assignDisabledIndexes(const char * newval, bool doit, GucSource source)
 179 {
 180         return indexesAssign(newval, doit, source, true);
 181 }
 182
 183 static const char *
 184 assignEnabledIndexes(const char * newval, bool doit, GucSource source)
 185 {
 186         return indexesAssign(newval, doit, source, false);
 187 }
 188
 189 static void
 190 lateInit()
 191 {
 192         if (!plantuner_enable_inited)
 193                 indexesAssign(enableIndexesOutStr, true, PGC_S_USER, false);
 194         if (!plantuner_disable_inited)
 195                 indexesAssign(disableIndexesOutStr, true, PGC_S_USER, true);
 196 }
 197
 198 #if PG_VERSION_NUM >= 90100
 199
 200 static bool
 201 checkDisabledIndexes(char **newval, void **extra, GucSource source)
 202 {
 203         char *val;
 204
 205         val = (char*)indexesAssign(*newval, false, source, true);
 206
 207         if (val)
 208         {
 209                 *newval = val;
 210                 return true;
 211         }
 212
 213         return false;
 214 }
 215
 216 static bool
 217 checkEnabledIndexes(char **newval, void **extra, GucSource source)
 218 {
 219         char *val;
 220
 221         val = (char*)indexesAssign(*newval, false, source, false);
 222
 223         if (val)
 224         {
 225                 *newval = val;
 226                 return true;
 227         }
 228
 229         return false;
 230 }
 231
 232 static void
 233 assignDisabledIndexesNew(const char *newval, void *extra)
 234 {
 235         assignDisabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 236 }
 237
 238 static void
 239 assignEnabledIndexesNew(const char *newval, void *extra)
 240 {
 241         assignEnabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 242 }
 243
 244 #endif
 245
 246 static void
 247 indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 248                         RelOptInfo *rel)
 249 {
 250         int i;
 251
 252         lateInit();
 253
 254         for(i=0;i<nDisabledIndexes;i++)
 255         {
 256                 ListCell   *l;
 257
 258                 foreach(l, rel->indexlist)
 259                 {
 260                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 261
 262                         if (disabledIndexes[i] == info->indexoid)
 263                         {
 264                                 int j;
 265
 266                                 for(j=0; j<nEnabledIndexes; j++)
 267                                         if (enabledIndexes[j] == info->indexoid)
 268                                                 break;
 269
 270                                 if (j >= nEnabledIndexes)
 271                                         rel->indexlist = list_delete_ptr(rel->indexlist, info);
 272
 273                                 break;
 274                         }
 275                 }
 276         }
 277 }
 278
 279 static void
 280 execPlantuner(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 281                           RelOptInfo *rel)
 282 {
 283         Relation        relation;
 284
 285         relation = heap_open(relationObjectId, NoLock);
 286         if (relation->rd_rel->relkind == RELKIND_RELATION)
 287         {
 288                 if (fix_empty_table && RelationGetNumberOfBlocks(relation) == 0)
 289                 {
 290                         /*
 291                          * estimate_rel_size() could be too pessimistic for particular
 292                          * workload
 293                          */
 294                         rel->pages = 0.0;
 295                         rel->tuples = 0.0;
 296                 }
 297
 298                 indexFilter(root, relationObjectId, inhparent, rel);
 299         }
 300         heap_close(relation, NoLock);
 301
 302         /*
 303          * Call next hook if it exists
 304          */
 305         if (prevHook)
 306                 prevHook(root, relationObjectId, inhparent, rel);
 307 }
 308
 309 static const char*
 310 IndexFilterShow(Oid* indexes, int nIndexes)
 311 {
 312         char    *val, *ptr;
 313         int             i,
 314                         len;
 315
 316         lateInit();
 317
 318         len = 1 /* \0 */ + nIndexes * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
 319         ptr = val = palloc(len);
 320
 321         *ptr =(char)'\0';
 322         for(i=0; i<nIndexes; i++)
 323         {
 324                 char    *relname = get_rel_name(indexes[i]);
 325                 Oid             nspOid = get_rel_namespace(indexes[i]);
 326                 char    *nspname = get_namespace_name(nspOid);
 327
 328                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
 329                         continue;
 330
 331                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
 332                                                                                                 (i==0) ? "" : ", ",
 333                                                                                                 nspname,
 334                                                                                                 relname);
 335         }
 336
 337         return val;
 338 }
 339
 340 static const char*
 341 disabledIndexFilterShow(void)
 342 {
 343         return IndexFilterShow(disabledIndexes, nDisabledIndexes);
 344 }
 345
 346 static const char*
 347 enabledIndexFilterShow(void)
 348 {
 349         return IndexFilterShow(enabledIndexes, nEnabledIndexes);
 350 }
 351
 352 void _PG_init(void);
 353 void
 354 _PG_init(void)
 355 {
 356         DefineCustomStringVariable(
 357                 "plantuner.forbid_index",
 358                 "List of forbidden indexes (deprecated)",
 359                 "Listed indexes will not be used in queries (deprecated, use plantuner.disable_index)",
 360                 &disableIndexesOutStr,
 361                 "",
 362                 PGC_USERSET,
 363                 0,
 364 #if PG_VERSION_NUM >= 90100
 365                 checkDisabledIndexes,
 366                 assignDisabledIndexesNew,
 367 #else
 368                 assignDisabledIndexes,
 369 #endif
 370                 disabledIndexFilterShow
 371         );
 372
 373         DefineCustomStringVariable(
 374                 "plantuner.disable_index",
 375                 "List of disabled indexes",
 376                 "Listed indexes will not be used in queries",
 377                 &disableIndexesOutStr,
 378                 "",
 379                 PGC_USERSET,
 380                 0,
 381 #if PG_VERSION_NUM >= 90100
 382                 checkDisabledIndexes,
 383                 assignDisabledIndexesNew,
 384 #else
 385                 assignDisabledIndexes,
 386 #endif
 387                 disabledIndexFilterShow
 388         );
 389
 390         DefineCustomStringVariable(
 391                 "plantuner.enable_index",
 392                 "List of enabled indexes (overload plantuner.disable_index)",
 393                 "Listed indexes which could be used in queries even they are listed in plantuner.disable_index",
 394                 &enableIndexesOutStr,
 395                 "",
 396                 PGC_USERSET,
 397                 0,
 398 #if PG_VERSION_NUM >= 90100
 399                 checkEnabledIndexes,
 400                 assignEnabledIndexesNew,
 401 #else
 402                 assignEnabledIndexes,
 403 #endif
 404                 enabledIndexFilterShow
 405         );
 406
 407         DefineCustomBoolVariable(
 408                 "plantuner.fix_empty_table",
 409                 "Sets to zero estimations for empty tables",
 410                 "Sets to zero estimations for empty or newly created tables",
 411                 &fix_empty_table,
 412 #if PG_VERSION_NUM >= 80400
 413                 fix_empty_table,
 414 #endif
 415                 PGC_USERSET,
 416 #if PG_VERSION_NUM >= 80400
 417                 GUC_NOT_IN_SAMPLE,
 418 #if PG_VERSION_NUM >= 90100
 419                 NULL,
 420 #endif
 421 #endif
 422                 NULL,
 423                 NULL
 424         );
 425
 426         if (get_relation_info_hook != execPlantuner )
 427         {
 428                 prevHook = get_relation_info_hook;
 429                 get_relation_info_hook = execPlantuner;
 430         }
 431 }