Re: like-operator on index-scan - Mailing list pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Andreas Degert <ad@papyrus-gmbh.de> writes:
> > I'd like to know if anyone can reproduce a strange error in V7.02
> > (debian i386). It happens when an index-scan is done in an index with
> > more than 80 entries, using a like-match, where the %-wildcard
> > directly follows a '/'.
> 
> Someone else just reported this a couple days ago.  Very odd.  I suspect
> the problem is locale-related; what LOCALE do you run the postmaster in?

yes, i just found that out myself (should check my mail more often :)).

It seems it doesn't make any difference which locale it is; I tried
de_DE (that's what I'm using normally), en_US, en_GB, ...

> 
> Also, it might help to look at the output of EXPLAIN VERBOSE for
> the misbehaving query.  That would let us see what indexscan limits
> are being generated.

This is the output of

explain verbose select count(*) from test where a like '/%';

(hand-formatted.. first and last time i've done that :))

{ AGG :startup_cost 2.02 :total_cost 2.02 :rows 1 :width 4 :state <> :qptargetlist   ({ TARGETENTRY      :resdom {
RESDOM               :resno 1 :restype 23 :restypmod -1                :resname count :reskey 0 :reskeyop 0
  :ressortgroupref 0 :resjunk false }      :expr   { AGGREG                :aggname count :basetype 0 :aggtype 23
        :target { CONST :consttype 23 :constlen 4                                :constisnull false
          :constvalue  4 [ 1 0 0 0 ]                                :constbyval true }                :usenulls false
:aggstartrue                :aggdistinct false }   }) :qpqual <> :lefttree { INDEXSCAN   :startup_cost 0.00 :total_cost
2.01:rows 1 :width 4   :state <> :qptargetlist <>   :qpqual   ({ EXPR      :typeOid 16  :opType op      :oper { OPER
:opno1209 :opid 850 :opresulttype 16 }      :args      ({ VAR :varno 1 :varattno 1 :vartype 25 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}       { CONST :consttype 25 :constlen -1 :constisnull false
:constvalue 6 [ 6 0 0 0 47 37 ]  :constbyval false }      )   })   :lefttree <> :righttree <> :extprm () :locprm ()
:initplan<>   :nprm 0  :scanrelid 1 :indxid ( 30208)   :indxqual   (({ EXPR       :typeOid 16  :opType op       :oper {
OPER:opno 667 :opid 743 :opresulttype 16 }       :args ({ VAR :varno 1 :varattno 1 :vartype 25 :vartypmod -1
       :varlevelsup 0 :varnoold 1 :varoattno 1}              { CONST :consttype 25 :constlen -1 :constisnull false
               :constvalue  5 [ 5 0 0 0 47 ]  :constbyval false }             )}     { EXPR       :typeOid 16  :opType
op      :oper { OPER :opno 664 :opid 740 :opresulttype 16 }       :args ({ VAR :varno 1 :varattno 1 :vartype 25
:vartypmod-1                    :varlevelsup 0 :varnoold 1 :varoattno 1}              { CONST :consttype 25 :constlen
-1:constisnull false                      :constvalue  5 [ 5 0 0 0 48 ]  :constbyval false }             )     }   ))
:indxqualorig  (({ EXPR :typeOid 16  :opType op            :oper { OPER :opno 667 :opid 743 :opresulttype 16 }
 :args ({ VAR :varno 1 :varattno 1 :vartype 25                         :vartypmod -1  :varlevelsup 0 :varnoold 1
                :varoattno 1}                   { CONST :consttype 25 :constlen -1 :constisnull false
       :constvalue  5 [ 5 0 0 0 47 ]                           :constbyval false }                  )     }     { EXPR
:typeOid16  :opType op            :oper { OPER :opno 664 :opid 740 :opresulttype 16 }            :args ({ VAR :varno 1
:varattno1 :vartype 25 :vartypmod -1                         :varlevelsup 0 :varnoold 1 :varoattno 1}
{CONST :consttype 25 :constlen -1 :constisnull false                           :constvalue  5 [ 5 0 0 0 48 ]
              :constbyval false }                  )     }   ))   :indxorderdir 1 } :righttree <> :extprm () :locprm ()
:initplan<> :nprm 0
 
}

It looks like the like-match is converted into a "x >= a and x < b"
form of expression (opno 664 is text_lt and opno 667 is text_ge, and
47 == ascii(/)), which doesn't work with collating order in most
locales? But it must be more compicated, because the query works when
there are less then 80 entries in the index. How many go onto a page?

ciao
Andreas