From 4658687fa586302f46026d1405b36a3fdac87612 Mon Sep 17 00:00:00 2001 From: teodor Date: Thu, 1 Oct 2009 17:22:36 +0000 Subject: [PATCH] Improve readme and add copyright --- README.plantuner | 74 ++++++++++++++++++++++++++++++++++++++++-------- plantuner.c | 25 ++++++++++++++++ 2 files changed, 87 insertions(+), 12 deletions(-) diff --git a/README.plantuner b/README.plantuner index 54c34f9..16aef5e 100644 --- a/README.plantuner +++ b/README.plantuner @@ -1,15 +1,65 @@ -plantuner module provides plantuner.forbid_index GUC which contains comma-separated -list of indexes forbidden to use in query. +Plantuner - enable planner hints -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) +contrib/plantuner is a contribution module for PostgreSQL 8.4+, which enable +planner hints. +All work was done by Teodor Sigaev (teodor@sigaev.ru) and + Oleg Bartunov (oleg@sai.msu.su). + +Sponsor: JFG Networks (www.jfg-networks.net) +Motivation + +It's very interesting to be able to control planner (provide hints), which is +currently impossible. This first version of plantuner provides a possibility to +hide specified indexes from PostgreSQL planner, so it will not use them. + +There are many situation, when developer want to temporarily disable specific +index(es), without dropping them, or to instruct planner to use specific index. +Installation + + * Get latest source of plantuner from CVS Repository + * gmake && gmake install && gmake installcheck + +Usage + +To enable the module you can either load shared library 'plantuner' in psql +session or specify 'shared_preload_libraries' option in postgresql.conf. + +=# LOAD 'plantuner'; +=# create table test(id int); +=# create index id_idx on test(id); +=# create index id_idx2 on test(id); +=# \d test + Table "public.test" + Column | Type | Modifiers +--------+---------+----------- + id | integer | +Indexes: + "id_idx" btree (id) + "id_idx2" btree (id) +=# explain select id from test where id=1; + QUERY PLAN +----------------------------------------------------------------------- + Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4) + Recheck Cond: (id = 1) + -> Bitmap Index Scan on id_idx2 (cost=0.00..4.34 rows=12 width=0) + Index Cond: (id = 1) +(4 rows) +=# set enable_seqscan=off; +=# set plantuner.forbid_index='id_idx2'; +=# explain select id from test where id=1; + QUERY PLAN +---------------------------------------------------------------------- + Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4) + Recheck Cond: (id = 1) + -> Bitmap Index Scan on id_idx (cost=0.00..4.34 rows=12 width=0) + Index Cond: (id = 1) +(4 rows) +=# set plantuner.forbid_index='id_idx2,id_idx'; +=# explain select id from test where id=1; + QUERY PLAN +------------------------------------------------------------------------- + Seq Scan on test (cost=10000000000.00..10000000040.00 rows=12 width=4) + Filter: (id = 1) +(2 rows) -It's recommended to load library by shared_preload_libraries option -in postgresql.conf diff --git a/plantuner.c b/plantuner.c index 4e0f0c5..bf3c511 100644 --- a/plantuner.c +++ b/plantuner.c @@ -1,5 +1,30 @@ /* * 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. */ #include -- 2.46.1