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/