Re: GiST, Not Using Index

> Ah.  Your problem is you need to mark sig_in() as IMMUTABLE, or at
> least [...]

That was it!  Thanks!

For your enjoyment:

test=# select count(*) from x;count  
--------200000
(1 row)

test=# explain analyze select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
     QUERY PLAN                                              
 
------------------------------------------------------------------------------------------------------Index Scan using
xxon x  (cost=0.00..4.82 rows=1 width=8) (actual time=5.07..93.15 rows=29 loops=1)  Index Cond: (y ~>=
'.*****..........................................................'::sig)Totalruntime: 93.31 msec
 
(3 rows)

test=# select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);                               y
         
 

------------------------------------------------------------------.*****.*..*..**.....*.*...*.*.*.....*....................*...*...*******.*..**.*.*.***...*....*.....**..*..*.*...**..**....**....*****..*...**......*.....*.....*....*..*...*...*..............*.*****..*...**.....*....*.....*..*......**....*.......**...*....******........*.**.....*...............**.........*.......*......*****.*.**.*...***.............*...........*.**.*..*..**...*....******.*.*...*..*.****.*.*..**..*....**.*...***.*..*...****.**.******....*..*.*.*.**.*..*....*..................*..*...*.......*******..**..***.*..*...*...*.*.***.*.*...**...***.......*.......*****..................*.*..*.*.....*...**.......*....**..**....******......*.*.**.*.*.*..*.....***.............**......**.....******.*.....*.*...*......*...*.**.......*........***..*.*.....*.******....*...*....***.*.*..*.*.....*..*......*..*.......*.....*********.*..*.*...*...*.*.*.......................*.*....*...*..*****..............*.....................*....*...*...*....*..*.*******.**.*..*....**.*.....*.....*..*.*.*..**....*...**..*...*.*****............**..*..*.***....*...*.**.............*.........*******...*.**.***..*.****.*..**..*....*.*.**.**.*..*........********.*...**..*..*.****..*...*..*.*..******.....*.*..*.**..*....*****.*....*..****.........*....*..*...*............**..*....*********.**.......*..*........*..*.***....*........**...**.....*.******...*...........*.*.....**...**...*...**.....*...*.**...*...*****..............***.**.*...*....*...**.*...***......*..**...******..**....*......**.*................*.*...*.**......*.*.**..*****....*.*.*...*.**..*......*...*..**.**.*...*...**....****...*****..*...*........*....*....*....*....................*...*...*****..**.*........*..*...*..*.*.*..*..*....**.*..***.*......*..*****...*.......................................*...........*..*********........*.......*.......*..*.*...*....*.......*........
(29 rows)

test=# drop index xx;
DROP INDEX
test=# explain analyze select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
 QUERY PLAN                                           
 
-----------------------------------------------------------------------------------------------Seq Scan on x
(cost=0.00..22.50rows=1 width=8) (actual time=35.16..2231.98 rows=29 loops=1)  Filter: (y ~>=
'.*****..........................................................'::sig)Totalruntime: 2232.18 msec
 
(3 rows)

-- 
Itai Zukerman  <http://www.math-hat.com/~zukerman/>