pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PFC <lists@boutiquenumerique.com>
To: Oleg Bartunov <oleg@sai.msu.su>
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 14:11:13 +0100
Message-ID: <opsk9iwzf2th1vuj@musicbox> (raw)
In-Reply-To: <Pine.GSO.4.62.0501271415370.6701@ra.sai.msu.su>
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>



> 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 :

> 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.





view thread (50+ messages)  latest in thread

Message-ID: <opsk9iwzf2th1vuj@musicbox>
Permalink:  ../opsk9iwzf2th1vuj@musicbox/
Also on:    postgresql.org/message-id/opsk9iwzf2th1vuj@musicbox

 · 

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

* 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