Re: GiST, Not Using Index
От | Itai Zukerman |
---|---|
Тема | Re: GiST, Not Using Index |
Дата | |
Msg-id | 87of4xs4gs.fsf@pip.dynamiclogic.com обсуждение исходный текст |
Ответ на | OffsetNumber, picksplit, and GiST (Itai Zukerman <zukerman@math-hat.com>) |
Список | 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/>
В списке pgsql-sql по дате отправления: