[EMAIL PROTECTED] wrote:
On Fri, May 25, 2007 at 09:13:25AM +0100, Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
And since it's basically impossible to know the selectivity of this kind
of where condition, I doubt the planner would ever realistically want to
choose that plan anyway because of its poor worst-case behavior.
What is a real life example where an intelligent and researched
database application would issue a like or ilike query as their
primary condition in a situation where they expected very high
selectivity?
Avoiding a poor worst-case behaviour for a worst-case behaviour that
won't happen doesn't seem practical.
But if you are also filtering on e.g. date, and that has an index with
good selectivity, you're never going to use the text index anyway are
you? If you've only got a dozen rows to check against, might as well
just read them in.
The only time it's worth considering the behaviour at all is *if* the
worst-case is possible.
I notice you did not provide a real life example as requested. :-)
OK - any application that allows user-built queries: <choose column:
foo> <choose filter: contains> <choose target: "bar">
Want another? Any application that has a "search by name" box - users
can (and do) put one letter in and hit enter.
Unfortunately you don't always have control over the selectivity of
queries issued.
This seems like an ivory tower restriction. Not allowing best performance
in a common situation vs not allowing worst performance in a not-so-common
situation.
What best performance plan are you thinking of? I'm assuming we're
talking about trailing-wildcard matches here, rather than "contains"
style matches.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
