Skip site navigation (1) Skip section navigation (2)

Re: parameterized LIKE does not use index

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>,Kurt De Grave <Kurt(dot)DeGrave(at)student(dot)kuleuven(dot)ac(dot)be>,pgsql-performance(at)postgresql(dot)org
Subject: Re: parameterized LIKE does not use index
Date: 2005-06-24 23:41:44
Message-ID: 20050624234144.GE89438@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)decibel(dot)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?"

In response to

pgsql-performance by date

Next:From: Greg Sabino MullaneDate: 2005-06-25 01:09:29
Subject: Re: Configurator project launched
Previous:From: Dmitri BichkoDate: 2005-06-24 22:02:36
Subject: Re: Performance Tuning Article

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group