pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedqueries and inserts
2+ messages / 2 participants
[nested] [flat]
* queries and inserts
@ 2000-08-25 19:20 Rini Dutta <rinid@rocketmail.com>
2000-08-27 22:01 ` Re: queries and inserts Mitch Vincent <mitch@venux.net>
0 siblings, 1 reply; 2+ messages in thread
From: Rini Dutta @ 2000-08-25 19:20 UTC (permalink / raw)
To: pgsql-sql@hub.org; +Cc: pgsql-hackers@hub.org
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: queries and inserts
2000-08-25 19:20 queries and inserts Rini Dutta <rinid@rocketmail.com>
@ 2000-08-27 22:01 ` Mitch Vincent <mitch@venux.net>
0 siblings, 0 replies; 2+ messages in thread
From: Mitch Vincent @ 2000-08-27 22:01 UTC (permalink / raw)
To: Rini Dutta <rinid@rocketmail.com>; pgsql-sql@hub.org; +Cc: pgsql-hackers@hub.org
Removing indexes will speed up the INSERT portion but slow down the SELECT
portion.
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).
Have you EXPLAINed the SELECT query to see if index scans are being used
where possible?
-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/
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-08-27 22:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-25 19:20 queries and inserts Rini Dutta <rinid@rocketmail.com>
2000-08-27 22:01 ` Mitch Vincent <mitch@venux.net>
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