From 176da910e8225a413bac8992e650507f10f9b9d1 Mon Sep 17 00:00:00 2001 From: teodor Date: Tue, 29 Sep 2009 13:12:41 +0000 Subject: [PATCH 1/1] Initial revision --- COPYRIGHT | 29 ++++++++ Makefile | 10 +++ README.plantuner | 15 ++++ expected/plantuner.out | 37 +++++++++ plantuner.c | 165 +++++++++++++++++++++++++++++++++++++++++ sql/plantuner.sql | 21 ++++++ 6 files changed, 277 insertions(+) create mode 100644 COPYRIGHT create mode 100644 Makefile create mode 100644 README.plantuner create mode 100644 expected/plantuner.out create mode 100644 plantuner.c create mode 100644 sql/plantuner.sql diff --git a/COPYRIGHT b/COPYRIGHT new file mode 100644 index 0000000..6e4705b --- /dev/null +++ b/COPYRIGHT @@ -0,0 +1,29 @@ +/* + * Copyright (c) 2009 Teodor Sigaev + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of the author nor the names of any co-contributors + * may be used to endorse or promote products derived from this software + * without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY CONTRIBUTORS ``AS IS'' AND ANY EXPRESS + * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE + * ARE DISCLAIMED. IN NO EVENT SHALL CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL + * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE + * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS + * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER + * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR + * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN + * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + diff --git a/Makefile b/Makefile new file mode 100644 index 0000000..add40de --- /dev/null +++ b/Makefile @@ -0,0 +1,10 @@ +subdir = contrib/plantuner +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global + +MODULE_big = plantuner +DOCS = README.plantuner +REGRESS = plantuner +OBJS=plantuner.o + +include $(top_srcdir)/contrib/contrib-global.mk diff --git a/README.plantuner b/README.plantuner new file mode 100644 index 0000000..54c34f9 --- /dev/null +++ b/README.plantuner @@ -0,0 +1,15 @@ +plantuner module provides plantuner.forbid_index GUC which contains comma-separated +list of indexes forbidden to use in query. + +USAGE (see sql/plantuner.sql: +% LOAD 'plantuner'; +% SET plantuner.forbid_index="i_idx, j_idx"; +% SET enable_seqscan=off; +% explain SELECT * FROM wow; + QUERY PLAN +-------------------------------------------------------------------------- + Seq Scan on wow (cost=10000000000.00..10000000031.40 rows=2140 width=8) + + +It's recommended to load library by shared_preload_libraries option +in postgresql.conf diff --git a/expected/plantuner.out b/expected/plantuner.out new file mode 100644 index 0000000..e279d83 --- /dev/null +++ b/expected/plantuner.out @@ -0,0 +1,37 @@ +LOAD 'plantuner'; +SHOW plantuner.forbid_index; + plantuner.forbid_index +------------------------ + +(1 row) + +CREATE TABLE wow (i int, j int); +CREATE INDEX i_idx ON wow (i); +CREATE INDEX j_idx ON wow (j); +SET enable_seqscan=off; +SELECT * FROM wow; + i | j +---+--- +(0 rows) + +SET plantuner.forbid_index="i_idx, j_idx"; +SELECT * FROM wow; + i | j +---+--- +(0 rows) + +SHOW plantuner.forbid_index; + plantuner.forbid_index +---------------------------- + public.i_idx, public.j_idx +(1 row) + +SET plantuner.forbid_index="i_idx, nonexistent, public.j_idx, wow"; +WARNING: 'nonexistent' does not exist +WARNING: 'wow' is not an index +SHOW plantuner.forbid_index; + plantuner.forbid_index +---------------------------- + public.i_idx, public.j_idx +(1 row) + diff --git a/plantuner.c b/plantuner.c new file mode 100644 index 0000000..4e0f0c5 --- /dev/null +++ b/plantuner.c @@ -0,0 +1,165 @@ +/* + * Copyright (c) 2009 Teodor Sigaev + */ + +#include + +#include +#include +#include +#include +#include +#include +#include +#include + +PG_MODULE_MAGIC; + +static int nIndexesOut = 0; +static Oid *indexesOut = NULL; +get_relation_info_hook_type prevHook = NULL; + +static char *indexesOutStr = ""; + +static const char * +indexesOutAssign(const char * newval, bool doit, GucSource source) +{ + char *rawname; + List *namelist; + ListCell *l; + Oid *newOids = NULL; + int nOids = 0, + i = 0; + + rawname = pstrdup(newval); + + if (!SplitIdentifierString(rawname, ',', &namelist)) + goto cleanup; + + if (doit) + { + nOids = list_length(namelist); + newOids = malloc(sizeof(Oid) * (nOids+1)); + if (!newOids) + elog(ERROR,"could not allocate %d bytes", sizeof(Oid) * (nOids+1)); + } + + foreach(l, namelist) + { + char *curname = (char *) lfirst(l); + Oid indexOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), true); + + if (indexOid == InvalidOid) + { + elog(WARNING,"'%s' does not exist", curname); + continue; + } + else if ( get_rel_relkind(indexOid) != RELKIND_INDEX ) + { + elog(WARNING,"'%s' is not an index", curname); + continue; + } + else if (doit) + { + newOids[i++] = indexOid; + } + } + + if (doit) + { + nIndexesOut = nOids; + indexesOut = newOids; + } + + pfree(rawname); + list_free(namelist); + + return newval; + +cleanup: + if (newOids) + free(newOids); + pfree(rawname); + list_free(namelist); + return NULL; +} + + +static void +indexFilter(PlannerInfo *root, Oid relationObjectId, bool inhparent, RelOptInfo *rel) { + int i; + + for(i=0;iindexlist) + { + IndexOptInfo *info = (IndexOptInfo*)lfirst(l); + + if (indexesOut[i] == info->indexoid) + { + rel->indexlist = list_delete_ptr(rel->indexlist, info); + break; + } + } + } + + /* + * Call next hook if it exists + */ + if (prevHook) + prevHook(root, relationObjectId, inhparent, rel); +} + +static const char* +IndexFilterShow(void) +{ + char *val, *ptr; + int i, + len; + + len = 1 /* \0 */ + nIndexesOut * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */); + ptr = val = palloc(len); + + *ptr ='\0'; + for(i=0; i