Re: Weird indices

Tom Lane wrote:
>
> Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> > This raises some other questions. Why can't postgres get the count(*)
> > from the index? Why doesn't it predict the correct number of rows in
> > the planner? (25 estimated vs 16 actual).
>
> 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.

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

> We have to do a certain amount of guessing, otherwise the planner will
> be a net drag on performance. Accordingly, the estimates will never be
> perfectly accurate.

But certain things could be done. Like planning for the case of there
being a single not null value, and updating the indecies not to point at
expired rows. Isn't the point of a vacuum to get rid of old rows? Then
why doesn't it update the index as well?

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

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com