Re: GiST, Not Using Index - Mailing list pgsql-sql
| From | Itai Zukerman |
|---|---|
| Subject | Re: GiST, Not Using Index |
| Date | |
| Msg-id | 87of4xs4gs.fsf@pip.dynamiclogic.com Whole thread Raw |
| In response to | OffsetNumber, picksplit, and GiST (Itai Zukerman <zukerman@math-hat.com>) |
| List | pgsql-sql |
> 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/>