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 316803A5EE1 for ; Thu, 27 Jan 2005 13:45:11 +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 60325-05 for ; Thu, 27 Jan 2005 13:45:05 +0000 (GMT) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr1.postgresql.org (Postfix) with ESMTP id B34B63A5EC1 for ; Thu, 27 Jan 2005 13:44:59 +0000 (GMT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id j0RDi45Q002747; Thu, 27 Jan 2005 16:44:04 +0300 (MSK) Date: Thu, 27 Jan 2005 16:44:04 +0300 (MSK) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: PFC Cc: alex@neteconomist.com, Greg Stark , Merlin Moncure , Andrei Bintintan , pgsql-performance@postgresql.org Subject: Re: [SQL] OFFSET impact on Performance??? In-Reply-To: Message-ID: References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local> <87brbiah0f.fsf@stark.xeocode.com> <33c6269f0501261035bc87695@mail.gmail.com> <33c6269f05012620427c1ccc40@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.367 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200501/535 X-Sequence-Number: 10225 On Thu, 27 Jan 2005, PFC wrote: > > >> for example, >> http://www.sai.msu.su/~megera/postgres/gist/code/7.3/README.intarray >> see OPERATIONS and EXAMPLE USAGE: >> > > Thanks, I already know this documentation and have used intarray > before (I find it absolutely fabulous in the right application, it has a > great potential for getting out of tight situations which would involve huge > unmanageable pivot or attributes tables). Its only drawback is that the gist > index creation time is slow and sometimes just... takes forever until the > disk is full. > However, it seems that integer && integer[] does not exist : Try intset(id) && int[]. intset is an undocumented function :) I'm going to add intset() to README. > >> SELECT * FROM table WHERE id && int[] > > explain analyze select * from temp t where id && ( > '{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[] ); > ERREUR: L'operateur n'existe pas : integer && integer[] > ASTUCE : Aucun operateur correspond au nom donne et aux types d'arguments. > Vous devez ajouter des conversions explicites de type. > > I have already used this type of intarray indexes, but you have to > create a special gist index with the gist__int_ops on the column, and the > column has to be an array. In my case the column is just a SERIAL PRIMARY > KEY, and should stay this way, and I don't want to create a functional index > in array[id] just for this feature ; so I guess I can't use the && operator. > Am I mistaken ? My index is the standard btree here. > It would be nice if the =ANY() could use the index just like > IN does ; besides at planning time the length of the array is known which > makes it behave quite just like IN(). > > So I'll use either an EXECUTE'd plpgsql-generated query (IN (....)) , > which I don't like because it's a kludge ; or this other solution which I > find more elegant : > > CREATE OR REPLACE FUNCTION tools.array_srf( INTEGER[] ) > RETURNS SETOF INTEGER RETURNS NULL ON NULL INPUT > LANGUAGE plpgsql AS > $$ > DECLARE > _data ALIAS FOR $1; > _i INTEGER; > BEGIN > FOR _i IN 1..icount(_data) LOOP > RETURN NEXT _data[_i]; > END LOOP; > RETURN; > END; > $$; > > ----------------------------------------------------------------------------------- > explain analyze select * from temp t where id =ANY( > '{1,2,3,4,5,6,7,8,9,10,11,12}' ); > Seq Scan on "temp" t (cost=0.00..5165.52 rows=65536 width=8) (actual > time=0.030..173.319 rows=12 loops=1) > Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12}'::integer[])) > Total runtime: 173.391 ms > > ----------------------------------------------------------------------------------- > explain analyze select * from temp t where id IN( 1,2,3,4,5,6,7,8,9,10,11,12 > ); > Index Scan using temp_pkey, temp_pkey, temp_pkey, temp_pkey, temp_pkey, > temp_pkey, temp_pkey, temp_pkey, temp_pkey, temp_pkey, temp_pkey, > temp_pkey on "temp" t (cost=0.00..36.49 rows=12 width=8) (actual > time=0.046..0.137 rows=12 loops=1) > Index Cond: ((id = 1) OR (id = 2) OR (id = 3) OR (id = 4) OR (id = 5) OR > (id = 6) OR (id = 7) OR (id = 8) OR (id = 9) OR (id = 10) OR (id = 11) OR (id > = 12)) > Total runtime: 0.292 ms > > ----------------------------------------------------------------------------------- > explain analyze select * from temp t where id in (select * from > tools.array_srf('{1,2,3,4,5,6,7,8,9,10,11,12}')); > Nested Loop (cost=15.00..620.20 rows=200 width=8) (actual time=0.211..0.368 > rows=12 loops=1) > -> HashAggregate (cost=15.00..15.00 rows=200 width=4) (actual > time=0.160..0.173 rows=12 loops=1) > -> Function Scan on array_srf (cost=0.00..12.50 rows=1000 width=4) > (actual time=0.127..0.139 rows=12 loops=1) > -> Index Scan using temp_pkey on "temp" t (cost=0.00..3.01 rows=1 > width=8) (actual time=0.010..0.012 rows=1 loops=12) > Index Cond: (t.id = "outer".array_srf) > Total runtime: 0.494 ms > > ----------------------------------------------------------------------------------- > explain analyze select * from temp t, (select * from > tools.array_srf('{1,2,3,4,5,6,7,8,9,10,11,12}')) foo where foo.array_srf = > t.id; > > Merge Join (cost=62.33..2824.80 rows=1000 width=12) (actual > time=0.215..0.286 rows=12 loops=1) > Merge Cond: ("outer".id = "inner".array_srf) > -> Index Scan using temp_pkey on "temp" t (cost=0.00..2419.79 > rows=131072 width=8) (actual time=0.032..0.056 rows=13 loops=1) > -> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=0.169..0.173 > rows=12 loops=1) > Sort Key: array_srf.array_srf > -> Function Scan on array_srf (cost=0.00..12.50 rows=1000 width=4) > (actual time=0.127..0.139 rows=12 loops=1) > Total runtime: 0.391 ms > > ----------------------------------------------------------------------------------- > Note that the meaning is different ; the IN removes duplicates in the array > but the join does not. > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83