GiST, Not Using Index - Mailing list pgsql-sql

From Itai Zukerman
Subject GiST, Not Using Index
Date
Msg-id 87adghtt0h.fsf_-_@pip.dynamiclogic.com
Whole thread Raw
In response to Re: OffsetNumber, picksplit, and GiST  (Itai Zukerman <zukerman@math-hat.com>)
Responses Re: GiST, Not Using Index  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
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_cost2826.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
:varoattno2         }      }   )       :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  <http://www.math-hat.com/~zukerman/>


pgsql-sql by date:

Previous
From: "Dan Langille"
Date:
Subject: Analyse article
Next
From: "Dan Langille"
Date:
Subject: Re: Analyse article