pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Itai Zukerman <zukerman@math-hat.com>
To: pgsql-sql@postgresql.org
Subject: GiST, Not Using Index
Date: Thu, 27 Feb 2003 14:37:02 -0500
Message-ID: <87adghtt0h.fsf_-_@pip.dynamiclogic.com> (raw)
In-Reply-To: <87lm03ghpo.fsf@matt.w80.math-hat.com>
References: <87bs0z1zzs.fsf@matt.w80.math-hat.com>
	<87lm03ghpo.fsf@matt.w80.math-hat.com>

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  <http://www.math-hat.com/~zukerman/;



view thread (5+ messages)  latest in thread

Message-ID: <87adghtt0h.fsf_-_@pip.dynamiclogic.com>
Permalink:  ../87adghtt0h.fsf_-_@pip.dynamiclogic.com/
Also on:    postgresql.org/message-id/87adghtt0h.fsf_-_@pip.dynamiclogic.com

 · 

reply
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
  Subject: Re: GiST, Not Using Index
  In-Reply-To: <87adghtt0h.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