From: Itai Zukerman <zukerman@math-hat.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: GiST, Not Using Index
Date: Thu, 27 Feb 2003 18:12:35 -0500
Message-ID: <87of4xs4gs.fsf@pip.dynamiclogic.com> (raw)
In-Reply-To: <29842.1046385523@sss.pgh.pa.us>
References: <87bs0z1zzs.fsf@matt.w80.math-hat.com>
<87lm03ghpo.fsf@matt.w80.math-hat.com>
<87adghtt0h.fsf_-_@pip.dynamiclogic.com>
<27896.1046379102@sss.pgh.pa.us>
<871y1ttoh4.fsf@pip.dynamiclogic.com>
<29842.1046385523@sss.pgh.pa.us>
> 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 xx on x (cost=0.00..4.82 rows=1 width=8) (actual time=5.07..93.15 rows=29 loops=1)
Index Cond: (y ~>= '.*****..........................................................'::sig)
Total runtime: 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.50 rows=1 width=8) (actual time=35.16..2231.98 rows=29 loops=1)
Filter: (y ~>= '.*****..........................................................'::sig)
Total runtime: 2232.18 msec
(3 rows)
--
Itai Zukerman <http://www.math-hat.com/~zukerman/;
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-sql@postgresql.org
Cc: zukerman@math-hat.com, tgl@sss.pgh.pa.us
Subject: Re: GiST, Not Using Index
In-Reply-To: <87of4xs4gs.fsf@pip.dynamiclogic.com>
* 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