Thread: Clustering with enough work_mem: copy heap in mem?
Hi, I have a table with 15M rows. Table is around 5GB on disk. Clustering the table takes 5 minutes. A seq scan takes 20 seconds. I guess clustering is done using a seq scan on the index and then fetching the proper rows in the heap. If that's the case, fetching random rows on disk is the cause of the enormous time it takes to cluster the table. Since I can set work_mem > 5GB. couldn't postgres do something like: - read the whole table in memory - access the table in memory instead of the disk when reading the "indexed" data ? I mean: there's access exclusive lock on the table while clustering, so I don't see any problem in doing it... this way youcould - avoid sorting (which is what is used in the method "create newtable as select * from oldtable order by mycol", and canbe 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"?
On Thu, Aug 20, 2009 at 8:28 AM, Scara Maccai<m_lists@yahoo.it> wrote: > Hi, > > I have a table with 15M rows. Table is around 5GB on disk. > > Clustering the table takes 5 minutes. > > A seq scan takes 20 seconds. > > I guess clustering is done using a seq scan on the index and then fetching the proper rows in the heap. > If that's the case, fetching random rows on disk is the cause of the enormous time it takes to cluster the table. Yep. > Since I can set work_mem > 5GB. couldn't postgres do something like: > > - read the whole table in memory > - access the table in memory instead of the disk when reading the "indexed" data I've found it easier to select everything into another table, truncate the original table, then insert the rows as: insert into orig_table select * from mytemptable order by field1,field2; If needs be you can lock the original table to prevent modifications while doing this.
> I've found it easier to select everything into another > table, truncate > the original table, then insert the rows as: that takes 50 seconds of pure sorting and 8GB of ram to sort; my method doesn't require more memory than the size of theheap table, and no sorting, since the index is already sorted. Basically the cluster operation would be: A) time it takes to do a full scan of the heap + B) time it takes to do a full scan of the index + C) time it takes to rewrite ordered heap and index of course C) is no different than any other method I guess. plus: with the "create as" method indexes, foreign keys etc have to be recreated on the tab (I'm not talking about timing:it's just that you have to "remember" to re-create whatever was in the old table...). Plus: if a table has a foreignkey to the table you're clustering, I guess the "create as" method doesn't work (I guess you can't drop a table thatis the foreign key of another one).
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.
> 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. Actually I read that, but it's complicated... it involves planning and a lot of other stuff I don't even know about... My "solution" I guess would be easier (but, of course, can't be used if you don't have enough work_mem): if heap table <= work_mem copy heap table in mem use that region as it was the real file else do the regular (slow!) cluster I guess this can be worse than the current way of doing it only when the table contains a lot of dead rows; in all othercases I can't see how cluster could ever become faster than a simple table + index scans. Of course, I'm not saying it's "very easy" to implement... but given the tons of ram a lot of people use in the servers,and the fact that work_mem can be set on a per-connection basis, I think it would be nice...