Thread: Any way to *not* use server-side prepared statements in Postgresql?
In (say) Python, I can issue:
psycopg2.connect(...).cursor().execute("select * from account
where id='00100000006ONCrAAO'")
which on the server results in the following log entries:
2011-07-18 18:56:08 PDT LOG: duration: 6.112 ms statement:
select * from account where id='00100000006ONCrAAO'
However, in Java, issuing:
conn.createStatement().executeQuery("select * from account where
id = '00100000006ONCrAAO'");
results in:
2011-07-18 18:44:59 PDT LOG: duration: 4.353 ms parse <unnamed>:
select * from account where id = '00100000006ONCrAAO'
2011-07-18 18:44:59 PDT LOG: duration: 0.230 ms bind <unnamed>:
select * from account where id = '00100000006ONCrAAO'
2011-07-18 18:44:59 PDT LOG: duration: 0.246 ms execute
<unnamed>: select * from account where id = '00100000006ONCrAAO'
Some searching shows that the PG JDBC driver *always* uses prepared
statements:
http://postgresql.1045698.n5.nabble.com/JDBC-prepared-statements-amp-server-side-prepared-statements-td1919506.html
Is there any way to circumvent server prepared statements? If it makes
a difference, I'm asking regarding PG 8.4 and 9.0. Thanks in advance.
Yang Zhang <yanghatespam@gmail.com> Tuesday 19 of July 2011 04:07:05
> In (say) Python, I can issue:
>
> psycopg2.connect(...).cursor().execute("select * from account
> where id='00100000006ONCrAAO'")
>
> which on the server results in the following log entries:
>
> 2011-07-18 18:56:08 PDT LOG: duration: 6.112 ms statement:
> select * from account where id='00100000006ONCrAAO'
>
> However, in Java, issuing:
>
> conn.createStatement().executeQuery("select * from account where
> id = '00100000006ONCrAAO'");
>
> results in:
>
> 2011-07-18 18:44:59 PDT LOG: duration: 4.353 ms parse <unnamed>:
> select * from account where id = '00100000006ONCrAAO'
> 2011-07-18 18:44:59 PDT LOG: duration: 0.230 ms bind <unnamed>:
> select * from account where id = '00100000006ONCrAAO'
> 2011-07-18 18:44:59 PDT LOG: duration: 0.246 ms execute
> <unnamed>: select * from account where id = '00100000006ONCrAAO'
>
> Some searching shows that the PG JDBC driver *always* uses prepared
> statements:
> http://postgresql.1045698.n5.nabble.com/JDBC-prepared-statements-amp-serve
> r-side-prepared-statements-td1919506.html
>
> Is there any way to circumvent server prepared statements? If it makes
> a difference, I'm asking regarding PG 8.4 and 9.0. Thanks in advance.
Hi,
At the begining it's looks like your Java call is faster then python, and it's
about 1ms, so it's about 16%. Probably your call could be faster if You use
prepared statement, but...
Your call do not use prepared statements, as You even don't asked for those.
It uses extended query protocol which sends query in few "chunks", parse,
bind, execute - this is needed to allow result set fetches.
Regards,
Radosław Smogura
They're slower (by about 4x on our boxes) for cases where statements are being generated beyond simple value parameters, such as trying many distinct projections. On Mon, Jul 18, 2011 at 7:47 PM, Maciek Sakrejda <msakrejda@truviso.com> wrote: > It doesn't look like it. Any particular use case for this? The driver > uses unnamed prepared statements here, and I'd be curious to hear > where they are slower. > --- > Maciek Sakrejda | System Architect | Truviso > > 1065 E. Hillsdale Blvd., Suite 215 > Foster City, CA 94404 > (650) 242-3500 Main > www.truviso.com > -- Yang Zhang http://yz.mit.edu/
On 19 July 2011 14:07, Yang Zhang <yanghatespam@gmail.com> wrote:
> However, in Java, issuing:
>
> conn.createStatement().executeQuery("select * from account where
> id = '00100000006ONCrAAO'");
You probably want to be using a PreparedStatement here anyway (it's
just good practice to let the driver worry about parameter value
escaping etc, especially given that the rules are a bit variable
depending on server version)
> results in:
>
> 2011-07-18 18:44:59 PDT LOG: duration: 4.353 ms parse <unnamed>:
> select * from account where id = '00100000006ONCrAAO'
> 2011-07-18 18:44:59 PDT LOG: duration: 0.230 ms bind <unnamed>:
> select * from account where id = '00100000006ONCrAAO'
> 2011-07-18 18:44:59 PDT LOG: duration: 0.246 ms execute
> <unnamed>: select * from account where id = '00100000006ONCrAAO'
>
> Some searching shows that the PG JDBC driver *always* uses prepared
> statements:
http://postgresql.1045698.n5.nabble.com/JDBC-prepared-statements-amp-server-side-prepared-statements-td1919506.html
>
> Is there any way to circumvent server prepared statements? If it makes
> a difference, I'm asking regarding PG 8.4 and 9.0. Thanks in advance.
You can use the v2 protocol (which inserts parameter values as text,
rather than sending them out of line), but you will lose various other
bits of driver functionality that depend on the v3 protocol.
Perhaps a better question is: Why do you want to avoid server prepared
statements?
If it is because you want more compact logging, perhaps there is
something that can be improved on the server side?
Oliver
It doesn't look like it. Any particular use case for this? The driver uses unnamed prepared statements here, and I'd be curious to hear where they are slower. --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
On Mon, Jul 18, 2011 at 7:50 PM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 19 July 2011 14:07, Yang Zhang <yanghatespam@gmail.com> wrote:
>
>> However, in Java, issuing:
>>
>> conn.createStatement().executeQuery("select * from account where
>> id = '00100000006ONCrAAO'");
>
> You probably want to be using a PreparedStatement here anyway (it's
> just good practice to let the driver worry about parameter value
> escaping etc, especially given that the rules are a bit variable
> depending on server version)
>
>> results in:
>>
>> 2011-07-18 18:44:59 PDT LOG: duration: 4.353 ms parse <unnamed>:
>> select * from account where id = '00100000006ONCrAAO'
>> 2011-07-18 18:44:59 PDT LOG: duration: 0.230 ms bind <unnamed>:
>> select * from account where id = '00100000006ONCrAAO'
>> 2011-07-18 18:44:59 PDT LOG: duration: 0.246 ms execute
>> <unnamed>: select * from account where id = '00100000006ONCrAAO'
>>
>> Some searching shows that the PG JDBC driver *always* uses prepared
>> statements:
http://postgresql.1045698.n5.nabble.com/JDBC-prepared-statements-amp-server-side-prepared-statements-td1919506.html
>>
>> Is there any way to circumvent server prepared statements? If it makes
>> a difference, I'm asking regarding PG 8.4 and 9.0. Thanks in advance.
>
> You can use the v2 protocol (which inserts parameter values as text,
> rather than sending them out of line), but you will lose various other
> bits of driver functionality that depend on the v3 protocol.
Hmm...this sounded promising until I found that I'm using Python
psycopg2.4. They wrap libpq and dropped v2 protocol support by
psycopg2.3 (http://wiki.postgresql.org/images/7/77/Psycopg-2010-stuttgart.pdf),
yet the statements that are being issued still appear as above. I'll
still give the v2 protocol a shot, but something is inconsistent here.
>
> Perhaps a better question is: Why do you want to avoid server prepared
> statements?
> If it is because you want more compact logging, perhaps there is
> something that can be improved on the server side?
It's not for logging and we don't avoid prepared statements - we
typically use them, but for one particular workload they're causing a
slowdown. It's for situations where we're issuing many queries and few
queries share the same structure.
--
Yang Zhang
http://yz.mit.edu/
On 19 July 2011 15:03, Yang Zhang <yanghatespam@gmail.com> wrote: > On Mon, Jul 18, 2011 at 7:50 PM, Oliver Jowett <oliver@opencloud.com> wrote: >> You can use the v2 protocol (which inserts parameter values as text, >> rather than sending them out of line), but you will lose various other >> bits of driver functionality that depend on the v3 protocol. > > Hmm...this sounded promising until I found that I'm using Python > psycopg2.4. They wrap libpq and dropped v2 protocol support by > psycopg2.3 (http://wiki.postgresql.org/images/7/77/Psycopg-2010-stuttgart.pdf), > yet the statements that are being issued still appear as above. I'll > still give the v2 protocol a shot, but something is inconsistent here. Probably it is using the simple query protocol, not the extended query protocol that the JDBC driver uses. I would expect the simple protocol to perform essentially the same as the extended protocol for an identical query string with no driver-handled parameters. If you're seeing differences, I suspect you're not actually doing what you think you're doing. Perhaps you have driver-provided parameters? There are some cases that tickle server-side query planning issues, where using the unnamed statement with parameters doesn't result in the same query plan as inserting the parameter values as text in the query string yourself. Oliver
On Mon, Jul 18, 2011 at 8:40 PM, Oliver Jowett <oliver@opencloud.com> wrote: > On 19 July 2011 15:03, Yang Zhang <yanghatespam@gmail.com> wrote: >> On Mon, Jul 18, 2011 at 7:50 PM, Oliver Jowett <oliver@opencloud.com> wrote: > >>> You can use the v2 protocol (which inserts parameter values as text, >>> rather than sending them out of line), but you will lose various other >>> bits of driver functionality that depend on the v3 protocol. >> >> Hmm...this sounded promising until I found that I'm using Python >> psycopg2.4. They wrap libpq and dropped v2 protocol support by >> psycopg2.3 (http://wiki.postgresql.org/images/7/77/Psycopg-2010-stuttgart.pdf), >> yet the statements that are being issued still appear as above. I'll >> still give the v2 protocol a shot, but something is inconsistent here. > > Probably it is using the simple query protocol, not the extended query > protocol that the JDBC driver uses. > > I would expect the simple protocol to perform essentially the same as > the extended protocol for an identical query string with no > driver-handled parameters. > If you're seeing differences, I suspect you're not actually doing what > you think you're doing. Perhaps you have driver-provided parameters? > There are some cases that tickle server-side query planning issues, > where using the unnamed statement with parameters doesn't result in > the same query plan as inserting the parameter values as text in the > query string yourself. > > Oliver > Thanks, the V2 suggestion did the trick. The speedup is ~25%. Should tide us over until we rehaul our architecture. -- Yang Zhang http://yz.mit.edu/
On 19/07/11 10:07, Yang Zhang wrote: > Is there any way to circumvent server prepared statements? If it makes > a difference, I'm asking regarding PG 8.4 and 9.0. Thanks in advance. http://jdbc.postgresql.org/documentation/84/server-prepare.html Set the prepare threshold using a call to the PGStatement interface of the Statement you're interested in to control it on a statement by statement level. This requires access to the real, unwrapped Statement instance, which isn't available through some connection pooling systems. You can set the prepare threshold using the prepareThreshold connection argumnent to set it for all statements. Whether 0 or -1 disables prepared statements don't seem to be documented. Check the PgJDBC sources, or give it a try and see. -- Craig Ringer