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

Re: LIKE, leading percent, bind parameters and indexes

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: LIKE, leading percent, bind parameters and indexes
Date: 2006-05-25 17:46:14
Message-ID: 20060525174614.GB2954@phlogiston.dyndns.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, May 25, 2006 at 02:18:10PM -0300, Rodrigo Hjort wrote:
> make a index scan. Otherwise, i.e. using leading '%' on static text or bound
> paremeter, makes the planner always do a sequential scan. Is that the
> scenario?

I think more exactly, the planner can't possibly know how to plan an
indexscan with a leading '%', because it has nowhere to start.

Think of it this way: if you go to the public library, and say, "I
want a book.  I can't remember its name exactly, but it starts with
'daytime'," you can find it by going to the title index and browsing
for things that start that way.  If you go to the public library, and
say, "There's this book I want, but I can't remember the title.  It's
red," you're going to have a lot of books to look through.  Maybe all
of them.

If it were important enough -- say you left a $10,000 cheque inside
-- you might just start looking.  Maybe you'll get lucky, and hit it.  

A

-- 
Andrew Sullivan  | ajs(at)crankycanuck(dot)ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
		--J.D. Baldwin

In response to

Responses

pgsql-hackers by date

Next:From: Dave CramerDate: 2006-05-25 18:04:03
Subject: Re: LIKE, leading percent, bind parameters and indexes
Previous:From: Marc G. FournierDate: 2006-05-25 17:39:22
Subject: Re: Gborg and pgfoundry

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