SQL optimization - WHERE SomeField STARTING WITH ...

Hello

2008/8/28 Bill <pg@dbginc.com>:

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?

there is some similar - look
http://www.depesz.com/index.php/2008/03/04/searching-for-longest-prefix/
http://pgfoundry.org/projects/prefix

regards
Pavel Stehule

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

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

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

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

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

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?

One workaround might be to rewrite the query using some string function
(substring, position, or write your own) and index over that function.