Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux