Re: Why search term results different query plan?

On Sat, Sep 29, 2001 at 10:37:24PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > I don't really see how you can do any real estimates on %X% type queries,
> > although maybe it's assuming longer string => less matches.
>
> More exactly, it's assuming more fixed characters in the pattern ->
> less matches; see like_selectivity in src/backend/utils/adt/selfuncs.c.
> While the specific numbers it's using are made from whole cloth, I think
> the principle should hold good.
>
> I don't see any way to accumulate actual statistics that would improve
> the estimate, do you?

It is difficult, although obviously the results he is getting are bunk. The
only problem I can see is that it doesn't appear to be using any of the
gathered statistics at all. For example, if %STAR% matched the most common
value in the column, the selectivity would still be very low.

What I find most interesting about his case is that the query with %A% ran
40 times faster (260s to 6s) than the one where it thought only 1 row in p
was going to match. What that tells me is that the plan used for %A% is more
appropriate even though %STAR% is more selective.

How can you convince the planner of that?

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.