X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B9E8B475B07 for ; Thu, 27 Feb 2003 15:09:56 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 22386-10 for ; Thu, 27 Feb 2003 15:09:46 -0500 (EST) Received: from debbie2.questionmarket.com (debbie2.questionmarket.com [64.7.7.170]) by postgresql.org (Postfix) with ESMTP id 0B532475AAC for ; Thu, 27 Feb 2003 14:37:01 -0500 (EST) Received: from pip.dynlog.com (unknown [192.168.1.12]) by debbie2.questionmarket.com (Postfix) with SMTP id D668F885A for ; Thu, 27 Feb 2003 14:37:02 -0500 (EST) Received: (nullmailer pid 27514 invoked by uid 1000); Thu, 27 Feb 2003 19:37:02 -0000 To: pgsql-sql@postgresql.org Subject: GiST, Not Using Index From: Itai Zukerman References: <87bs0z1zzs.fsf@matt.w80.math-hat.com> <87lm03ghpo.fsf@matt.w80.math-hat.com> Date: Thu, 27 Feb 2003 14:37:02 -0500 In-Reply-To: <87lm03ghpo.fsf@matt.w80.math-hat.com> (Itai Zukerman's message of "Wed, 26 Feb 2003 10:54:59 -0500") Message-ID: <87adghtt0h.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/599 X-Sequence-Number: 11997 I've defined this: [...some definitions...] CREATE OPERATOR CLASS sig_ops FOR TYPE sig USING gist AS OPERATOR 7 ~>=, FUNCTION 1 g_sig_consistent (internal, internal, int2), FUNCTION 2 g_sig_union (bytea, internal), FUNCTION 3 g_sig_compress (internal), FUNCTION 4 g_sig_decompress (internal), FUNCTION 5 g_sig_penalty (internal, internal), FUNCTION 6 g_sig_picksplit (bytea, internal), FUNCTION 7 g_sig_same (internal, internal, internal); CREATE TABLE x (z sig); CREATE INDEX xx ON x USING gist (z sig_ops); [...insert 20000 rows into x...] VACUUM ANALYZE x; But I can't seem to get PG to use the index. Shouldn't this "explain verbose" at least attempt to check the cost of using the index? Am I not interpreting the output correctly? What am I doing wrong? # explain verbose # select z from x where z ~>= sig_in('{1,2,3}'::int4[]);; QUERY PLAN -------------------------------------------------------------------------------- { SEQSCAN :startup_cost 0.00 :total_cost 2826.01 :rows 24334 :width 8 :qptargetlist ( { TARGETENTRY :resdom { RESDOM :resno 1 :restype 1802511 :restypmod -1 :resname z :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1802511 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2 } } ) :qpqual ( { EXPR :typeOid 16 :opType op :oper { OPER :opno 1836035 :opid 1802521 :opresulttype 16 :opretset false } :args ( { VAR :varno 1 :varattno 2 :vartype 1802511 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2 } { EXPR :typeOid 1802511 :opType func :oper { FUNC :funcid 1802514 :funcresulttype 1802511 :funcretset false :funcformat 0 } :args ( { CONST :consttype 1007 :constlen -1 :constbyval false :constisnull false :constvalue 36 [ 36 0 0 0 1 0 0 0 0 0 0 0 23 0 0 0 3 0 0 0 1 0 0 0 1 0 0 0 2 0 0 0 3 0 0 0 ] } ) } ) } ) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 } Seq Scan on x (cost=0.00..2826.01 rows=24334 width=8) Filter: (z ~>= sig_in('{1,2,3}'::integer[])) (94 rows) -- Itai Zukerman