Re: Estimating costs (was Functional Indices)

On Wed, May 23, 2001 at 01:22:41PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > I was thinking "average runlength". If this were 10 for example, when it
> > came to calculating the cost of the index scan, it would divide the
> > per-tuple cost by 10.
>
> > You can go the simple calculation method which would count the number of
> > times the value in a column was different than the previous value, then
> > divide that into the total number of tuples. That's not difficult to
> > implement.
>
> Unfortunately, it is difficult to implement, in fact impossible, given
> the new sampling-based implementation of ANALYZE. You could only
> discover that runs of identical keys exist if you were willing to
> examine every row, not just a statistical sample.

Ouch! You're right. With such an implementation it's impossible.

> Since this seems a rather specialized situation, I'm not eager to pay
> that high a price to recognize it ...

I'm not sure how common this is (long runs in a foreign key column) and it's
probably not worth it in the general case. So, is there a column in
pg_statistic where I can twiddle the per-tuple index-scan cost? If so then
my own program can fill in the value. In my case 2 hours spent scanning at
4am is worth 20 seconds per query during the day.

I suppose it's unlikely that there will be a VACUUM ANALYZE EVERYTHING?

Doesn't matter I guess. Fiddling enable_seqscan makes everything mostly
right. We'd get better results with partial indexes anyway I think. Maybe I
should look at that some more.

Anyway, thank for listening.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/