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

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

From: Bill <pg(at)dbginc(dot)com>
To: Tino Wildenhain <tino(at)wildenhain(dot)de>
Cc: PgSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Date: 2008-08-28 21:32:41
Message-ID: 48B71979.6080907@dbginc.com (view raw or flat)
Thread:
Lists: pgsql-general
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 
do not understand how that is possible since optimizer does not know the 
value of the parameter at the time the SQL is parsed and optimized. When 
the parameter value is supplied it could just as easily be '%ABC' in 
which case an index cannot be used.

This is based on the assumption that PostgreSQL, like other database 
servers, parses and optimizes a parameterized query once then stores it 
in memory so it can be executed multiple times with different parameter 
values. The optimizer could only determine if an index could be used or 
not if it optimized the query each time it was executed after the 
parameter value was supplied.

Bill

In response to

Responses

pgsql-general by date

Next:From: Raymond O'DonnellDate: 2008-08-28 21:37:23
Subject: Re: MySQL LAST_INSERT_ID() to Postgres
Previous:From: Albretch MuellerDate: 2008-08-28 21:29:32
Subject: ERROR: relation . . . does not exist

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