pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: postgres@countup.de
To: pgsql-performance@postgresql.org
Subject: Re: Select-Insert-Query
Date: Fri, 27 Feb 2004 19:47:13 +0000
Message-ID: <200402271947.13736.dev@archonet.com> (raw)
In-Reply-To: <20040227165239.7A6495A8173@kserver17.erfurt12.de>
References: <20040227165239.7A6495A8173@kserver17.erfurt12.de>

On Friday 27 February 2004 16:52, postgres@countup.de wrote:

*please* don't post HTML-only messages.

<br><br>what is the most performant way to select for
> example the first 99 rows of a table and insert them into another
> table...<br><br>at the moment i do this:<br><br>

> 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



view thread (4+ messages)  latest in thread

Message-ID: <200402271947.13736.dev@archonet.com>
Permalink:  ../200402271947.13736.dev@archonet.com/
Also on:    postgresql.org/message-id/200402271947.13736.dev@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, postgres@countup.de
  Subject: Re: Select-Insert-Query
  In-Reply-To: <200402271947.13736.dev@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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