Re: Large databases, performance

From: Curt Sampson <cjs(at)cynic(dot)net>
To: Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Large databases, performance
Date: 2002-10-07 02:30:57
Message-ID: Pine.NEB.4.44.0210071127320.443-100000@angelic.cynic.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-performance pgsql-sql

On Thu, 3 Oct 2002, Shridhar Daithankar wrote:

> Our major concern remains load time as data is generated in real time and is
> expecetd in database with in specified time period.

If your time period is long enough, you can do what I do, which is
to use partial indexes so that the portion of the data being loaded
is not indexed. That will speed your loads quite a lot. Aftewards
you can either generate another partial index for the range you
loaded, or generate a new index over both old and new data, and
then drop the old index.

The one trick is that the optimizer is not very smart about combining
multiple indexes, so you often need to split your queries across
the two "partitions" of the table that have separate indexes.

> Shall I subscribe to performance?

Yes, you really ought to. The list is pgsql-performance(at)postgresql(dot)org(dot)

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-10-07 03:20:33 cross-posts (was Re: Large databases, performance)
Previous Message Curt Sampson 2002-10-07 02:27:04 Re: Large databases, performance

Browse pgsql-hackers by date

  From Date Subject
Next Message ellis 2002-10-07 02:37:08 Re: Moving to PostGres
Previous Message Curt Sampson 2002-10-07 02:27:04 Re: Large databases, performance

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2002-10-07 03:20:33 cross-posts (was Re: Large databases, performance)
Previous Message Curt Sampson 2002-10-07 02:27:04 Re: Large databases, performance

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2002-10-07 03:20:33 cross-posts (was Re: Large databases, performance)
Previous Message Curt Sampson 2002-10-07 02:27:04 Re: Large databases, performance