pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rini Dutta <rinid@rocketmail.com>
To: pgsql-sql@hub.org
Cc: pgsql-hackers@hub.org
Subject: queries and inserts
Date: Fri, 25 Aug 2000 12:20:59 -0700 (PDT)
Message-ID: <20000825192059.3873.qmail@web2901.mail.yahoo.com> (raw)

Hi,

I am interested in how to speed up storage. About 1000
or more inserts may need to be performed at a time ,
and before each insert I need to look up its key from
the reference table. So each insert is actually a
query followed by an insert.
 
The tables concerned are :
CREATE TABLE referencetable(idx serial, rcol1 int4 NOT
NULL, rcol2 int4 NOT  NULL, rcol3 varchar(20) NOT
NULL, rcol4 varchar(20), PRIMARY KEY(idx) ...
CREATE INDEX index_referencetable on
referencetable(rcol1, rcol2, rcol3, rcol4);

CREATE TABLE datatable ( ref_idx int4,
start_date_offset int4 NOT NULL, stop_date_offset int4
NOT NULL, dcol4 float NOT NULL, dcol5  float NOT NULL,
PRIMARY KEY(ref_idx, start_date_offset), CONSTRAINT c1
FOREIGN KEY(ref_idx) REFERENCES referencetable(idx) );

I need to do the following sequence n number of times
- 
1. select idx (as key) from referencetable where
col1=c1 and col2=c2 and col3=c3 and col4=c4; (Would an
initial 'select into temptable'  help here since for a
large number of these queries 'c1' and 'c2'
comnbinations would remain constant ?)
2. insert into datatable values(key, ....);

I am using JDBC interface of postgresql-7.0.2 on
Linux. 'referencetable' has about 1000 records, it can
keep growing. 'datatable' has about 3 million records,
it would grow at a very fast rate. Storing 2000
records takes around 75 seconds after I vacuum
analyze. (before that it took around 40 seconds - ???)
. I am performing all the inserts ( including the
lookup) as one transaction.

Thanks,
Rini


__________________________________________________
Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/



view thread (2+ messages)  latest in thread

Message-ID: <20000825192059.3873.qmail@web2901.mail.yahoo.com>
Permalink:  ../20000825192059.3873.qmail@web2901.mail.yahoo.com/
Also on:    postgresql.org/message-id/20000825192059.3873.qmail@web2901.mail.yahoo.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-sql@postgresql.org
  Cc: rinid@rocketmail.com, pgsql-sql@hub.org, pgsql-hackers@hub.org
  Subject: Re: queries and inserts
  In-Reply-To: <20000825192059.3873.qmail@web2901.mail.yahoo.com>
  References: <20000825192059.3873.qmail@web2901.mail.yahoo.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