From: John Siracusa <siracusa@mindspring.com>
To: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: Feature request: smarter use of conditional indexes
Date: Sat, 06 Mar 2004 15:51:47 -0500
Message-ID: <BC6FA413.30DB6%siracusa@mindspring.com> (raw)
In-Reply-To: <19341.1078358036@sss.pgh.pa.us>
On 3/3/04 6:53 PM, Tom Lane wrote:
> John Siracusa <siracusa@mindspring.com> writes:
>> Given an index like this:
>> CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
>> and a query like this:
>> SELECT * FROM t1 WHERE c1 = 123;
>> I'd like the planner to be smart enough to use an index scan using i1.
>
> Send a patch ;-)
>
> The routine you want to teach about this is pred_test_simple_clause() in
> src/backend/optimizer/path/indxpath.c. ISTM that it's legitimate to
> conclude that "foo IS NOT NULL" is implied by "foo op anything" or
> "anything op foo" if the operator is marked strict.
Gack, C is not my forte...
So...I'm noodling around in pred_test_simple_clause() and my test query of:
SELECT * FROM t1 WHERE c1 = 123;
lands me in pred_test_simple_clause() with a "predicate" with a NodeTag of
NullTest, and a "clause" with a NodeTag of OpExpr. The clause "rightop"
IsA() Const. So far, it seems to make sense. It's comparing the clause "c1
= 123" with the predicate on the "i1" index ("IS NOT NULL") to see if one
implies the other.
But now I'm stuck, because IsA(predicate, NullTest) is *also* true if the
index i1 is dropped and index i2 is created like this:
CREATE UNIQUE INDEX i2 ON t1 (c1) WHERE c1 IS NOT NULL;
IOW, both "IS NOT NULL" and "IS NULL" lead to IsA(predicate, NullTest) being
true. I found this, which looked promising:
typedef enum BoolTestType
{
IS_TRUE, IS_NOT_TRUE, IS_FALSE, IS_NOT_FALSE, IS_UNKNOWN, IS_NOT_UNKNOWN
} BoolTestType;
typedef struct BooleanTest
{
Expr xpr;
Expr *arg; /* input expression */
BoolTestType booltesttype; /* test type */
} BooleanTest;
But then I realized that "predicate" is "Expr *" inside the
pred_test_simple_clause() function, and Expr seems only to have a single
field, which is tested by IsA()
typedef struct Expr
{
NodeTag type;
} Expr;
So apparently all I can do is find out if it's a null test, but not if it is
specifically "IS NOT NULL"
Now I'm stuck, and thinking that I'd have to modify more than
pred_test_simple_clause() to make this work. Any additional pointers? :)
-John
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: siracusa@mindspring.com
Subject: Re: Feature request: smarter use of conditional indexes
In-Reply-To: <BC6FA413.30DB6%siracusa@mindspring.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox