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 static int      nOnlyIndexes = 0;
  61 static Oid      *onlyIndexes = NULL;
  62 static char *onlyIndexesOutStr = "";
  63
  64 get_relation_info_hook_type     prevHook = NULL;
  65 static bool     fix_empty_table = false;
  66
  67 static bool     plantuner_enable_inited = false;
  68 static bool     plantuner_only_inited = false;
  69 static bool     plantuner_disable_inited = false;
  70
  71 typedef enum IndexListKind {
  72         EnabledKind,
  73         DisabledKind,
  74         OnlyKind
  75 } IndexListKind;
  76
  77 static const char *
  78 indexesAssign(const char * newval, bool doit, GucSource source,
  79                           IndexListKind kind)
  80 {
  81         char            *rawname;
  82         List            *namelist;
  83         ListCell        *l;
  84         Oid                     *newOids = NULL;
  85         int                     nOids = 0,
  86                                 i = 0;
  87
  88         rawname = pstrdup(newval);
  89
  90         if (!SplitIdentifierString(rawname, ',', &namelist))
  91                 goto cleanup;
  92
  93         /*
  94          * follow work could be done only in normal processing because of
  95          * accsess to system catalog
  96          */
  97         if (MyBackendId == InvalidBackendId || !IsUnderPostmaster ||
  98                 !IsTransactionState())
  99         {
 100                 /* reset init state */
 101                 switch(kind)
 102                 {
 103                         case EnabledKind:
 104                                 plantuner_enable_inited = false;
 105                                 break;
 106                         case DisabledKind:
 107                                 plantuner_disable_inited = false;
 108                                 break;
 109                         case OnlyKind:
 110                                 plantuner_only_inited = false;
 111                                 break;
 112                         default:
 113                                 elog(ERROR, "wrong kind");
 114                 }
 115
 116                 return newval;
 117         }
 118
 119         if (doit)
 120         {
 121                 nOids = list_length(namelist);
 122                 newOids = malloc(sizeof(Oid) * (nOids+1));
 123                 if (!newOids)
 124                         elog(ERROR,"could not allocate %d bytes",
 125                                  (int)(sizeof(Oid) * (nOids+1)));
 126         }
 127
 128         switch(kind)
 129         {
 130                 case EnabledKind:
 131                         plantuner_enable_inited = true;
 132                         break;
 133                 case DisabledKind:
 134                         plantuner_disable_inited = true;
 135                         break;
 136                 case OnlyKind:
 137                         plantuner_only_inited = true;
 138                         break;
 139                 default:
 140                         elog(ERROR, "wrong kind");
 141         }
 142
 143         foreach(l, namelist)
 144         {
 145                 char    *curname = (char *) lfirst(l);
 146 #if PG_VERSION_NUM >= 90200
 147                 Oid             indexOid = RangeVarGetRelid(
 148                                 makeRangeVarFromNameList(stringToQualifiedNameList(curname)),
 149                                                                                         NoLock, true);
 150 #else
 151                 Oid             indexOid = RangeVarGetRelid(
 152                                 makeRangeVarFromNameList(stringToQualifiedNameList(curname)),
 153                                                                                         true);
 154 #endif
 155
 156                 if (indexOid == InvalidOid)
 157                 {
 158 #if PG_VERSION_NUM >= 90100
 159                         if (doit == false)
 160 #endif
 161                                 elog(WARNING,"'%s' does not exist", curname);
 162                         continue;
 163                 }
 164                 else if ( get_rel_relkind(indexOid) != RELKIND_INDEX )
 165                 {
 166 #if PG_VERSION_NUM >= 90100
 167                         if (doit == false)
 168 #endif
 169                                 elog(WARNING,"'%s' is not an index", curname);
 170                         continue;
 171                 }
 172                 else if (doit)
 173                 {
 174                         newOids[i++] = indexOid;
 175                 }
 176         }
 177
 178         if (doit)
 179         {
 180                 switch(kind)
 181                 {
 182                         case EnabledKind:
 183                                 nEnabledIndexes = i;
 184                                 if (enabledIndexes)
 185                                         free(enabledIndexes);
 186                                 enabledIndexes = newOids;
 187                                 break;
 188                         case DisabledKind:
 189                                 nDisabledIndexes = i;
 190                                 if (disabledIndexes)
 191                                         free(disabledIndexes);
 192                                 disabledIndexes = newOids;
 193                                 break;
 194                         case OnlyKind:
 195                                 nOnlyIndexes = i;
 196                                 if (onlyIndexes)
 197                                         free(onlyIndexes);
 198                                 onlyIndexes = newOids;
 199                                 break;
 200                         default:
 201                                 elog(ERROR, "wrong kind");
 202                 }
 203         }
 204
 205         pfree(rawname);
 206         list_free(namelist);
 207
 208         return newval;
 209
 210 cleanup:
 211         if (newOids)
 212                 free(newOids);
 213         pfree(rawname);
 214         list_free(namelist);
 215         return NULL;
 216 }
 217
 218 static const char *
 219 assignDisabledIndexes(const char * newval, bool doit, GucSource source)
 220 {
 221         return indexesAssign(newval, doit, source, DisabledKind);
 222 }
 223
 224 static const char *
 225 assignEnabledIndexes(const char * newval, bool doit, GucSource source)
 226 {
 227         return indexesAssign(newval, doit, source, EnabledKind);
 228 }
 229
 230 static const char *
 231 assignOnlyIndexes(const char * newval, bool doit, GucSource source)
 232 {
 233         return indexesAssign(newval, doit, source, OnlyKind);
 234 }
 235
 236 static void
 237 lateInit()
 238 {
 239         if (!plantuner_only_inited)
 240                 indexesAssign(onlyIndexesOutStr, true, PGC_S_USER, OnlyKind);
 241         if (!plantuner_enable_inited)
 242                 indexesAssign(enableIndexesOutStr, true, PGC_S_USER, EnabledKind);
 243         if (!plantuner_disable_inited)
 244                 indexesAssign(disableIndexesOutStr, true, PGC_S_USER, DisabledKind);
 245 }
 246
 247 #if PG_VERSION_NUM >= 90100
 248
 249 static bool
 250 checkOnlyIndexes(char **newval, void **extra, GucSource source)
 251 {
 252         char *val;
 253
 254         val = (char*)indexesAssign(*newval, false, source, OnlyKind);
 255
 256         if (val)
 257         {
 258                 *newval = val;
 259                 return true;
 260         }
 261
 262         return false;
 263 }
 264
 265 static bool
 266 checkDisabledIndexes(char **newval, void **extra, GucSource source)
 267 {
 268         char *val;
 269
 270         val = (char*)indexesAssign(*newval, false, source, DisabledKind);
 271
 272         if (val)
 273         {
 274                 *newval = val;
 275                 return true;
 276         }
 277
 278         return false;
 279 }
 280
 281 static bool
 282 checkEnabledIndexes(char **newval, void **extra, GucSource source)
 283 {
 284         char *val;
 285
 286         val = (char*)indexesAssign(*newval, false, source, EnabledKind);
 287
 288         if (val)
 289         {
 290                 *newval = val;
 291                 return true;
 292         }
 293
 294         return false;
 295 }
 296
 297 static void
 298 assignDisabledIndexesNew(const char *newval, void *extra)
 299 {
 300         assignDisabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 301 }
 302
 303 static void
 304 assignEnabledIndexesNew(const char *newval, void *extra)
 305 {
 306         assignEnabledIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 307 }
 308
 309 static void
 310 assignOnlyIndexesNew(const char *newval, void *extra)
 311 {
 312         assignOnlyIndexes(newval, true, PGC_S_USER /* doesn't matter */);
 313 }
 314
 315 #endif
 316
 317 static void
 318 indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 319                         RelOptInfo *rel)
 320 {
 321         int i;
 322
 323         lateInit();
 324
 325         if (nOnlyIndexes > 0)
 326         {
 327                 ListCell        *l;
 328
 329                 foreach(l, rel->indexlist)
 330                 {
 331                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 332                         bool                    remove = true;
 333
 334                         for(i=0; remove && i<nOnlyIndexes; i++)
 335                                 if (onlyIndexes[i] == info->indexoid)
 336                                         remove = false;
 337
 338                         if (remove)
 339                                 rel->indexlist = list_delete_ptr(rel->indexlist, info);
 340                 }
 341
 342                 return;
 343         }
 344
 345         for(i=0; i<nDisabledIndexes; i++)
 346         {
 347                 ListCell   *l;
 348
 349                 foreach(l, rel->indexlist)
 350                 {
 351                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 352
 353                         if (disabledIndexes[i] == info->indexoid)
 354                         {
 355                                 int j;
 356
 357                                 for(j=0; j<nEnabledIndexes; j++)
 358                                         if (enabledIndexes[j] == info->indexoid)
 359                                                 break;
 360
 361                                 if (j >= nEnabledIndexes)
 362                                         rel->indexlist = list_delete_ptr(rel->indexlist, info);
 363
 364                                 break;
 365                         }
 366                 }
 367         }
 368 }
 369
 370 static void
 371 execPlantuner(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 372                           RelOptInfo *rel)
 373 {
 374         Relation        relation;
 375
 376         relation = heap_open(relationObjectId, NoLock);
 377         if (relation->rd_rel->relkind == RELKIND_RELATION)
 378         {
 379                 if (fix_empty_table && RelationGetNumberOfBlocks(relation) == 0)
 380                 {
 381                         /*
 382                          * estimate_rel_size() could be too pessimistic for particular
 383                          * workload
 384                          */
 385                         rel->pages = 1.0;
 386                         rel->tuples = 0.0;
 387                 }
 388
 389                 indexFilter(root, relationObjectId, inhparent, rel);
 390         }
 391         heap_close(relation, NoLock);
 392
 393         /*
 394          * Call next hook if it exists
 395          */
 396         if (prevHook)
 397                 prevHook(root, relationObjectId, inhparent, rel);
 398 }
 399
 400 static const char*
 401 IndexFilterShow(Oid* indexes, int nIndexes)
 402 {
 403         char    *val, *ptr;
 404         int             i,
 405                         len;
 406
 407         lateInit();
 408
 409         len = 1 /* \0 */ + nIndexes * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
 410         ptr = val = palloc(len);
 411
 412         *ptr =(char)'\0';
 413         for(i=0; i<nIndexes; i++)
 414         {
 415                 char    *relname = get_rel_name(indexes[i]);
 416                 Oid             nspOid = get_rel_namespace(indexes[i]);
 417                 char    *nspname = get_namespace_name(nspOid);
 418
 419                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
 420                         continue;
 421
 422                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
 423                                                                                                 (i==0) ? "" : ", ",
 424                                                                                                 nspname,
 425                                                                                                 relname);
 426         }
 427
 428         return val;
 429 }
 430
 431 static const char*
 432 disabledIndexFilterShow(void)
 433 {
 434         return IndexFilterShow(disabledIndexes, nDisabledIndexes);
 435 }
 436
 437 static const char*
 438 enabledIndexFilterShow(void)
 439 {
 440         return IndexFilterShow(enabledIndexes, nEnabledIndexes);
 441 }
 442
 443 static const char*
 444 onlyIndexFilterShow(void)
 445 {
 446         return IndexFilterShow(onlyIndexes, nOnlyIndexes);
 447 }
 448
 449 void _PG_init(void);
 450 void
 451 _PG_init(void)
 452 {
 453         DefineCustomStringVariable(
 454                 "plantuner.forbid_index",
 455                 "List of forbidden indexes (deprecated)",
 456                 "Listed indexes will not be used in queries (deprecated, use plantuner.disable_index)",
 457                 &disableIndexesOutStr,
 458                 "",
 459                 PGC_USERSET,
 460                 0,
 461 #if PG_VERSION_NUM >= 90100
 462                 checkDisabledIndexes,
 463                 assignDisabledIndexesNew,
 464 #else
 465                 assignDisabledIndexes,
 466 #endif
 467                 disabledIndexFilterShow
 468         );
 469
 470         DefineCustomStringVariable(
 471                 "plantuner.disable_index",
 472                 "List of disabled indexes",
 473                 "Listed indexes will not be used in queries",
 474                 &disableIndexesOutStr,
 475                 "",
 476                 PGC_USERSET,
 477                 0,
 478 #if PG_VERSION_NUM >= 90100
 479                 checkDisabledIndexes,
 480                 assignDisabledIndexesNew,
 481 #else
 482                 assignDisabledIndexes,
 483 #endif
 484                 disabledIndexFilterShow
 485         );
 486
 487         DefineCustomStringVariable(
 488                 "plantuner.enable_index",
 489                 "List of enabled indexes (overload plantuner.disable_index)",
 490                 "Listed indexes which could be used in queries even they are listed in plantuner.disable_index",
 491                 &enableIndexesOutStr,
 492                 "",
 493                 PGC_USERSET,
 494                 0,
 495 #if PG_VERSION_NUM >= 90100
 496                 checkEnabledIndexes,
 497                 assignEnabledIndexesNew,
 498 #else
 499                 assignEnabledIndexes,
 500 #endif
 501                 enabledIndexFilterShow
 502         );
 503
 504         DefineCustomStringVariable(
 505                 "plantuner.only_index",
 506                 "List of explicitly enabled indexes (overload plantuner.disable_index and plantuner.enable_index)",
 507                 "Only indexes in this list are allowed",
 508                 &onlyIndexesOutStr,
 509                 "",
 510                 PGC_USERSET,
 511                 0,
 512 #if PG_VERSION_NUM >= 90100
 513                 checkOnlyIndexes,
 514                 assignOnlyIndexesNew,
 515 #else
 516                 assignOnlyIndexes,
 517 #endif
 518                 onlyIndexFilterShow
 519         );
 520
 521         DefineCustomBoolVariable(
 522                 "plantuner.fix_empty_table",
 523                 "Sets to zero estimations for empty tables",
 524                 "Sets to zero estimations for empty or newly created tables",
 525                 &fix_empty_table,
 526 #if PG_VERSION_NUM >= 80400
 527                 fix_empty_table,
 528 #endif
 529                 PGC_USERSET,
 530 #if PG_VERSION_NUM >= 80400
 531                 GUC_NOT_IN_SAMPLE,
 532 #if PG_VERSION_NUM >= 90100
 533                 NULL,
 534 #endif
 535 #endif
 536                 NULL,
 537                 NULL
 538         );
 539
 540         if (get_relation_info_hook != execPlantuner )
 541         {
 542                 prevHook = get_relation_info_hook;
 543                 get_relation_info_hook = execPlantuner;
 544         }
 545 }