Re: like-operator on index-scan

Andreas Degert <ad@papyrus-gmbh.de> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> 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 :))

>     :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 }
>               )
>       }
>     ))

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

What we've got here is x >= '/' AND x < '0', which should work as far
as I can see, unless your machine uses a really peculiar collation
order.

What happens if you try the query in the form

select count(*) from test where a >= '/' and a < '0'

Do you get the same behavior?  If so, try changing the index bounds to
see where it works and stops working.

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

More than that, I'd think, at least for strings as short as you showed
in your example.  An index item only has about a dozen bytes of
overhead, so for short strings you ought to get three or four hundred
per index page.  You can check this by looking to see if the index
file has grown past its minimum size of 2 pages (16K).

The whole thing is quite peculiar.  Your example works fine for me;
can anyone else duplicate the failure, and if so on what platform?
        regards, tom lane