Re: Optimisation deficiency: currval('seq')-->seq scan, constant-->index scan

On Mon, 21 Aug 2000, Tom Lane wrote:

> > One thing it might be interesting (please tell me if you think
> > otherwise) would be to improve pg with better statistical information, by
> > using, for example, histograms.
>
> Yes, that's been on the todo list for a while.

If it's ok and nobody is working on that, I'll look on that subject.
I'll start by looking at the analize portion of vacuum. I'm thinking in
using arrays for the histogram (I've never used the array data type of
postgres).
Should I use 7.0.2 or the cvs version?

> Interesting article. We do most of what she talks about, but we don't
> have anything like the ClusterRatio statistic. We need it --- that was
> just being discussed a few days ago in another thread. Do you have any
> reference on exactly how DB2 defines that stat?

I don't remember seeing that information spefically. From what I've
read I can speculate:

1. They have clusterratios for both indexes and the relation itself.
2. They might use an index even if there is no "order by" if the table
has a low clusterratio: just to get the RIDs, then sort the RIDs and
fetch.
3. One possible way to calculate this ratio:
a) for tables
SeqScan
if tuple points to a next tuple on the same page then its
"good"
ratio = # good tuples / # all tuples
b) for indexes (high speculation ratio here)
foreach pointed RID in index
if RID is in same page of next RID in index than mark as
"good"

I suspect that if a tuple size is big (relative to page size) than the
cluster ratio is always low.

A tuple might also be "good" if it pointed to the next page.

Tiago