On Thu, Aug 20, 2009 at 8:28 AM, Scara Maccai<m_li...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
