From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Re: SELECT slows down on sixth execution
Date: Tue, 20 Oct 2015 08:55:02 +0200
Message-ID: <n04og6$7fr$1@ger.gmane.org> (raw)
In-Reply-To: <5621AF0D.5080203@socialserve.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.htmlhttps://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThresh...
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.
Thomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: SELECT slows down on sixth execution
In-Reply-To: <n04og6$7fr$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox