Re: PostgreSQL 7.1 forces sequence scan when there is no reason

Denis Perchine <dyp(at)perchine(dot)com> writes:
> On Monday 20 May 2002 21:48, Tom Lane wrote:
>> Hm. Is it possible that the rows with server_id = 15182 are clustered
>> together? Given that you are fetching 10011 rows from a 14224-page
>> table, it seems unlikely that an indexscan could be such a big win
>> unless there was a very strong clustering effect.

> Possible, but 10 000 records are less than 1% of all records.
> How can I figure out whether they are clustered.

Look at the ctid column for those records. The range of block numbers
in the ctids would tell the tale. I don't think Postgres itself
provides any operations on type TID, but you could dump the info into
a file and then analyze it.

> listmembers | server_id | 0 | 4 | 1150 |
> {34062,32715,42495,15182,38013,36503,13746,46532,42434,15392}
> |
> {0.038,0.027,0.024,0.0143333,0.012,0.01,0.00933333,0.00766667,0.00733333,0.007}
> | {12855,15419,16223,20598,26019,30733,34402,38139,40811,44986,49509}
> | 0.428932

Hmm. Correlation 0.43 is high enough to suggest that there's some
clustering effect. If you look in the archives there's been prior
discussion about whether to make the optimizer weight the correlation
factor more strongly.

regards, tom lane