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

PFC <lists@boutiquenumerique.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
immutablestrict; 
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