Re[2]: Weird indices

Joseph,

I think you're going a bit too far... Tom and Stephan have been very
patient explaining you the basics of indices.

>> The name of the game here is to make a plan *without* actually going
>> out and expending large amounts of time to find out the true state of
>> affairs; by the time you know for sure, you've already done the query.

Believe this. All the best DB engines including PostgreSQL work that
way. This is based on measures, on real life.

JS> Well I'd hope that extracting the count from the index should be very
JS> low cost. That is what indecies are for.

No, indices are made for finding a record in one go or for isolating a
small range of values.

JS> But certain things could be done.  Like planning for the case of there
JS> being a single not null value, and updating the indecies not to point at
JS> expired rows.

And then you'll ask when there are 2 not null values...?

JS> Isn't the point of a vacuum to get rid of old rows?  Then
JS> why doesn't it update the index as well?

It does. Look at vacuum verbose.

JS> I mean the explain shows that getting the count(*) from the field that
JS> is indexed has to do a seq scan, presumably to determine if the rows are
JS> in fact valid.

count(*) means you want all the rows that have all the fields "not
null". Read carefully : ALL THE FIELDS.

JS> That is ridiculous.

ahem. One solution to the problem is known as "optimizer hints" in
Oracle : you specify directly in the query HOW the optimizer should
execute the query. It's very useful in various situations. I have
asked Tom many times if that exists in PostgreSQL but didn't get any
answer. I guess it's on a TODO list somewhere ;-)

--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Perl, PostgreSQL