Re: conditional indexes

Hello,

I think that conditional indexes not so clever as can.. Just little one
example:

isbs=# create unique index person_login on person (login) where
login<>'';
CREATE INDEX

isbs=# explain select * from person where login='user';
QUERY PLAN
---------------------------------------------------------
Seq Scan on person (cost=0.00..53.34 rows=1 width=167)

Why it does not use person_login index - predicate login='user' definitely
also mean (login<>'') - indexes' predicate!

isbs=# explain select * from person where login='user' and login<>'';
QUERY PLAN

-----------------------------------------------------------------------------
Index Scan using person_login on person (cost=0.00..5.97 rows=1
width=167)

Postgres start to use conditional index only when I also pass index'
condition:
login='user' AND login<>'' ...

isbs=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.3 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4

--
best regards,
Ruslan A Dautkhanov rusland(at)scn(dot)ru

On Wed, 25 Jun 2003 17:33:27 +0800
Ruslan A Dautkhanov <rusland(at)scn(dot)ru> wrote:

> Postgres start to use conditional index only when I also pass index'
> condition:
> login='user' AND login<>'' ...

i belive it's pretty rational when you'll think that you can have your own operators which does something else, and allowing postgres to treat conditional indices the way you'd like them to behave, would require to put lot of really nasty workarounds.

just my point of view, though.

depesz

--

Hubert Lubaczewski wrote:

>On Wed, 25 Jun 2003 17:33:27 +0800
>Ruslan A Dautkhanov <rusland(at)scn(dot)ru> wrote:
>
>>Postgres start to use conditional index only when I also pass index'
>>condition:
>> login='user' AND login<>'' ...
>>
>>
>
>i belive it's pretty rational when you'll think that you can have your own operators which does something else, and allowing postgres to treat conditional indices the way you'd like them to behave, would require to put lot of really nasty workarounds.
>
>

Statistics: 10% of peoples drink 90% of bear consumed volumes.. .)

I think peoples in 99% of situations use _standart_ types. Custom types
is exotic and used rarely by most peoples,
only 1% use custom types often, I think. Next survey at
http://www.postgresql.org/ can be "Do you use custom data types or
operators?".

Just if Postgres will especially interact with _standart_ types it will
make 99% peoples that us it a little happier.
I remember another examples like this - Postgres will use sometimes
indexes on int8 fields only when you
explicitly cast values to int8... Maybe you can remember another
examples? If SQL developer play with custom
data types, let him be sometimes more concrete...

--
best regards,
Ruslan A Dautkhanov rusland(at)scn(dot)ru

On Wed, 25 Jun 2003, Ruslan A Dautkhanov wrote:

Bugs is not the right place for this, -general would be better.

> I think that conditional indexes not so clever as can.. Just little one
> example:
>
> isbs=# create unique index person_login on person (login) where
> login<>'';
> CREATE INDEX
>
> isbs=# explain select * from person where login='user';
> QUERY PLAN
> ---------------------------------------------------------
> Seq Scan on person (cost=0.00..53.34 rows=1 width=167)
>
> Why it does not use person_login index - predicate login='user' definitely
> also mean (login<>'') - indexes' predicate!

You have to be able to show that however, correctness needs to be first.
The general belief (what I get from the docs and past discussions) is that
doing the proofs would be expensive in general even for cases where the
index doesn't end up getting used.

From the docs (at least the cvs ones):

However, keep in mind that the predicate must match the conditions used in
the queries that are supposed to benefit from the index. To be precise, a
partial index can be used in a query only if the system can recognize that
the WHERE condition of the query mathematically implies the predicate of
the index. PostgreSQL does not have a sophisticated theorem prover that
can recognize mathematically equivalent expressions that are written in
different forms. (Not only is such a general theorem prover extremely
difficult to create, it would probably be too slow to be of any real use.)
The system can recognize simple inequality implications, for example "x <
1" implies "x < 2"; otherwise the predicate condition must exactly match
the query's WHERE condition or the index will not be recognized to be
usable.

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> The system can recognize simple inequality implications, for example "x <
> 1" implies "x < 2"; otherwise the predicate condition must exactly match
> the query's WHERE condition or the index will not be recognized to be
> usable.

The reason it understands that example, but not that foo = 'bar' implies
foo <> '', is that the implication rules are built to work with btree
index operators. The presence of an operator in a btree opclass is what
gives us enough confidence that we understand its semantics (including
its relationships to other operators) to make these sorts of deductions.

As an example, we understand that foo < 42 (in WHERE) implies foo <= 42
(a possible partial index condition) only if the < and <= operators
involved can be found in the same index opclass. It is their roles in
the opclass, *not* their names, that we use to understand their
relationship.

The problem with <> is that it is not a btree-indexable operator (simply
because an index would hardly ever be useful for searching for rows that
do not match a key). And so there are no implication rules for it.

It might be possible to teach the planner to recognize that foo = 'bar'
implies an index predicate that's written like NOT (foo = ''), but it
doesn't look like that would work today (there's no special handling for
NOT clauses...)

regards, tom lane