optimal performance for inserts

Thanks for your suggestions, though I've already
considered most of them. (I have a detailed reply
below, interleaved with your mail).

I am considering an option but would need help from
somebody who knows how the backend works to be able to
figure out if any of the following options would help.
Consider the scenario of a database with say 3 tables,
and atleast 3 concurrent writers to all the tables
inserting different records. Which of the three
options would be expected to perform better ? (I am
using JDBC, I dont know if that is relevant)

1. Having a different Connection per writer
2. Having a different Connection per table
3. Having a single Connection which performs the 3
transactions sequentially.

I was trying out some tests to decide between option 1
& option 2 , but did not get any conclusive results.

Would be helpful to get some suggestions on the same.

Thanks,
Rini

--- Mitch Vincent <mitch(at)venux(dot)net> wrote:
> Removing indexes will speed up the INSERT portion
> but slow down the SELECT
> portion.
I cannot remove indexes since there may be other
queries to these tables at the same time when I am
doing the inserts.

> Just an FYI, you can INSERT into table (select
> whatever from another
> table) -- you could probably do what you need in a
> single query (but would
> also probably still have the speed problem).
I have not spent time on it but I could not figure out
how to have an insert statement such that one of the
attributes (only) is a result of a select from another
table. I would be interested in knowing if there is a
way to do that.

> Have you EXPLAINed the SELECT query to see if index
> scans are being used
> where possible?
Yes, the index scans are being used

> -Mitch
>
> ----- Original Message -----
> From: "Rini Dutta" <rinid(at)rocketmail(dot)com>
> To: <pgsql-sql(at)hub(dot)org>
> Cc: <pgsql-hackers(at)hub(dot)org>
> Sent: Friday, August 25, 2000 12:20 PM
> Subject: [SQL] queries and inserts
>
>
> > 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/
> >
>

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