Skip site navigation (1) Skip section navigation (2)

Re: [SQL] OFFSET impact on Performance???

From: Greg Stark <gsstark(at)mit(dot)edu>
To: PFC <lists(at)boutiquenumerique(dot)com>
Cc: "Oleg Bartunov" <oleg(at)sai(dot)msu(dot)su>, alex(at)neteconomist(dot)com,"Greg Stark" <gsstark(at)mit(dot)edu>,"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>,"Andrei Bintintan" <klodoma(at)ar-sd(dot)net>,pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: 2005-01-27 18:33:33
Message-ID: 87d5vqwwle.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
PFC <lists(at)boutiquenumerique(dot)com> writes:

> 	intset(x) seems to be like array[x] ?
> 	Actually what I want is the opposite. 

What you want is called UNNEST. It didn't get done in time for 8.0. But if
what you have is an array of integers the int_array_enum() function I quoted
in the other post is basically that.

> Note that int_agg_final_array() crashes my postgres, see my message in
> psql/general

You don't really need the int_array_aggregate function any more. You can write
your own aggregate using the new array operators:

test=> create or replace function array_push (anyarray, anyelement) returns anyarray as 'select $1 || $2' language sql immutable strict;
CREATE FUNCTION
test=> create aggregate array_aggregate (basetype=anyelement, sfunc=array_push, stype=anyarray, initcond = '{}');
CREATE AGGREGATE

Of course it's about 50x slower than the C implementation though:

test=> select icount(array_aggregate (foo_id)) from foo;
 icount 
--------
  15127
(1 row)

Time: 688.419 ms

test=> select icount(int_array_aggregate (foo_id)) from foo;
 icount 
--------
  15127
(1 row)

Time: 13.680 ms

(And no, that's not a caching artifact; the whole table is cached for both
trials)

-- 
greg


In response to

Responses

pgsql-performance by date

Next:From: PFCDate: 2005-01-27 19:11:55
Subject: Re: [SQL] OFFSET impact on Performance???
Previous:From: Andrew SullivanDate: 2005-01-27 18:27:31
Subject: Re: PostgreSQL vs. Oracle vs. Microsoft

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group