Re: Configuration Advice

> From: "Steve" <cheetah(at)tanabi(dot)org>
> To: pgsql-performance(at)postgresql(dot)org
> Sent: 1/17/2007 2:41 PM
> Subject: [PERFORM] Configuration Advice
>
> SO ... our goal here is to make this load process take less time. It
> seems the big part is building the big summary table; this big summary
> table is currently 9 million rows big. Every night, we drop the table,
> re-create it, build the 9 million rows of data (we use COPY to put hte
> data in when it's prepared, not INSERT), and then build the indexes on it
> -- of which there are many. Unfortunately this table gets queried
> in a lot of different ways and needs these indexes; also unfortunately, we
> have operator class indexes to support both ASC and DESC sorting on
> columns so these are for all intents and purposes duplicate but required
> under Postgres 8.1 (we've recently upgraded to Postgres 8.2, is this still
> a requirement?)

Note that you only need to have the ASC and DESC versions of opclasses when
you are going to use multicolumn indexes with some columns in ASC order and
some in DESC order. For columns used by themselves in an index, you don't
need to do this, no matter which order you are sorting on.