"Kevin Hunter" <hunteke@xxxxxxxxxxx> writes: > Or, assuming the REINDEX is for speed/bloat, not for corruption, perhaps > an option to use the old index as a basis, rather than scanning the > entire table multiple times as with a CREATE INDEX CONCURRENTLY. That's been mentioned, it ought to be on the TODO. The trick is determining *when* to use the index and when to use the table -- but that's something the planner already does quite well and we could hopefully leverage that. Note that in typical cases it would be slower. REINDEX scans the table precisely once and sorts it. The sorting will probably have to do multiple passes through temporary files which is presumably what you're referring to. But those passes are still at least sequential. A full index scan has to do random access reads and in many cases read the same page many times to get different records. The cases where it would win would be where you have a lot of dead space in the table (but not in the index), partial indexes which don't cover much of the table, or a table which is already very well clustered (possibly, depending on other factors). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
