X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B17D2D1B8B6 for ; Tue, 2 Mar 2004 11:48:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 34741-04 for ; Tue, 2 Mar 2004 07:48:15 -0400 (AST) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 11FABD1B476 for ; Tue, 2 Mar 2004 07:48:06 -0400 (AST) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1Ay8Nh-000GMg-0Y; Tue, 02 Mar 2004 11:48:13 +0000 Received: from localhost ([127.0.0.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.30) id 1Ay8Na-0003tb-8I; Tue, 02 Mar 2004 11:48:06 +0000 Subject: Re: Select-Insert-Query From: Oliver Elphick Reply-To: olly@lfix.co.uk To: postgres@countup.de Cc: pgsql-performance@postgresql.org In-Reply-To: <20040302004903.DA8D55A816E@kserver17.erfurt12.de> References: <20040302004903.DA8D55A816E@kserver17.erfurt12.de> Content-Type: text/plain Organization: LFIX Ltd Message-Id: <1078228086.13842.60.camel@cerberus.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Tue, 02 Mar 2004 11:48:06 +0000 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/16 X-Sequence-Number: 5872 On Tue, 2004-03-02 at 00:49, postgres@countup.de wrote: > what is the most performant way to select for example the first 99 > rows of a table and insert them into another table... > > at the moment i do this: > > for userrecord in select * from table where account_id = a_account_id > and counter_id = userrecord.counter_id and visitortable_id between > a_minid and a_maxid limit 99 loop Using LIMIT without ORDER BY will give a selection that is dependent on the physical location of rows in the table; this will change whenever one of them is UPDATEd. > insert into lastusers (account_id, counter_id, date, ip, hostname) > values(a_account_id,userrecord.counter_id,userrecord.date > ,userrecord.ip,userrecord.hostname); > end loop; > > i think "limit" is a performance killer, is that right? but what to do > instead I'm sure it is the loop that is the killer. Use a query in the INSERT statement: INSERT INTO lastusers (account_id, counter_id, date, ip, hostname) SELECT * FROM table WHERE account_id = a_account_id AND counter_id = userrecord.counter_id AND visitortable_id between a_minid and a_maxid ORDER BY date DESC LIMIT 99; -- Oliver Elphick LFIX Ltd