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 restart1:
 330                 foreach(l, rel->indexlist)
 331                 {
 332                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 333                         bool                    remove = true;
 334
 335                         for(i=0; remove && i<nOnlyIndexes; i++)
 336                                 if (onlyIndexes[i] == info->indexoid)
 337                                         remove = false;
 338
 339                         if (remove)
 340                         {
 341                                 rel->indexlist = list_delete_ptr(rel->indexlist, info);
 342                                 goto restart1;
 343                         }
 344                 }
 345
 346                 return;
 347         }
 348
 349         for(i=0; i<nDisabledIndexes; i++)
 350         {
 351                 ListCell   *l;
 352
 353                 foreach(l, rel->indexlist)
 354                 {
 355                         IndexOptInfo    *info = (IndexOptInfo*)lfirst(l);
 356
 357                         if (disabledIndexes[i] == info->indexoid)
 358                         {
 359                                 int j;
 360
 361                                 for(j=0; j<nEnabledIndexes; j++)
 362                                         if (enabledIndexes[j] == info->indexoid)
 363                                                 break;
 364
 365                                 if (j >= nEnabledIndexes)
 366                                         rel->indexlist = list_delete_ptr(rel->indexlist, info);
 367
 368                                 break;
 369                         }
 370                 }
 371         }
 372 }
 373
 374 static void
 375 execPlantuner(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 376                           RelOptInfo *rel)
 377 {
 378         Relation        relation;
 379
 380         relation = heap_open(relationObjectId, NoLock);
 381         if (relation->rd_rel->relkind == RELKIND_RELATION)
 382         {
 383                 if (fix_empty_table && RelationGetNumberOfBlocks(relation) == 0)
 384                 {
 385                         /*
 386                          * estimate_rel_size() could be too pessimistic for particular
 387                          * workload
 388                          */
 389                         rel->pages = 1.0;
 390                         rel->tuples = 0.0;
 391                 }
 392
 393                 indexFilter(root, relationObjectId, inhparent, rel);
 394         }
 395         heap_close(relation, NoLock);
 396
 397         /*
 398          * Call next hook if it exists
 399          */
 400         if (prevHook)
 401                 prevHook(root, relationObjectId, inhparent, rel);
 402 }
 403
 404 static const char*
 405 IndexFilterShow(Oid* indexes, int nIndexes)
 406 {
 407         char    *val, *ptr;
 408         int             i,
 409                         len;
 410
 411         lateInit();
 412
 413         len = 1 /* \0 */ + nIndexes * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
 414         ptr = val = palloc(len);
 415
 416         *ptr =(char)'\0';
 417         for(i=0; i<nIndexes; i++)
 418         {
 419                 char    *relname = get_rel_name(indexes[i]);
 420                 Oid             nspOid = get_rel_namespace(indexes[i]);
 421                 char    *nspname = get_namespace_name(nspOid);
 422
 423                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
 424                         continue;
 425
 426                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
 427                                                                                                 (i==0) ? "" : ", ",
 428                                                                                                 nspname,
 429                                                                                                 relname);
 430         }
 431
 432         return val;
 433 }
 434
 435 static const char*
 436 disabledIndexFilterShow(void)
 437 {
 438         return IndexFilterShow(disabledIndexes, nDisabledIndexes);
 439 }
 440
 441 static const char*
 442 enabledIndexFilterShow(void)
 443 {
 444         return IndexFilterShow(enabledIndexes, nEnabledIndexes);
 445 }
 446
 447 static const char*
 448 onlyIndexFilterShow(void)
 449 {
 450         return IndexFilterShow(onlyIndexes, nOnlyIndexes);
 451 }
 452
 453 void _PG_init(void);
 454 void
 455 _PG_init(void)
 456 {
 457         DefineCustomStringVariable(
 458                 "plantuner.forbid_index",
 459                 "List of forbidden indexes (deprecated)",
 460                 "Listed indexes will not be used in queries (deprecated, use plantuner.disable_index)",
 461                 &disableIndexesOutStr,
 462                 "",
 463                 PGC_USERSET,
 464                 0,
 465 #if PG_VERSION_NUM >= 90100
 466                 checkDisabledIndexes,
 467                 assignDisabledIndexesNew,
 468 #else
 469                 assignDisabledIndexes,
 470 #endif
 471                 disabledIndexFilterShow
 472         );
 473
 474         DefineCustomStringVariable(
 475                 "plantuner.disable_index",
 476                 "List of disabled indexes",
 477                 "Listed indexes will not be used in queries",
 478                 &disableIndexesOutStr,
 479                 "",
 480                 PGC_USERSET,
 481                 0,
 482 #if PG_VERSION_NUM >= 90100
 483                 checkDisabledIndexes,
 484                 assignDisabledIndexesNew,
 485 #else
 486                 assignDisabledIndexes,
 487 #endif
 488                 disabledIndexFilterShow
 489         );
 490
 491         DefineCustomStringVariable(
 492                 "plantuner.enable_index",
 493                 "List of enabled indexes (overload plantuner.disable_index)",
 494                 "Listed indexes which could be used in queries even they are listed in plantuner.disable_index",
 495                 &enableIndexesOutStr,
 496                 "",
 497                 PGC_USERSET,
 498                 0,
 499 #if PG_VERSION_NUM >= 90100
 500                 checkEnabledIndexes,
 501                 assignEnabledIndexesNew,
 502 #else
 503                 assignEnabledIndexes,
 504 #endif
 505                 enabledIndexFilterShow
 506         );
 507
 508         DefineCustomStringVariable(
 509                 "plantuner.only_index",
 510                 "List of explicitly enabled indexes (overload plantuner.disable_index and plantuner.enable_index)",
 511                 "Only indexes in this list are allowed",
 512                 &onlyIndexesOutStr,
 513                 "",
 514                 PGC_USERSET,
 515                 0,
 516 #if PG_VERSION_NUM >= 90100
 517                 checkOnlyIndexes,
 518                 assignOnlyIndexesNew,
 519 #else
 520                 assignOnlyIndexes,
 521 #endif
 522                 onlyIndexFilterShow
 523         );
 524
 525         DefineCustomBoolVariable(
 526                 "plantuner.fix_empty_table",
 527                 "Sets to zero estimations for empty tables",
 528                 "Sets to zero estimations for empty or newly created tables",
 529                 &fix_empty_table,
 530 #if PG_VERSION_NUM >= 80400
 531                 fix_empty_table,
 532 #endif
 533                 PGC_USERSET,
 534 #if PG_VERSION_NUM >= 80400
 535                 GUC_NOT_IN_SAMPLE,
 536 #if PG_VERSION_NUM >= 90100
 537                 NULL,
 538 #endif
 539 #endif
 540                 NULL,
 541                 NULL
 542         );
 543
 544         if (get_relation_info_hook != execPlantuner )
 545         {
 546                 prevHook = get_relation_info_hook;
 547                 get_relation_info_hook = execPlantuner;
 548         }
 549 }