Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
Hello 2008/8/28 Bill [EMAIL PROTECTED]: 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 Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
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 smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL optimization - WHERE SomeField STARTING WITH ...
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 smime.p7s Description: S/MIME Cryptographic Signature
