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.




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Reply via email to