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

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

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Bill <pg(at)dbginc(dot)com>
Cc: PgSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Date: 2008-08-29 04:20:49
Message-ID: 48B77921.8070803@wildenhain.de (view raw or flat)
Thread:
Lists: pgsql-general
Bill wrote:
> 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.
>>
>> Regards
>> Tino
> Are you saying that a parameterized query whose WHERE clause is AFIELD 
> LIKE ? will use an index on AFIELD if the parameter value is 'ABC%'. I 
...
no, I'm not saying that anymore (nor did I intend to do :-) I was just
misreading your question. Sorry.

Regards
Tino

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2008-08-29 04:45:20
Subject: Re: MySQL LAST_INSERT_ID() to Postgres
Previous:From: Greg SmithDate: 2008-08-29 04:16:20
Subject: Re: WAL archiving to network drive

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