pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oliver Elphick <olly@lfix.co.uk>
To: postgres@countup.de
Cc: pgsql-performance@postgresql.org
Subject: Re: Select-Insert-Query
Date: Tue, 02 Mar 2004 11:48:06 +0000
Message-ID: <1078228086.13842.60.camel@cerberus.lfix.co.uk> (raw)
In-Reply-To: <20040302004903.DA8D55A816E@kserver17.erfurt12.de>
References: <20040302004903.DA8D55A816E@kserver17.erfurt12.de>

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




view thread (4+ messages)

Message-ID: <1078228086.13842.60.camel@cerberus.lfix.co.uk>
Permalink:  ../1078228086.13842.60.camel@cerberus.lfix.co.uk/
Also on:    postgresql.org/message-id/1078228086.13842.60.camel@cerberus.lfix.co.uk

 · 

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: olly@lfix.co.uk, postgres@countup.de
  Subject: Re: Select-Insert-Query
  In-Reply-To: <1078228086.13842.60.camel@cerberus.lfix.co.uk>

* 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