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

Re: SQL optimization - WHERE SomeField STARTING WITH ...

From: Steve Atkins <steve(at)blighty(dot)com>
To: PgSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Date: 2008-08-28 19:58:51
Message-ID: 858E9CAE-EAC2-40AA-A434-75A805B496B4@blighty.com (view raw or flat)
Thread:
Lists: pgsql-general
On Aug 28, 2008, at 12:27 PM, Tino Wildenhain wrote:

> Hi Bill,
>
> Bill wrote:
>> The SQL database servers I have worked with cannot use and index  
>> for a SELECT of the form
>> SELECT * FROM ATABLE
>> WHERE AFIELD LIKE ?
>> because there is no way to know the location of the wild card until  
>> the parameter value is known. InterBase and Firebird allow
>> SELECT * FROM ATABLE
>> WHERE AFIELD STARTING WITH ?
>> which is equivalent to LIKE  'ABC%' and will use an index on  
>> AFIELD. Is there a similar syntax in PostgreSQL?
>
> Yes, its actually: LIKE 'ABC%' and it will use an index.

I think Bill's point is that the planner can't take advantage of that  
at the time it's planning the query unless it has the string at that  
point. Something like "STARTING WITH" could be used with prepared  
statements too.

Cheers,
   Steve


In response to

Responses

pgsql-general by date

Next:From: Tino WildenhainDate: 2008-08-28 20:56:02
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Previous:From: Steve AtkinsDate: 2008-08-28 19:56:44
Subject: Re: MySQL LAST_INSERT_ID() to Postgres

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