pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Select-Insert-Query
4+ messages / 3 participants
[nested] [flat]

* Select-Insert-Query
@ 2004-02-27 16:52 postgres@countup.de
  2004-02-27 19:47 ` Re: Select-Insert-Query Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 4+ messages in thread

From: postgres@countup.de @ 2004-02-27 16:52 UTC (permalink / raw)
  To: pgsql-performance

<html><head></head><body bgcolor="#ffffff" text="#000000" link="#000000" alink="#000000" vlink="#000000" marginwidth=10 marginheight=10 topmargin=10 leftmargin=10>Hi,
<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
<br>	insert into lastusers (account_id, counter_id, date, ip, hostname) values(a_account_id,userrecord.counter_id,userrecord.date,userrecord.ip,userrecord.hostname);
<br>end loop;
<br>
<br>i think "limit" is a performance killer, is that right? but what to do instead
<br>
<br>thanks
<br>bye</body></html>



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Select-Insert-Query
  2004-02-27 16:52 Select-Insert-Query postgres@countup.de
@ 2004-02-27 19:47 ` Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Richard Huxton @ 2004-02-27 19:47 UTC (permalink / raw)
  To: postgres@countup.de; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Select-Insert-Query
@ 2004-03-02 00:49 postgres@countup.de
  2004-03-02 11:48 ` Re: Select-Insert-Query Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: postgres@countup.de @ 2004-03-02 00:49 UTC (permalink / raw)
  To: pgsql-performance

<html><head></head><body bgcolor="#ffffff" text="#000000" link="#000000" alink="#000000" vlink="#000000" marginwidth=10 marginheight=10 topmargin=10 leftmargin=10>Hi,
<br>
<br>nobody has an idea? :-(
<br>
<br>-----Ursprüngliche Nachricht-----
<br>Von: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] Im Auftrag von postgres@countup.de
<br>Gesendet: Freitag, 27. Februar 2004 17:53
<br>An: pgsql-performance@postgresql.org
<br>Betreff: [PERFORM] Select-Insert-Query 
<br>
<br>Hi,
<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
<br>insert into lastusers (account_id, counter_id, date, ip, hostname) values(a_account_id,userrecord.counter_id,userrecord.date
 ,userrecord.ip,userrecord.hostname);
<br>end loop;
<br>
<br>i think "limit" is a performance killer, is that right? but what to do instead
<br>
<br>thanks
<br>bye
<br></body></html>



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Select-Insert-Query
  2004-03-02 00:49 Re: Select-Insert-Query postgres@countup.de
@ 2004-03-02 11:48 ` Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Oliver Elphick @ 2004-03-02 11:48 UTC (permalink / raw)
  To: postgres@countup.de; +Cc: pgsql-performance

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 <olly@lfix.co.uk>
LFIX Ltd




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-03-02 11:48 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-27 16:52 Select-Insert-Query postgres@countup.de
2004-02-27 19:47 ` Richard Huxton <dev@archonet.com>
2004-03-02 00:49 Re: Select-Insert-Query postgres@countup.de
2004-03-02 11:48 ` Oliver Elphick <olly@lfix.co.uk>

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