Re: trying to use CLUSTER

David Sahagian wrote:
> Version=9.1.7
>
> INFO: clustering "my_cool_table" using sequential scan and sort
> INFO: "my_cool_table": found 1 removable, 1699139 nonremovable row versions in 49762 pages
> Detail: 1689396 dead row versions cannot be removed yet.
> CPU 9.80s/4.98u sec elapsed 175.92 sec.

> My expectation is that a VERY SMALL percentage of the row versions would actually get written to the
> new table!

> Can somebody tell me why some "dead row versions cannot be removed yet" ?
>
> I assume that means CLUSTER must write them to the new table ?

I would say so. The dead rows probably cannot be removed because
of a long running transaction.

Is there a reason why you use CLUSTER and not VACUUM FULL?
Does VACUUM FULL show the same symptoms (dead row versions
cannot be removed yet)?

> Is there a way for me to discover the approx number of "non-removables" BEFORE I do the CLUSTER ?
> ? Some pg_table query ? maybe after an analyze ?

SELECT n_live_tup, n_dead_tup
FROM pg_stat_all_tables
WHERE relname = 'my_cool_table';

> Also, does the use of [index scan on "pk_cool"] basically depend on the ratio of
> removable/nonremovable row versions ?

I guess it will use whatever is cheaper (faster).

Yours,
Laurenz Albe