Re: [HACKERS] Interesting index/LIKE/join slowness problems

Ole Gjerde <gjerde(at)icebox(dot)org> writes:
> The pg install is from CVS last night around 7pm Central time.

Do you have USE_LOCALE defined?

> The problems seems to be rooted in 'OR' combined with 'LIKE'. If I remove
> the % in the string, explain shows the same (high) cost. If I also remove
> the 'LIKE' the cost basically goes to nothing. The cost is indeed
> correct, either of the 2 first cases takes ~5 minutes, while the last one
> (no LIKE) finishes instantly.

When you have just "where reference = 'AN914'", the system knows it can
use the index to scan just the tuples with keys between AN914 and AN914
(duh). Very few tuples actually get fetched.

As soon as you use LIKE with a %, more tuples have to be scanned. It's
particularly bad if you have USE_LOCALE; with the current code, that
basically means that LIKE 'AN914-%' will cause all tuples beginning with
key AN914- and running to the end of the table to be scanned.

See the extensive thread on this topic from about a month or two back
in the pgsql-hackers mail list archives; I don't feel like repeating the
info now.

When you throw in the OR, the indexqual logic basically breaks down
completely; I think you end up scanning the entire table. (This could
be made smarter, perhaps, but right now I don't believe the system is
able to figure out the union of indexqual conditions.) I would say it
is an optimizer bug that it is not reverting to sequential scan here
... that would be a good bit faster, I bet.

regards, tom lane