[BUGS] 9.2.4: Strange behavior when wildcard is on left side of search string

[ERR ORR]

Hi,

this is with 9.2.4_PGDG / FC18 / 64bit upgraded from 9.1.8 via
dump/restore, settings kept for the most part.

Table has 1.5M records, the varchar(100) field in question has a *
varchar_ops* and a *varchar_pattern_ops* btree index.

3 Cases:

   - "MYFIELD" like 'BLA BLA *%*': *OK, about 7 msec*
   - "MYFIELD" like 'BLA *%* BLA': *OK, about 20 msec*
   - "MYFIELD" like '*%* BLA BLA': *NOT OK*

In the third case, the query will take anywhere between 4200ms and over
83Kms ms to deliver 2 results, in one case I broke it off after 10 MINUTES.
I never noticed this sort of behavior in the 9.x series.

According to explain, the query resolves into an index-only scan. I tried
to turn this off to
see how it behaves but the toggle in the .conf apparently has no effect.

What I find interesting is that, whereas with the default it would resolve
into an index-only scan on the *varchar_pattern_ops* index, after setting
indexscan_only=off, it would resolve into
an index-only scan on the *varchar_ops* index.

Another peculiarity is that the Explain for the bad case does not display
the "Sort" icon for the order-by clause, whereas the OK cases do display it.

Also, the problem does not appear to be a resource problem, as I have the
settings and resources to pull that whole table plus indexes into RAM, yet
still, there is constant disk activity during the query in the bad case.

And, yes, I DID reindex and/or *vacuum verbose analyze* the table after
each relevant change.

Thanks for any feedback on this. If you need any further info I'll be happy
to help as possible.

RD

Re: [BUGS] Re: 9.2.4: Strange behavior when wildcard is on left side of search string

[ERR ORR]

On 7 April 2013 15:52, Kevin Grittner  wrote:

> David Johnston  wrote:
>
> > varchar(100)
>
> > The other option to index words via the full-text search capabilities.
>
> Or for columns this short,  a similarity search on a trigram index.
>
> http://www.postgresql.org/docs/current/interactive/pgtrgm.html
>


> Or for columns this short,  a similarity search on a trigram index.

I had actually thought of that as the next thing to try out [?]

My understanding from your replies is that this behavior with *b-tree
indices* is not considered a bug but rather a case of "works as designed",
yet still and apart from the solution of my particular problem, the fact
that in 9.2.4 it is so bad that a simple query sometimes does not finish
after 10+ minutes with incessant disk activity tends to look like a problem
to me.

Thank you very much, gentlemen for your consideration.

RD
<<332.gif>>