Re: proposal for smaller indexes on index-ordered tables

On Tue, Jun 24, 2008 at 2:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Jeffrey Baker" <jwbaker(at)gmail(dot)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.
>
> There's something wrong with that: sorting the table rows surely ought
> 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