From: Teodor Sigaev Date: Wed, 8 May 2019 12:16:14 +0000 (+0300) Subject: only_index X-Git-Url: http://www.sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=commitdiff_plain;h=ecc61f2bf65d40d144e394dc228e9f93d865776e;hp=0bfe8d22e41a4a690ef9ca950f56c65ef194fe4d only_index --- diff --git a/README.plantuner b/README.plantuner index 6b16fba..17c8ba0 100644 --- a/README.plantuner +++ b/README.plantuner @@ -41,6 +41,9 @@ Syntax plantuner.enable_index List of indexes visible to planner even they are hided by plantuner.disable_index. + plantuner.only_index + List of explicitly enabled indexes (overload plantuner.disable_index + and plantuner.enable_index), so, only indexes in this list are allowed. Usage diff --git a/expected/plantuner.out b/expected/plantuner.out index 0d372ca..70d2bca 100644 --- a/expected/plantuner.out +++ b/expected/plantuner.out @@ -8,6 +8,9 @@ SHOW plantuner.disable_index; CREATE TABLE wow (i int, j int); CREATE INDEX i_idx ON wow (i); CREATE INDEX j_idx ON wow (j); +CREATE INDEX i1 ON WOW (i); +CREATE INDEX i2 ON WOW (i); +CREATE INDEX i3 ON WOW (i); SET enable_seqscan=off; SELECT * FROM wow; i | j @@ -47,3 +50,47 @@ SELECT * FROM wow; ---+--- (0 rows) +--test only index +RESET plantuner.disable_index; +RESET plantuner.enable_index; +SET enable_seqscan=off; +SET enable_bitmapscan=off; +SET enable_indexonlyscan=off; +SET plantuner.only_index="i1"; +SHOW plantuner.only_index; + plantuner.only_index +---------------------- + public.i1 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + QUERY PLAN +---------------------------- + Index Scan using i1 on wow + Index Cond: (i = 0) +(2 rows) + +SET plantuner.disable_index="i1,i2,i3"; +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + QUERY PLAN +---------------------------- + Index Scan using i1 on wow + Index Cond: (i = 0) +(2 rows) + +SET plantuner.only_index="i2"; +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + QUERY PLAN +---------------------------- + Index Scan using i2 on wow + Index Cond: (i = 0) +(2 rows) + +RESET plantuner.only_index; +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + QUERY PLAN +------------------------------- + Index Scan using i_idx on wow + Index Cond: (i = 0) +(2 rows) + diff --git a/plantuner.c b/plantuner.c index 8595463..1973d01 100644 --- a/plantuner.c +++ b/plantuner.c @@ -57,14 +57,26 @@ static int nEnabledIndexes = 0; static Oid *enabledIndexes = NULL; static char *enableIndexesOutStr = ""; +static int nOnlyIndexes = 0; +static Oid *onlyIndexes = NULL; +static char *onlyIndexesOutStr = ""; + get_relation_info_hook_type prevHook = NULL; static bool fix_empty_table = false; static bool plantuner_enable_inited = false; +static bool plantuner_only_inited = false; static bool plantuner_disable_inited = false; +typedef enum IndexListKind { + EnabledKind, + DisabledKind, + OnlyKind +} IndexListKind; + static const char * -indexesAssign(const char * newval, bool doit, GucSource source, bool isDisable) +indexesAssign(const char * newval, bool doit, GucSource source, + IndexListKind kind) { char *rawname; List *namelist; @@ -86,10 +98,20 @@ indexesAssign(const char * newval, bool doit, GucSource source, bool isDisable) !IsTransactionState()) { /* reset init state */ - if (isDisable) - plantuner_disable_inited = false; - else - plantuner_enable_inited = false; + switch(kind) + { + case EnabledKind: + plantuner_enable_inited = false; + break; + case DisabledKind: + plantuner_disable_inited = false; + break; + case OnlyKind: + plantuner_only_inited = false; + break; + default: + elog(ERROR, "wrong kind"); + } return newval; } @@ -103,10 +125,20 @@ indexesAssign(const char * newval, bool doit, GucSource source, bool isDisable) (int)(sizeof(Oid) * (nOids+1))); } - if (isDisable) - plantuner_disable_inited = true; - else - plantuner_enable_inited = true; + switch(kind) + { + case EnabledKind: + plantuner_enable_inited = true; + break; + case DisabledKind: + plantuner_disable_inited = true; + break; + case OnlyKind: + plantuner_only_inited = true; + break; + default: + elog(ERROR, "wrong kind"); + } foreach(l, namelist) { @@ -145,19 +177,28 @@ indexesAssign(const char * newval, bool doit, GucSource source, bool isDisable) if (doit) { - if (isDisable) - { - nDisabledIndexes = i; - if (disabledIndexes) - free(disabledIndexes); - disabledIndexes = newOids; - } - else + switch(kind) { - nEnabledIndexes = i; - if (enabledIndexes) - free(enabledIndexes); - enabledIndexes = newOids; + case EnabledKind: + nEnabledIndexes = i; + if (enabledIndexes) + free(enabledIndexes); + enabledIndexes = newOids; + break; + case DisabledKind: + nDisabledIndexes = i; + if (disabledIndexes) + free(disabledIndexes); + disabledIndexes = newOids; + break; + case OnlyKind: + nOnlyIndexes = i; + if (onlyIndexes) + free(onlyIndexes); + onlyIndexes = newOids; + break; + default: + elog(ERROR, "wrong kind"); } } @@ -177,32 +218,56 @@ cleanup: static const char * assignDisabledIndexes(const char * newval, bool doit, GucSource source) { - return indexesAssign(newval, doit, source, true); + return indexesAssign(newval, doit, source, DisabledKind); } static const char * assignEnabledIndexes(const char * newval, bool doit, GucSource source) { - return indexesAssign(newval, doit, source, false); + return indexesAssign(newval, doit, source, EnabledKind); +} + +static const char * +assignOnlyIndexes(const char * newval, bool doit, GucSource source) +{ + return indexesAssign(newval, doit, source, OnlyKind); } static void lateInit() { + if (!plantuner_only_inited) + indexesAssign(onlyIndexesOutStr, true, PGC_S_USER, OnlyKind); if (!plantuner_enable_inited) - indexesAssign(enableIndexesOutStr, true, PGC_S_USER, false); + indexesAssign(enableIndexesOutStr, true, PGC_S_USER, EnabledKind); if (!plantuner_disable_inited) - indexesAssign(disableIndexesOutStr, true, PGC_S_USER, true); + indexesAssign(disableIndexesOutStr, true, PGC_S_USER, DisabledKind); } #if PG_VERSION_NUM >= 90100 +static bool +checkOnlyIndexes(char **newval, void **extra, GucSource source) +{ + char *val; + + val = (char*)indexesAssign(*newval, false, source, OnlyKind); + + if (val) + { + *newval = val; + return true; + } + + return false; +} + static bool checkDisabledIndexes(char **newval, void **extra, GucSource source) { char *val; - val = (char*)indexesAssign(*newval, false, source, true); + val = (char*)indexesAssign(*newval, false, source, DisabledKind); if (val) { @@ -218,7 +283,7 @@ checkEnabledIndexes(char **newval, void **extra, GucSource source) { char *val; - val = (char*)indexesAssign(*newval, false, source, false); + val = (char*)indexesAssign(*newval, false, source, EnabledKind); if (val) { @@ -241,6 +306,12 @@ assignEnabledIndexesNew(const char *newval, void *extra) assignEnabledIndexes(newval, true, PGC_S_USER /* doesn't matter */); } +static void +assignOnlyIndexesNew(const char *newval, void *extra) +{ + assignOnlyIndexes(newval, true, PGC_S_USER /* doesn't matter */); +} + #endif static void @@ -251,7 +322,27 @@ indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent, lateInit(); - for(i=0;i 0) + { + ListCell *l; + + foreach(l, rel->indexlist) + { + IndexOptInfo *info = (IndexOptInfo*)lfirst(l); + bool remove = true; + + for(i=0; remove && iindexoid) + remove = false; + + if (remove) + rel->indexlist = list_delete_ptr(rel->indexlist, info); + } + + return; + } + + for(i=0; i= 90100 + checkOnlyIndexes, + assignOnlyIndexesNew, +#else + assignOnlyIndexes, +#endif + onlyIndexFilterShow + ); + DefineCustomBoolVariable( "plantuner.fix_empty_table", "Sets to zero estimations for empty tables", diff --git a/sql/plantuner.sql b/sql/plantuner.sql index f0bda23..ddd6fcc 100644 --- a/sql/plantuner.sql +++ b/sql/plantuner.sql @@ -5,6 +5,9 @@ SHOW plantuner.disable_index; CREATE TABLE wow (i int, j int); CREATE INDEX i_idx ON wow (i); CREATE INDEX j_idx ON wow (j); +CREATE INDEX i1 ON WOW (i); +CREATE INDEX i2 ON WOW (i); +CREATE INDEX i3 ON WOW (i); SET enable_seqscan=off; @@ -25,3 +28,24 @@ SET plantuner.enable_index="i_idx"; SHOW plantuner.enable_index; SELECT * FROM wow; +--test only index +RESET plantuner.disable_index; +RESET plantuner.enable_index; + +SET enable_seqscan=off; +SET enable_bitmapscan=off; +SET enable_indexonlyscan=off; + +SET plantuner.only_index="i1"; +SHOW plantuner.only_index; + +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + +SET plantuner.disable_index="i1,i2,i3"; +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + +SET plantuner.only_index="i2"; +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0; + +RESET plantuner.only_index; +EXPLAIN (COSTS OFF) SELECT * FROM wow WHERE i = 0;