Re: TODO item: adding VERBOSE option to CLUSTER [with patch]

Jim Cox wrote:
> On Mon, Oct 13, 2008 at 8:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>>> No, I was thinking of something along the lines of:
>>> INFO: clustering "public.my_c"
>>> INFO: complete, was 33%, now 100% clustered
>>> The only such measure that we have is the correlation, which isn't very
>>> good anyway, so I'm not sure if that's worthwhile.
>> It'd be possible to count the number of order reversals during the
>> indexscan, ie the number of tuples with CTID lower than the previous
>> one's. But I'm not sure how useful that number really is.

It will look bad for patterns like:
2
1
4
3
6
5
..

which for all practical purposes is just as good as a perfectly sorted
table. So no, I don't think that's a very useful metric either without
somehow taking caching effects into account.

> Another version of the patch should be attached, this time counting the
> number of "inversions" (pairs of tuples in the table that are in the wrong
> order) as a measure of the "sortedness" of the original data (scanned/live
> numbers still reported as an indication of the extent to which the table was
> vacuumed).

Until we have a better metric for "sortedness", my earlier suggestion to
print it was probably a bad idea. If anything, should probably print the
same correlation metric that ANALYZE calculates, so that it would at
least match what the planner uses for decision-making.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com