Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work

On Tue, 01 Apr 2008 16:06:01 +0200, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>> Was the driver ever changed to take advantage of the above strategy?
>
> Well, it's automatic as long as you use the unnamed statement. About
> all that might need to be done on the client side is to use unnamed
> statements more often in preference to named ones, and I believe that
> something like that did get done in JDBC.
>
> regards, tom lane
>

PHP is also affected if you use pg_query_params...
Syntax : pg_query_params( "SQL with $ params", array( parameters )

Note that value is TEXT, indexed, there are 100K rows in table.

pg_query( "SELECT * FROM test WHERE id =12345" ); 1 rows in
0.15931844711304 ms
pg_query( "SELECT * FROM test WHERE value LIKE '1234%'" ); 11 rows in
0.26795864105225 ms

pg_query_params( "SELECT * FROM test WHERE id =$1", array( 12345 ) ); 1
rows in 0.16618013381958 ms
pg_query_params( "SELECT * FROM test WHERE value LIKE $1", array( '1234%'
)); 11 rows in 40.66633939743 ms

Last query does not use index.
However since noone uses pg_query_params in PHP (since PHP coders just
LOVE to manually escape their strings, or worse use magicquotes), noone
should notice ;)