BUG #14393: Querry plan creation time is too long
The following bug has been logged on the website:
Bug reference: 14393
Logged by: Vadim Akbashev
Email address: ufaowl@gmail.com
PostgreSQL version: 9.5.4
Operating system: Centos 6.8
Description:
Hello!
I have two questions concerning choosing\creation time of querry plan
regaring default_statistic_target parameter. I noticed that querry plan
creation time increases when default_statistic_target is more than 1000. It
reaches 14 sec for database without any load when default_statistic_target =
10 000.
I wonder:
1) Is such behavior considered normal? If it is, where I can get
documentation regulating this?
2) Is there any way to limit querry plan creation time?
Thank you!
P.S. Here are some examples:
1:
statistic_target=500
querry plan creation time = 464ms
pg_statistics size = 928 kb, 1912 kb toast, 72 kb index
2:
statistic_target = 10000
querry plan creation time = 14,3 sec
pg_statistics size = 928 kb, 10 MB toast, 72 kb index
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Mon, Oct 24, 2016 at 6:04 AM, <ufaowl@gmail.com> wrote:
The following bug has been logged on the website:
Bug reference: 14393
Logged by: Vadim Akbashev
Email address: ufaowl@gmail.com
PostgreSQL version: 9.5.4
Operating system: Centos 6.8
Description:Hello!
I have two questions concerning choosing\creation time of querry plan
regaring default_statistic_target parameter. I noticed that querry plan
creation time increases when default_statistic_target is more than 1000. It
reaches 14 sec for database without any load when default_statistic_target
=
10 000.
I wonder:
1) Is such behavior considered normal? If it is, where I can get
documentation regulating this?
It is normal that increasing default_statistic_target will increase
planning time. Increasing it to 14 seconds is undesirable, of course, but
without seeing the query it is hard to say more than that. This could be a
bug which leads to pathologically slow planning in some cases, or it could
be that your query is just a monster query that is difficult to plan.
2) Is there any way to limit querry plan creation time?
See the *_collapse_limit settings here,
https://www.postgresql.org/docs/9.5/static/runtime-config-query.html
And see the further sections referred to from there.
Cheers,
Jeff
Hi, Jeff!
Thank you for your response! Here is some additional information regarding
my first question. The querry itself and two plans(1:,2:)
2016-10-25 0:11 GMT+05:00 Jeff Janes <jeff.janes@gmail.com>:
Show quoted text
On Mon, Oct 24, 2016 at 6:04 AM, <ufaowl@gmail.com> wrote:
The following bug has been logged on the website:
Bug reference: 14393
Logged by: Vadim Akbashev
Email address: ufaowl@gmail.com
PostgreSQL version: 9.5.4
Operating system: Centos 6.8
Description:Hello!
I have two questions concerning choosing\creation time of querry plan
regaring default_statistic_target parameter. I noticed that querry plan
creation time increases when default_statistic_target is more than 1000.
It
reaches 14 sec for database without any load when
default_statistic_target =
10 000.
I wonder:
1) Is such behavior considered normal? If it is, where I can get
documentation regulating this?It is normal that increasing default_statistic_target will increase
planning time. Increasing it to 14 seconds is undesirable, of course, but
without seeing the query it is hard to say more than that. This could be a
bug which leads to pathologically slow planning in some cases, or it could
be that your query is just a monster query that is difficult to plan.2) Is there any way to limit querry plan creation time?
See the *_collapse_limit settings here, https://www.postgresql.org/
docs/9.5/static/runtime-config-query.htmlAnd see the further sections referred to from there.
Cheers,
Jeff