X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0C85447580B for ; Thu, 27 Feb 2003 20:07:23 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 21845-09 for ; Thu, 27 Feb 2003 20:07:12 -0500 (EST) Received: from debbie2.questionmarket.com (debbie2.questionmarket.com [64.7.7.170]) by postgresql.org (Postfix) with ESMTP id 61C5A476164 for ; Thu, 27 Feb 2003 18:12:31 -0500 (EST) Received: from pip.dynlog.com (unknown [192.168.1.12]) by debbie2.questionmarket.com (Postfix) with SMTP id 685E28888; Thu, 27 Feb 2003 18:12:35 -0500 (EST) Received: (nullmailer pid 29494 invoked by uid 1000); Thu, 27 Feb 2003 23:12:35 -0000 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: GiST, Not Using Index From: Itai Zukerman In-Reply-To: <29842.1046385523@sss.pgh.pa.us> (Tom Lane's message of "Thu, 27 Feb 2003 17:38:43 -0500") 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> Date: Thu, 27 Feb 2003 18:12:35 -0500 Message-ID: <87of4xs4gs.fsf@pip.dynamiclogic.com> User-Agent: Gnus/5.090015 (Oort Gnus v0.15) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/605 X-Sequence-Number: 12003 > 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