Well, getting closer. Maybe I should start version numbering the patches?
Things changed in this version:
* As Tom Lane pointed out, what to do with EXTEND INDEX. I've decided to
remove it since there is no defined syntax for it and you cannot make an
index more restrictive with it. Since the names of indicies don't matter,
you may as well drop the index and make a new one if you want to change
the predicate.
At this stage I have only removed it from the grammer because complete
removal would require many line-by-line changes and would end up with a
patch much larger than this one. I'd prefer to submit that as a separate
patch after this one is completed.
* Updates to the documentation as well as comments within the code. If you
see a spot I missed, let me know.
* Changes to the cost estimation. This is much better than before but still
needs some refining since index_selectivity != rel_selectivity. Feedback
welcomed.
Issues still needing to be fixed:
* Make it pg_dump-able. I've tried to extract the expression out the of
system tables by using stringToNode and deparse_expression but it doesn't
seem to work. I keep getting the error: "get_names_for_var: bogus
varlevelsup 0". Anyone know what's going on? See attachment <<expr.c>>.
* VACUUM still complains about tuple count mismatch. Anyone have a good idea
on how to deal with this?
* The regression tests need to be changed to deal with the newly added
regression tests for these indicies.
Oh, and it appears I accidently changed the allowed syntax a bit. You used
to have to qualify each field in the partial index predicate with the name
of the relation. That's no longer required.
Everyone still alive out there?
--
Martijn van Oosterhout <[EMAIL PROTECTED]>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.
diff --exclude gram.c -ur postgresql-7.1.orig/doc/TODO postgresql-7.1/doc/TODO
--- postgresql-7.1.orig/doc/TODO Mon Apr 9 04:11:09 2001
+++ postgresql-7.1/doc/TODO Sat Jul 7 15:06:55 2001
@@ -144,7 +144,7 @@
fails index can't store constant parameters
* -Allow SQL function indexes
* Add FILLFACTOR to index creation
-* Re-enable partial indexes
+* -Re-enable partial indexes
* Allow inherited tables to inherit index, UNIQUE constraint, and primary key
[inheritance]
* Prevent pg_attribute from having duplicate oids for indexes (Tom)
diff --exclude gram.c -ur postgresql-7.1.orig/doc/src/sgml/catalogs.sgml
postgresql-7.1/doc/src/sgml/catalogs.sgml
--- postgresql-7.1.orig/doc/src/sgml/catalogs.sgml Mon Apr 2 05:17:30 2001
+++ postgresql-7.1/doc/src/sgml/catalogs.sgml Sat Jul 7 15:01:17 2001
@@ -1053,7 +1053,7 @@
<entry>indpred</entry>
<entry><type>text</type></entry>
<entry></entry>
- <entry>Query plan for partial index predicate (not functional)</entry>
+ <entry>Query plan for partial index predicate</entry>
</row>
</tbody>
</tgroup>
diff --exclude gram.c -ur postgresql-7.1.orig/doc/src/sgml/indices.sgml
postgresql-7.1/doc/src/sgml/indices.sgml
--- postgresql-7.1.orig/doc/src/sgml/indices.sgml Sat Feb 24 05:11:55 2001
+++ postgresql-7.1/doc/src/sgml/indices.sgml Sat Jul 7 15:06:09 2001
@@ -557,11 +557,12 @@
<note>
<title>Note</title>
<para>
- Partial indices are not currently supported by
- <productname>PostgreSQL</productname>, but they were once supported
- by its predecessor <productname>Postgres</productname>, and much
- of the code is still there. We hope to revive support for this
- feature someday.
+ For a long time partial indicies were not supported by
+ <productname>PostgreSQL</productname>, but they were once supported by
+ its predecessor <productname>Postgres</productname>, and much of the
+ code was still there. Currently (July 2001) there is some work underway
+ to revive this feature. See the pgsql-general mailing list archives for
+ details.
</para>
</note>
diff --exclude gram.c -ur postgresql-7.1.orig/doc/src/sgml/ref/create_index.sgml
postgresql-7.1/doc/src/sgml/ref/create_index.sgml
--- postgresql-7.1.orig/doc/src/sgml/ref/create_index.sgml Mon Jan 29 13:53:33
2001
+++ postgresql-7.1/doc/src/sgml/ref/create_index.sgml Tue Jul 10 00:34:22 2001
@@ -25,8 +25,10 @@
<synopsis>
CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON
<replaceable class="parameter">table</replaceable>
[ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable
class="parameter">column</replaceable> [ <replaceable
class="parameter">ops_name</replaceable> ] [, ...] )
+ [ WHERE <replaceable class="parameter">expr</replaceable> ]
CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON
<replaceable class="parameter">table</replaceable>
[ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable
class="parameter">func_name</replaceable>( <replaceable
class="parameter">column</replaceable> [, ... ]) [ <replaceable
class="parameter">ops_name</replaceable> ] )
+ [ WHERE <replaceable class="parameter">expr</replaceable> ]
</synopsis>
<refsect2 id="R2-SQL-CREATEINDEX-1">
@@ -137,6 +139,15 @@
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">expr</replaceable></term>
+ <listitem>
+ <para>
+ Defines the expression for a partial index.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect2>
@@ -225,6 +236,23 @@
of these access methods are fully dynamic and do not have to be
optimized periodically (as is the case with, for example, static hash
access methods).
+ </para>
+
+ <para>
+ When the <command>WHERE</command> clause is present, this defines a
+ partial index. A partial index is an index that only covers a portion of
+ a table, usually a portion that is somehow more interesting than the
+ rest of the table. For example, if you have a table that contains both
+ billed and unbilled orders where the unbilled order take up a small
+ fraction of the total table and yet that is an often used section, you
+ can improve performance by creating an index on just that portion.
+ </para>
+
+ <para>
+ The expression used in the <command>WHERE</command> clause is restricted
+ to forms the planner can easily use. Each element can only consist of
+ <command>ATTR OP CONST</command> and these can only be joined by
+ <command>AND</command> and <command>OR</command> operators.
</para>
<para>
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/access/gist/gist.c
postgresql-7.1/src/backend/access/gist/gist.c
--- postgresql-7.1.orig/src/backend/access/gist/gist.c Sat Mar 24 11:54:34 2001
+++ postgresql-7.1/src/backend/access/gist/gist.c Wed Jul 4 22:05:47 2001
@@ -193,7 +193,7 @@
*/
if (oldPred != NULL)
{
- slot->val = htup;
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (ExecQual((List *) oldPred, econtext, false))
{
nitups++;
@@ -207,7 +207,7 @@
*/
if (pred != NULL)
{
- slot->val = htup;
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (!ExecQual((List *) pred, econtext, false))
continue;
}
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/access/hash/hash.c
postgresql-7.1/src/backend/access/hash/hash.c
--- postgresql-7.1.orig/src/backend/access/hash/hash.c Sat Mar 24 11:54:34 2001
+++ postgresql-7.1/src/backend/access/hash/hash.c Wed Jul 4 22:06:10 2001
@@ -128,7 +128,7 @@
*/
if (oldPred != NULL)
{
- slot->val = htup;
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (ExecQual((List *) oldPred, econtext, false))
{
nitups++;
@@ -142,7 +142,7 @@
*/
if (pred != NULL)
{
- slot->val = htup;
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (!ExecQual((List *) pred, econtext, false))
continue;
}
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/access/nbtree/nbtree.c
postgresql-7.1/src/backend/access/nbtree/nbtree.c
--- postgresql-7.1.orig/src/backend/access/nbtree/nbtree.c Sat Mar 24 11:54:35
2001
+++ postgresql-7.1/src/backend/access/nbtree/nbtree.c Wed Jul 4 21:59:55 2001
@@ -206,7 +206,8 @@
*/
if (oldPred != NULL)
{
- slot->val = htup;
+ /* Invalid buffer should be ok, index shouldn't go away, i
+hope */
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (ExecQual((List *) oldPred, econtext, false))
{
nitups++;
@@ -220,7 +221,8 @@
*/
if (pred != NULL)
{
- slot->val = htup;
+ /* Invalid buffer should be ok, index shouldn't go away, i
+hope */
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (!ExecQual((List *) pred, econtext, false))
continue;
}
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/access/rtree/rtree.c
postgresql-7.1/src/backend/access/rtree/rtree.c
--- postgresql-7.1.orig/src/backend/access/rtree/rtree.c Sat Mar 24 11:54:35
2001
+++ postgresql-7.1/src/backend/access/rtree/rtree.c Wed Jul 4 22:07:13 2001
@@ -182,7 +182,7 @@
*/
if (oldPred != NULL)
{
- slot->val = htup;
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (ExecQual((List *) oldPred, econtext, false))
{
nitups++;
@@ -196,7 +196,7 @@
*/
if (pred != NULL)
{
- slot->val = htup;
+ ExecStoreTuple( htup, slot, InvalidBuffer, false );
if (!ExecQual((List *) pred, econtext, false))
continue;
}
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/optimizer/path/indxpath.c
postgresql-7.1/src/backend/optimizer/path/indxpath.c
--- postgresql-7.1.orig/src/backend/optimizer/path/indxpath.c Sat Mar 24 11:54:40
2001
+++ postgresql-7.1/src/backend/optimizer/path/indxpath.c Wed Jul 4 19:59:53
+2001
@@ -196,8 +196,10 @@
* 4. Generate an indexscan path if there are relevant restriction
* clauses OR the index ordering is potentially useful for later
* merging or final output ordering.
+ *
+ * If there is a predicate, consider it anyway since the clause may be
+useful
*/
- if (restrictclauses != NIL || useful_pathkeys != NIL)
+ if (restrictclauses != NIL || useful_pathkeys != NIL || index->indpred
+!= NIL)
add_path(rel, (Path *)
create_index_path(root, rel, index,
restrictclauses,
@@ -1153,6 +1155,8 @@
ScanKeyData entry[3];
Form_pg_amop aform;
+ ExprContext *econtext;
+
pred_var = (Var *) get_leftop(predicate);
pred_const = (Const *) get_rightop(predicate);
clause_var = (Var *) get_leftop((Expr *) clause);
@@ -1302,7 +1306,8 @@
copyObject(clause_const),
copyObject(pred_const));
- test_result = ExecEvalExpr((Node *) test_expr, NULL, &isNull, NULL);
+ econtext = MakeExprContext(NULL, TransactionCommandContext);
+ test_result = ExecEvalExpr((Node *) test_expr, econtext, &isNull, NULL);
if (isNull)
{
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/optimizer/util/pathnode.c
postgresql-7.1/src/backend/optimizer/util/pathnode.c
--- postgresql-7.1.orig/src/backend/optimizer/util/pathnode.c Sat Mar 24 11:54:42
2001
+++ postgresql-7.1/src/backend/optimizer/util/pathnode.c Wed Jul 4 20:27:42
+2001
@@ -362,6 +362,11 @@
pathnode->alljoinquals = false;
pathnode->rows = rel->rows;
+ /* Not sure if this is necessary, but it should help if the
+ * statistics are too far off */
+ if( index->indpred && index->tuples < pathnode->rows )
+ pathnode->rows = index->tuples;
+
cost_index(&pathnode->path, root, rel, index, indexquals, false);
return pathnode;
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/parser/analyze.c
postgresql-7.1/src/backend/parser/analyze.c
--- postgresql-7.1.orig/src/backend/parser/analyze.c Sat Mar 24 11:54:42 2001
+++ postgresql-7.1/src/backend/parser/analyze.c Wed Jul 4 23:03:16 2001
@@ -1525,6 +1525,12 @@
{
Query *qry;
+ /* Add the table to the range table so that the WHERE clause can use the
+fields */
+ /* no inheritence, yes we can use fields from relation */
+ RangeTblEntry *rte = addRangeTableEntry( pstate, stmt->relname, NULL, false,
+true );
+ /* no to join list, yes to namespace */
+ addRTEtoQuery( pstate, rte, false, true );
+
qry = makeNode(Query);
qry->commandType = CMD_UTILITY;
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/parser/gram.y
postgresql-7.1/src/backend/parser/gram.y
--- postgresql-7.1.orig/src/backend/parser/gram.y Sat Feb 24 05:12:06 2001
+++ postgresql-7.1/src/backend/parser/gram.y Tue Jul 10 00:20:28 2001
@@ -446,7 +446,7 @@
| DropPLangStmt
| DropTrigStmt
| DropUserStmt
- | ExtendStmt
+/* | ExtendStmt */
| ExplainStmt
| FetchStmt
| GrantStmt
@@ -2309,11 +2309,10 @@
* using <access> "(" (<col> with <op>)+ ")" [with
* <target_list>]
*
- * [where <qual>] is not supported anymore
*****************************************************************************/
IndexStmt: CREATE index_opt_unique INDEX index_name ON relation_name
- access_method_clause '(' index_params ')' opt_with
+ access_method_clause '(' index_params ')' opt_with where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -2322,7 +2321,7 @@
n->accessMethod = $7;
n->indexParams = $9;
n->withClause = $11;
- n->whereClause = NULL;
+ n->whereClause = $12;
$$ = (Node *)n;
}
;
@@ -2390,8 +2389,9 @@
* QUERY:
* extend index <indexname> [where <qual>]
*
+ * Removed. No longer supported. (July 2001)
*****************************************************************************/
-
+/*
ExtendStmt: EXTEND INDEX index_name where_clause
{
ExtendStmt *n = makeNode(ExtendStmt);
@@ -2400,7 +2400,7 @@
$$ = (Node *)n;
}
;
-
+*/
/*****************************************************************************
*
* QUERY:
diff --exclude gram.c -ur postgresql-7.1.orig/src/backend/utils/adt/selfuncs.c
postgresql-7.1/src/backend/utils/adt/selfuncs.c
--- postgresql-7.1.orig/src/backend/utils/adt/selfuncs.c Sat Mar 24 11:54:50
2001
+++ postgresql-7.1/src/backend/utils/adt/selfuncs.c Sun Jul 8 11:01:10 2001
@@ -2103,18 +2103,26 @@
Cost *indexStartupCost = (Cost *) PG_GETARG_POINTER(4);
Cost *indexTotalCost = (Cost *) PG_GETARG_POINTER(5);
Selectivity *indexSelectivity = (Selectivity *) PG_GETARG_POINTER(6);
+ Selectivity thisIndexSelectivity;
double numIndexTuples;
double numIndexPages;
+ /* Create the list of all relevent clauses by including any index predicates */
+ List *selectQuals = nconc( listCopy( index->indpred ), indexQuals );
+
/* Estimate the fraction of main-table tuples that will be visited */
- *indexSelectivity = clauselist_selectivity(root, indexQuals,
+ *indexSelectivity = clauselist_selectivity(root, selectQuals,
lfirsti(rel->relids));
+ /* Estimate the fraction of index tuples to be visited (for partial indicies)
+*/
+ /* This is a simple way of doing it. Should we call clauselist_selectivity
+again? */
+ thisIndexSelectivity = *indexSelectivity * rel->tuples / index->tuples;
+
/* Estimate the number of index tuples that will be visited */
- numIndexTuples = *indexSelectivity * index->tuples;
+ numIndexTuples = thisIndexSelectivity * index->tuples;
/* Estimate the number of index pages that will be retrieved */
- numIndexPages = *indexSelectivity * index->pages;
+ numIndexPages = thisIndexSelectivity * index->pages;
/*
* Always estimate at least one tuple and page are touched, even when
diff --exclude gram.c -ur postgresql-7.1.orig/src/test/regress/sql/create_index.sql
postgresql-7.1/src/test/regress/sql/create_index.sql
--- postgresql-7.1.orig/src/test/regress/sql/create_index.sql Thu Feb 17 14:40:02
2000
+++ postgresql-7.1/src/test/regress/sql/create_index.sql Sat Jul 7 15:32:19
+2001
@@ -50,20 +50,19 @@
--
-- BTREE partial indices
--- partial indices are not supported in PostgreSQL
--
---CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
--- where onek2.unique1 < 20 or onek2.unique1 > 980;
+CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
+ where onek2.unique1 < 20 or onek2.unique1 > 980;
---CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
--- where onek2.stringu1 < 'B';
+CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
+ where onek2.stringu1 < 'B';
--- EXTEND INDEX onek2_u2_prtl where onek2.stringu1 < 'C';
+EXTEND INDEX onek2_u2_prtl where onek2.stringu1 < 'C';
--- EXTEND INDEX onek2_u2_prtl;
+EXTEND INDEX onek2_u2_prtl;
--- CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
--- where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
+CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
+ where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
--
-- RTREE
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "lib/stringinfo.h"
#include "nodes/nodes.h"
#include "nodes/pg_list.h"
char *
deparse_expression(Node *expr, List *dpcontext, bool forceprefix);
PG_FUNCTION_INFO_V1(pg_get_expr);
/* ----------
* get_expr - Turn a node expression into a expression
* Used to get the expr used in partial indicies
* ----------
*/
Datum
pg_get_expr(PG_FUNCTION_ARGS)
{
text *expr = PG_GETARG_TEXT_P(0);
StringInfoData buf;
text *result;
List *list;
List *node;
char *str;
int len;
/*
* Get the rules definition and put it into executors memory
*/
initStringInfo(&buf);
list = (List*)stringToNode( VARDATA(expr) );
if( list->type != T_List )
PG_RETURN_NULL();
foreach( node, list )
{
str = deparse_expression( lfirst(node), NULL, true);
appendStringInfo( &buf, str );
}
len = buf.len + VARHDRSZ;
result = palloc(len);
VARATT_SIZEP(result) = len;
memcpy(VARDATA(result), buf.data, buf.len);
pfree(buf.data);
PG_RETURN_TEXT_P(result);
}
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
