Re: Performance on inserts

Tom Lane wrote:
>
> Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> > What is the status of
> > partial indices? Are they functional now, or have they been broken
> > forever (I'm not recalling)?
>
> They've been diked out of gram.y's syntax for CREATE INDEX at least
> since Postgres95. No way to tell who did that, why or when, AFAIK.
> There is still an awful lot of support code for them, however.

I suspect that current indexes don't store nulls (and are thereby
partial indexes in relation to nulls ;)

At least the following suggests it:
---8<------------8<------------8<------------8<---------
hannu=> explain select * from test1 where i=777;
NOTICE: QUERY PLAN:

Index Scan using test1_i_ndx on test1 (cost=2.05 rows=2 width=8)

EXPLAIN
hannu=> explain select * from test1 where i is null;
NOTICE: QUERY PLAN:

Seq Scan on test1 (cost=3144.36 rows=27307 width=8)
---8<------------8<------------8<------------8<---------

As the logic to include or not include something in index seems to be
there
for nulls (and thus can't be very badly bit-rotten) it should be
possible to
extend it for simpler =x or in(x,y,z) conditions.

> I have no good way to guess how much bit-rot has occurred in all that
> unexercised code ... but it'd be interesting to try to get it going
> again.

Of course the IS NULL case may just be hard-wired in the optimiser in
which
case there may be not much use of current code.

IIRC Postgres95 was the first postgres with SQL, so if already that did
not
have partial indexes then no SQL-grammar for postgreSQL had. (Or maybe
Illustra did but it was a separate effort anyway ;)

---------------
Hannu