Received: from icebox.venux.net (icebox.venux.net [216.120.166.10]) by hub.org (8.10.1/8.10.1) with ESMTP id e7RLwCL04701; Sun, 27 Aug 2000 17:58:12 -0400 (EDT) Received: from doot (cx853045-b.phnx1.az.home.com [24.177.75.28]) by icebox.venux.net (Postfix) with SMTP id 5E86E26202; Sun, 27 Aug 2000 18:03:56 -0400 (EDT) Message-ID: <012401c01072$4ace15b0$0200000a@doot> From: "Mitch Vincent" To: "Rini Dutta" , Cc: References: <20000825192059.3873.qmail@web2901.mail.yahoo.com> Subject: Re: queries and inserts Date: Sun, 27 Aug 2000 15:01:02 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200008/291 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" To: Cc: 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/ >