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