pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Bartunov <oleg@sai.msu.su>
To: PFC <lists@boutiquenumerique.com>
Cc: alex@neteconomist.com, Greg Stark <gsstark@mit.edu>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: Thu, 27 Jan 2005 16:44:04 +0300 (MSK)
Message-ID: <Pine.GSO.4.62.0501271642280.6701@ra.sai.msu.su> (raw)
In-Reply-To: <opsk9iwzf2th1vuj@musicbox>
References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local>
	<87brbiah0f.fsf@stark.xeocode.com>
	<opsk7nngxwth1vuj@musicbox>
	<33c6269f0501261035bc87695@mail.gmail.com>
	<opsk8a28tcth1vuj@musicbox>
	<33c6269f05012620427c1ccc40@mail.gmail.com>
	<opsk89szusth1vuj@musicbox>
	<Pine.GSO.4.62.0501271332200.6701@ra.sai.msu.su>
	<opsk9cicntth1vuj@musicbox>
	<Pine.GSO.4.62.0501271415370.6701@ra.sai.msu.su>
	<opsk9iwzf2th1vuj@musicbox>

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



view thread (50+ messages)  latest in thread

Message-ID: <Pine.GSO.4.62.0501271642280.6701@ra.sai.msu.su>
Permalink:  ../Pine.GSO.4.62.0501271642280.6701@ra.sai.msu.su/
Also on:    postgresql.org/message-id/Pine.GSO.4.62.0501271642280.6701@ra.sai.msu.su

 · 

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: oleg@sai.msu.su, lists@boutiquenumerique.com, gsstark@mit.edu, merlin.moncure@rcsonline.com, klodoma@ar-sd.net
  Subject: Re: [SQL] OFFSET impact on Performance???
  In-Reply-To: <Pine.GSO.4.62.0501271642280.6701@ra.sai.msu.su>

* 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