Re: Why search term results different query plan?

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Erol ?z <eroloz(at)esg(dot)com(dot)tr>, PostgreSQL General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Why search term results different query plan?
Date: 2001-09-30 03:45:23
Message-ID: 20010930134523.B12033@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, Sep 29, 2001 at 10:37:24PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)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(at)svana(dot)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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Michael R. Fahey 2001-09-30 10:02:55 Can't add PL/PGSQL function even after running createlang.
Previous Message Trond Eivind =?iso-8859-1?q?Glomsr=F8d?= 2001-09-30 03:13:40 Re: Encoding passwords