On Tue, Jun 24, 2008 at 2:38 PM, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
"Jeffrey Baker" <
jwbaker@gmail.com> writes:
> I'm quite aware of the problems of maintaining such a table and index, but
> the fact is that data warehouse type tables may never be updated after being
> created. The particular application I'm struggling with does a SELECT ...
> INTO ... ORDER BY to make an ordered table for querying every night. The
> problem is it takes longer, much longer, to create the index than to create
> the table, and in the end the index is as big as half the table anyway.
to take longer than sorting the same number of (smaller) index entries.
Have you done any profiling to find out what the problem is? Perhaps
there's something wrong with the setting of maintenance_work_mem (vs
work_mem).
For this query, work_mem is 100MB and maintenance_work_mem is 1GB, on a system with 8GB of memory. Notably I just installed a new storage subsystem and upgraded to 8.3.1 less than a week ago, so my experience with this instance is somewhat limited. Creating the table in this case takes half an hour and then indexing it requires almost an hour. Subsequently analyzing the table takes less than a minute, with statistics set to maximum.
Query performance is excellent. I was just brainstorming on ways to save time on the creation.
-jwb