On Thu, 15 Jul 1999, Tom Lane wrote:
> Do you have USE_LOCALE defined?
Nope.. Not unless it defaults to on... I did a
./configure --prefix=/home/postgres ; make ; make install as usual
> 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.
Ok.. I get that.. But why does LIKE 'AN914' have the same problem? The %
doesn't have to be there as long as it's either LIKE or ~*(or ~ etc)
query. And that still doesn't explain why it happens with USE_LOCALE
off..
Also, since the ='s work using OR, why wouldn't LIKE also? Both methods
would use the indexes, and the LIKE doesn't take that much longer to run..
Doesn't make sense, especially concerning what you mention below..
> 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.
I haven't been able to find a discussion on this topic last few months, I
found discussion about something similar in March, but that didn't explain
it very well.. I'll just have to look some more :)
> 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.
Ok.. I can believe that.. This is a pretty nasty problem tho.. I don't
believe using OR with LIKE is all that rare.. Maybe it's rare on a 17
mill row table, but still..
What would be the outlook on fixing the problem and not the symptom? :)
As far as sequential scan being faster.. Unfortunately, this table has
about 17 million rows, so any kind of seq scan is gonna be really slow.
Thanks,
Ole Gjerde