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

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: Steve Atkins <steve(at)blighty(dot)com>
Cc: PgSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Date: 2008-08-28 20:56:02
Message-ID: 48B710E2.2030000@wildenhain.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Steve Atkins wrote:
>
> 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.

Ah yes, I now see :-) Seems a general way of hinting statement
preparation on the nature of the expected data would be nice.

Something like special domain types maybe which have resonable
constraints which can be used by the planner. Obviously not
so easy solution.

Regards
Tino

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2008-08-28 21:17:07 Re: MySQL LAST_INSERT_ID() to Postgres
Previous Message Steve Atkins 2008-08-28 19:58:51 Re: SQL optimization - WHERE SomeField STARTING WITH ...