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

Re: [PERFORM] Query much slower when run from postgres function

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Scott Carey <scott(at)richrelevance(dot)com>
Cc: Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com>, Guillaume Cottenceau <gc(at)mnc(dot)ch>, Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: [PERFORM] Query much slower when run from postgres function
Date: 2009-03-09 21:40:26
Message-ID: 49B58CCA.8060308@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
Scott Carey wrote:
> 
>    1. And how do you do that from JDBC?  There is no standard concept of
>       ‘unnamed’ prepared statements in most database APIs, and if there
>       were the behavior would be db specific.  Telling PG to plan after
>       binding should be more flexible than unnamed prepared statements —
>       or at least more transparent to standard APIs.  E.g. SET
>       plan_prepared_postbind=’true’.

I've suggested that as a protocol-level addition in the past, but it
would mean a new protocol version. The named vs. unnamed statement
behaviour was an attempt to crowbar it into the protocol without
requiring a version change. If it's really a planner behaviour thing,
maybe it does belong at the SET level, but I believe that there's
usually an aversion to having to SET anything per query to get
reasonable plans.

>    2. How do you use those on a granularity other than global from jdbc?

prepareThreshold=N (as part of a connection URL),
org.postgresql.PGConnection.setPrepareThreshold() (connection-level
granularity), org.postgresql.PGStatement.setPrepareThreshold()
(statement-level granularity). See the driver docs.

>          ( — I tried setting max_prepared_transactions to 0 but this
>       didn’t seem to work either, and it would be global if it did).

max_prepared_transactions is to do with two-phase commit, not prepared
statements.

> In the end, we had to write our own client side code to deal with sql
> injection safely and avoid jdbc prepared statements to get acceptable
> performance in many cases (all cases involving partitioned tables, a few
> others).  At least dollar-quotes are powerful and useful for dealing
> with this.  Since the most important benefit of prepared statements is
> code clarity and sql injection protection, its sad to see weakness in
> control/configuration over prepared statement behavior at the parse/plan
> level get in the way of using them for those benefits.  

It's unfortunate that ended up doing this, because it >is< all
configurable on the JDBC side. Did you ask on pgsql-jdbc?

-O

In response to

Responses

pgsql-performance by date

Next:From: Mario SplivaloDate: 2009-03-09 22:13:32
Subject: Re: Query much slower when run from postgres function
Previous:From: Oliver JowettDate: 2009-03-09 21:31:42
Subject: Re: [PERFORM] Query much slower when run from postgres function

pgsql-jdbc by date

Next:From: Mario SplivaloDate: 2009-03-09 22:13:32
Subject: Re: Query much slower when run from postgres function
Previous:From: Kris JurkaDate: 2009-03-09 21:38:17
Subject: Re: getGeneratedKeys

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