pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chad Wagner <chad.wagner@gmail.com>
To: Steve <cheetah@tanabi.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Wed, 17 Jan 2007 16:34:10 -0500
Message-ID: <81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
	<26412.1168983547@sss.pgh.pa.us>
	<1168986053.16393.1169598917@webmail.messagingengine.com>
	<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
	<1169002739.19758.1169636285@webmail.messagingengine.com>
	<1169012767.6271.7.camel@localhost.localdomain>
	<1169044649.19974.1169715743@webmail.messagingengine.com>
	<1169050746.9586.44.camel@state.g2switchworks.com>
	<45AE79D5.9080000@fuzzy.cz>
	<Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>

On 1/17/07, Steve <cheetah@tanabi.org> wrote:
>
> However, each night we load data from a legacy cobol system into the SQL
> system and then we summarize that data to make the reports faster.  This
> load process is intensely insert/update driven but also has a hefty
> amount of selects as well.  This load process is taking ever longer to
> complete.


How many rows do you typically load each night?  If it is say less than 10%
of the total rows, then perhaps the suggestion in the next paragraph is
reasonable.

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


Perhaps, placing a trigger on the source table and building a "change log"
would be useful.  For example, you could scan the change log (looking for
insert, update, and deletes) and integrate those changes into your summary
table.  Obviously if you are using complex aggregates it may not be possible
to adjust the summary table, but if you are performing simple SUM's,
COUNT's, etc.  then this is a workable solution.


-- 
Chad
http://www.postgresqlforums.com/

view thread (31+ messages)  latest in thread

Message-ID: <81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com>
Permalink:  ../81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com/
Also on:    postgresql.org/message-id/81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chad.wagner@gmail.com, cheetah@tanabi.org
  Subject: Re: Configuration Advice
  In-Reply-To: <81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com>
  References: <1168982580.8275.1169588211@webmail.messagingengine.com> <26412.1168983547@sss.pgh.pa.us> <1168986053.16393.1169598917@webmail.messagingengine.com> <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> <1169002739.19758.1169636285@webmail.messagingengine.com> <1169012767.6271.7.camel@localhost.localdomain> <1169044649.19974.1169715743@webmail.messagingengine.com> <1169050746.9586.44.camel@state.g2switchworks.com> <45AE79D5.9080000@fuzzy.cz> <Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org> <81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox