Skip site navigation (1) Skip section navigation (2)

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

From: PFC <lists(at)peufeu(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Dave Cramer" <pg(at)fastcrypt(dot)com>
Cc: "Michael Paesold" <mpaesold(at)gmx(dot)at>, pgsql-hackers(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work
Date: 2008-04-01 14:43:58
Message-ID: op.t8xsjkmscigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
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 ;)

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-04-01 14:46:15
Subject: Re: [HACKERS] ANALYZE getting dead tuple counthopelessly wrong
Previous:From: Dave CramerDate: 2008-04-01 14:26:52
Subject: Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-04-01 17:28:45
Subject: Re: CallableStatement and getUpdateCount
Previous:From: Dave CramerDate: 2008-04-01 14:26:52
Subject: Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group