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