Re: Clustering with enough work_mem: copy heap in mem?

Scara Maccai wrote:

> I mean: there's access exclusive lock on the table while clustering,
> so I don't see any problem in doing it... this way you could
>
> - avoid sorting (which is what is used in the method "create newtable
> as select * from oldtable order by mycol", and can be slow with 15M
> rows, plus in my case uses 8GB of ram...)
> - avoid random-reading on disk
>
> Am I missing something or it's just that "hasn't been done yet"?

The actual CLUSTER implementation is "do an indexscan, insert the rows
in the new heap in that order". It's pretty stupid. There was an
attempt to fix it (for example so that it could try to do a seqscan+sort
instead of indexscan), but it stalled.
http://archives.postgresql.org/message-id/87vdxg6a3d.fsf@oxford.xeocode.com
http://archives.postgresql.org/message-id/20080901072147.GB16993@svana.org

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.