Subject: Re: SELECT slows down on sixth execution
To: Pavel Stehule <pavel.stehule@gmail.com>,
 Thomas Kellerer <spam_eater@gmx.net>
References: <561E068F.1040702@socialserve.com>
 <A737B7A37273E048B164557ADEF4A58B50FB8D5E@ntex2010i.host.magwien.gv.at>
 <561E749D.4090301@socialserve.com>
 <A737B7A37273E048B164557ADEF4A58B50FB9F99@ntex2010i.host.magwien.gv.at>
 <5621AF0D.5080203@socialserve.com> <n04og6$7fr$1@ger.gmane.org>
 <CAFj8pRBNK_BS1vCo=RZgkEUc6V-KocFnpdFOJFDZ93iD=DFciw@mail.gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: Jonathan Rogers <jrogers@socialserve.com>
Message-ID: <56265436.4050806@socialserve.com>
Date: Tue, 20 Oct 2015 10:48:22 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFj8pRBNK_BS1vCo=RZgkEUc6V-KocFnpdFOJFDZ93iD=DFciw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 10/20/2015 03:45 AM, Pavel Stehule wrote:
> 
> 
> 2015-10-20 8:55 GMT+02:00 Thomas Kellerer <spam_eater@gmx.net
> <mailto:spam_eater@gmx.net>>:
> 
>     Jonathan Rogers schrieb am 17.10.2015 um 04:14:
>     >>> Yes, I have been looking at both plans and can see where they
>     diverge.
>     >>> How could I go about figuring out why Postgres fails to see the
>     large
>     >>> difference in plan execution time? I use exactly the same parameters
>     >>> every time I execute the prepared statement, so how would
>     Postgres come
>     >>> to think that those are not the norm?
>     >>
>     >> PostgreSQL does not consider the actual query execution time, it only
>     >> compares its estimates for there general and the custom plan.
>     >> Also, it does not keep track of the parameter values you supply,
>     >> only of the average custom plan query cost estimate.
>     >
>     > OK, that makes more sense then. It's somewhat tedious for the
>     purpose of
>     > testing to execute a prepared statement six times to see the plan
>     which
>     > needs to be optimized. Unfortunately, there doesn't seem to be any way
>     > to force use of a generic plan in SQL based on Pavel Stehule's reply.
> 
> 
>     If you are using JDBC the threshold can be changed:
> 
>        https://jdbc.postgresql.org/documentation/94/server-prepare.html
>      
>      https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold%28int%29
> 
>     As I don't think JDBC is using anything "exotic" I would be
>     surprised if this
>     can't be changed with other programming environments also.
> 
> 
> This is some different - you can switch between server side prepared
> statements and client side prepared statements in JDBC.  It doesn't
> change the behave of server side prepared statements in Postgres.

I am using psycopg2 with a layer on top which can automatically PREPARE
statements, so I guess that implements something similar to the JDBC
interface. I did solve my problem by turning off the automatic preparation.

-- 
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance