Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #14393: Querry plan creation time is too long

BUG #14393: Querry plan creation time is too long

From
ufaowl@gmail.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM5MwpMb2dnZWQgYnk6ICAg
ICAgICAgIFZhZGltIEFrYmFzaGV2CkVtYWlsIGFkZHJlc3M6ICAgICAgdWZh
b3dsQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS40Ck9wZXJh
dGluZyBzeXN0ZW06ICAgQ2VudG9zIDYuOApEZXNjcmlwdGlvbjogICAgICAg
IAoKSGVsbG8hDQpJIGhhdmUgdHdvIHF1ZXN0aW9ucyBjb25jZXJuaW5nIGNo
b29zaW5nXGNyZWF0aW9uIHRpbWUgb2YgcXVlcnJ5IHBsYW4KcmVnYXJpbmcg
ZGVmYXVsdF9zdGF0aXN0aWNfdGFyZ2V0IHBhcmFtZXRlci4gSSBub3RpY2Vk
IHRoYXQgcXVlcnJ5IHBsYW4KY3JlYXRpb24gdGltZSBpbmNyZWFzZXMgd2hl
biBkZWZhdWx0X3N0YXRpc3RpY190YXJnZXQgaXMgbW9yZSB0aGFuIDEwMDAu
IEl0CnJlYWNoZXMgMTQgc2VjIGZvciBkYXRhYmFzZSB3aXRob3V0IGFueSBs
b2FkIHdoZW4gZGVmYXVsdF9zdGF0aXN0aWNfdGFyZ2V0ID0KMTAgMDAwLg0K
SSB3b25kZXI6DQoxKSBJcyBzdWNoIGJlaGF2aW9yIGNvbnNpZGVyZWQgbm9y
bWFsPyBJZiBpdCBpcywgd2hlcmUgSSBjYW4gZ2V0CmRvY3VtZW50YXRpb24g
cmVndWxhdGluZyB0aGlzPw0KMikgSXMgdGhlcmUgYW55IHdheSB0byBsaW1p
dCBxdWVycnkgcGxhbiBjcmVhdGlvbiB0aW1lPw0KVGhhbmsgeW91IQ0KDQpQ
LlMuIEhlcmUgYXJlIHNvbWUgZXhhbXBsZXM6DQoxOg0Kc3RhdGlzdGljX3Rh
cmdldD01MDANCnF1ZXJyeSBwbGFuIGNyZWF0aW9uIHRpbWUgPSA0NjRtcw0K
cGdfc3RhdGlzdGljcyBzaXplID0gOTI4IGtiLCAxOTEyIGtiIHRvYXN0LCA3
MiBrYiBpbmRleA0KMjoNCnN0YXRpc3RpY190YXJnZXQgPSAxMDAwMA0KcXVl
cnJ5IHBsYW4gY3JlYXRpb24gdGltZSA9IDE0LDMgc2VjDQpwZ19zdGF0aXN0
aWNzIHNpemUgPSA5Mjgga2IsIDEwIE1CIHRvYXN0LCA3MiBrYiBpbmRleAoK

Re: BUG #14393: Querry plan creation time is too long

From
Jeff Janes
Date:
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

Re: BUG #14393: Querry plan creation time is too long

From
Вадим Акбашев
Date:
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>:
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?


And see the further sections referred to from there.

Cheers,

Jeff

Attachment