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 A4A54D1BA21 for ; Fri, 27 Feb 2004 19:47:26 +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 94773-07 for ; Fri, 27 Feb 2004 15:47:21 -0400 (AST) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 414D6D1B860 for ; Fri, 27 Feb 2004 15:47:19 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1Awnx7-0009k8-0W; Fri, 27 Feb 2004 19:47:17 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id A7CE216C24; Fri, 27 Feb 2004 19:47:16 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id AD79F162D6; Fri, 27 Feb 2004 19:47:14 +0000 (GMT) From: Richard Huxton To: postgres@countup.de, pgsql-performance@postgresql.org Subject: Re: Select-Insert-Query Date: Fri, 27 Feb 2004 19:47:13 +0000 User-Agent: KMail/1.5 References: <20040227165239.7A6495A8173@kserver17.erfurt12.de> In-Reply-To: <20040227165239.7A6495A8173@kserver17.erfurt12.de> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200402271947.13736.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/318 X-Sequence-Number: 5842 On Friday 27 February 2004 16:52, postgres@countup.de wrote: *please* don't post HTML-only messages.

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 > 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; If that is the actual query, I'm puzzled as to what you're doing, since you don't know what it is you just inserted. Anyway, you can do this as a single query INSERT INTO lastusers (account_id ... hostname) SELECT a_account_id, counter_id... FROM table where... The LIMIT shouldn't take any time in itself, although if you are sorting then PG may need to sort all the rows before discarding all except the first 99. If this new query is no better, make sure you have vacuum analyse'd the tables and post the output of EXPLAIN ANALYSE for the query. -- Richard Huxton Archonet Ltd