Received: from web2901.mail.yahoo.com (web2901.mail.yahoo.com [128.11.68.44]) by hub.org (8.10.1/8.10.1) with SMTP id e7PJL0I43639 for ; Fri, 25 Aug 2000 15:21:00 -0400 (EDT) Received: (qmail 3874 invoked by uid 60001); 25 Aug 2000 19:20:59 -0000 Message-ID: <20000825192059.3873.qmail@web2901.mail.yahoo.com> Received: from [63.79.167.169] by web2901.mail.yahoo.com; Fri, 25 Aug 2000 12:20:59 PDT Date: Fri, 25 Aug 2000 12:20:59 -0700 (PDT) From: Rini Dutta Subject: queries and inserts To: pgsql-sql@hub.org Cc: pgsql-hackers@hub.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200008/286 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/