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

r d-3 wrote
> 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

Going from slightly dated memory here but...

The further left the wildcard the larger the portion of the index that has
to be scanned. In the worse case, your third example, everything has to be
scanned and basically the index is only acting as a table surrogate as
opposed to an index. If you really want to anchor the start you should
index and search on the reverse of the string so you can write it as a
postfix wildcard. The other option to index words via the full-text search
capabilities.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/9-2-4-Strange-behavior-when-wildcard-is-on-left-side-of-search-string-tp5751086p5751095.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.