From: Rini Dutta <rinid@rocketmail.com>
To: Mitch Vincent <mitch@venux.net>
To: pgsql-sql@hub.org
Cc: pgsql-hackers@hub.org
Subject: optimal performance for inserts
Date: Thu, 31 Aug 2000 12:22:58 -0700 (PDT)
Message-ID: <20000831192258.1379.qmail@web2904.mail.yahoo.com> (raw)
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/
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, mitch@venux.net, pgsql-sql@hub.org, pgsql-hackers@hub.org
Subject: Re: optimal performance for inserts
In-Reply-To: <20000831192258.1379.qmail@web2904.mail.yahoo.com>
References: <20000831192258.1379.qmail@web2904.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