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
|
| 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/>