Re: [PATCH] Partial indicies almost working (I think)

On Tue, Jul 03, 2001 at 01:57:57PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > So, as far as I can tell, partial indecies would be completely usable, *if*
> > I could get the planner to use them. I'm pretty sure it goes wrong in
> > create_index_paths. The pred_test works fine but somewhere in the lines
> > below it doesn't realise it can use the index.
>
> Offhand I don't see why the existence of a predicate would matter. If
> you set enable_seqscan to FALSE, does it start using the index?

Hmm, maybe it's because it doesn't realise it would be an advantage to use
it. This is the test I used:

create table test (
seq serial,
clid int4,
billid text,
amount int4
);

-- Fill table with 300,000 rows
-- The billid column has about 10,000 nulls
-- Other values range from 0 to 9
create index test_ind on test(clid) where billid < '4';
-- The predicate matches about 4% of all the rows

explain select sum(clid) from test where billid < '3';

What I think is happening is that while the WHERE clause matches the
predicate, since my actual query doesn't reference clid anywhere other than
the output, it doesn't consider the index useful at all.

It just occured to me that there is an assumption there that there is no
point just trying a straight index scan with no constraints since a
sequential scan will always be faster in that case. But with partial
indicies that assumption is no longer valid.

I need to do some more tests when I get home this afternoon. Thanks for your
help.

> PS: please don't use // comments in Postgres code. They're unportable.

OK
--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
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.