Date: Fri, 24 Jun 2005 18:41:44 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Josh Berkus <josh@agliodbs.com>
Cc: Bruno Wolff III <bruno@wolff.to>,
	Kurt De Grave <Kurt.DeGrave@student.kuleuven.ac.be>,
	pgsql-performance@postgresql.org
Subject: Re: parameterized LIKE does not use index
Message-ID: <20050624234144.GE89438@decibel.org>
References: <42BA73CE.4030909@student.kuleuven.ac.be>
	<20050623141854.GA7505@wolff.to>
	<200506231155.36126.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200506231155.36126.josh@agliodbs.com>
User-Agent: Mutt/1.5.9i

On Thu, Jun 23, 2005 at 11:55:35AM -0700, Josh Berkus wrote:
> Bruno,
> 
> > I remember some discussion about delaying planning until the first
> > actual query so that planning could use actual parameters to do
> > the planning. If you really want to have it check the parameters
> > every time, I think you will need to replan every time. I don't
> > know if there is a way to save some of the prepare working while
> > doing this.
> 
> That wouldn't help much in Kurt's case.    Nor in most "real" cases, which is 
> why I think the idea never went anywhere.

I suspect the only way to do this and have it work well would be to
cache plans based on the relevant statistics of the parameters passed
in. Basically, as part of parsing (which could always be cached, btw, so
long as schema changes clear the cache), you store what fields in what
tables/indexes each parameter corresponds to. When you go to execute you
look up the stats relevant to each parameter; you can then cache plans
according to the stats each parameter has. Of course caching all that is
a non-trivial amount of work, so you'd only want to do it for pretty
complex queries.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"