Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes: >> We have been using the C locale for everything at our site, but >> there is occasionally talk of supporting characters outside the >> ASCII7 set. In playing around with indexing, to see what the >> impact of that would be, I stumbled across something which was >> mildly surprising. > >> In the C locale, if you want to search for an exact value which >> doesn't contain wildcard characters, it doesn't matter whether >> you use the 'LIKE' operator or the '=' operator. With LATIN1 >> encoding, it made three orders of magnitude difference, both in >> the estimated cost and the actual run time. > > What PG version are you testing? 8.4 and up should know that an > exact-match pattern can be optimized regardless of the lc_collate > setting. For reasons not worth getting into, I had an 8.3.8 database sitting around in this locale, so I was testing things there. I'll take the time to copy into an 8.4.4 database for further testing, and maybe 9.0 beta, too. That'll take hours, though, so I can't immediately test it. To be clear, though, the problem isn't that it didn't turn a LIKE with no wildcard characters into an equality test, it's that it would have been three orders of magnitude faster (because of an available index with an opclass specification) if it had treated an equality test as a LIKE. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
