pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rudi Starcevic <tech@wildcash.com>
To: pgsql-performance@postgresql.org
Subject: Bulk Insert and Index use
Date: Wed, 11 Aug 2004 09:04:02 +1000
Message-ID: <41195462.9090303@wildcash.com> (raw)

Hi,

I have a question on bulk checking, inserting into a table and
how best to use an index for performance.

The data I have to work with is a monthly CD Rom csv data dump of
300,000 property owners from one area/shire.

So every CD has 300,000 odd lines, each line of data which fills the 
'property' table.

Beginning with the first CD each line should require one SELECT and
one INSERT as it will be the first property with this address.

The SELECT uses fields like 'street' and 'suburb', to check for an 
existing property,
so I have built an index on those fields.

My question is does each INSERT rebuild the index on the 'street' and 
'suburb' fields?
I believe it does but I'm asking to be sure.

If this is the case I guess performance will suffer when I have, say, 
200,000
rows in the table.

Would it be like:

a) Use index to search on 'street' and 'suburb'
b) No result? Insert new record
c) Rebuild index on 'street' and 'suburb'

for each row?
Would this mean that after 200,000 rows each INSERT will require
the index of 000's of rows to be re-indexed?

So far I believe my only options are to use either and index
or sequential scan and see which is faster.

A minute for your thoughts and/or suggestions would be great.

Thanks.
Regards,
Rudi.




view thread (7+ messages)  latest in thread

Message-ID: <41195462.9090303@wildcash.com>
Permalink:  ../41195462.9090303@wildcash.com/
Also on:    postgresql.org/message-id/41195462.9090303@wildcash.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tech@wildcash.com
  Subject: Re: Bulk Insert and Index use
  In-Reply-To: <41195462.9090303@wildcash.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