...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.
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 ?
It means that while the CLUSTER itself considers them dead, they might still look alive to some older transaction, so they need to be copied. (That older transaction must not have touched the table yet, or else it would hold a lock that would prevent the CLUSTER from taking place). You might want to hunt down the source of those long-lived transactions and try to eliminated them.
It seems very costly to do the CLUSTER, if the new table is not really going to be a tiny fraction of the old table.
Is there a way for me to discover the approx number of "non-removables" BEFORE I do the CLUSTER ?
Not that I can think of. Well, other than doing a VACUUM VERBOSE, but that itself would be very costly to do and wasteful if it most of the table is dead and immediately going to be CLUSTERed anyway.
You might be able to use the contrib module pageinspect to come up with your own sampling technique. Although I don't immediately see how to do that.
Also, does the use of [index scan on "pk_cool"] basically depend on the ratio of removable/nonremovable row versions ?
I don't think so. It mostly depends on the correlation (i.e. how well it is already clustered), the maintenance_work_mem, and the size the table in pages, and the estimated size/number of the "live" rows. (The true costs depend on the live+nonremovable, but the planner does not have that number available to it so it uses "live" instead in making the estimates)
Cheers,
Jeff
