X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 166933A5F98 for ; Thu, 27 Jan 2005 18:33:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96537-03 for ; Thu, 27 Jan 2005 18:33:41 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 653153A5F08 for ; Thu, 27 Jan 2005 18:33:41 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CuESU-0004qX-00; Thu, 27 Jan 2005 13:33:34 -0500 To: PFC Cc: "Oleg Bartunov" , alex@neteconomist.com, "Greg Stark" , "Merlin Moncure" , "Andrei Bintintan" , pgsql-performance@postgresql.org Subject: Re: [SQL] OFFSET impact on Performance??? References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local> <87brbiah0f.fsf@stark.xeocode.com> <33c6269f0501261035bc87695@mail.gmail.com> <33c6269f05012620427c1ccc40@mail.gmail.com> In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 27 Jan 2005 13:33:33 -0500 Message-ID: <87d5vqwwle.fsf@stark.xeocode.com> Lines: 44 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/552 X-Sequence-Number: 10242 PFC 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