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@venux.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@rocketmail.com>
To: <pgsql-sql@hub.org>
Cc: <pgsql-hackers@hub.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/