Обсуждение: BUG #14393: Querry plan creation time is too long
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM5MwpMb2dnZWQgYnk6ICAg ICAgICAgIFZhZGltIEFrYmFzaGV2CkVtYWlsIGFkZHJlc3M6ICAgICAgdWZh b3dsQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS40Ck9wZXJh dGluZyBzeXN0ZW06ICAgQ2VudG9zIDYuOApEZXNjcmlwdGlvbjogICAgICAg IAoKSGVsbG8hDQpJIGhhdmUgdHdvIHF1ZXN0aW9ucyBjb25jZXJuaW5nIGNo b29zaW5nXGNyZWF0aW9uIHRpbWUgb2YgcXVlcnJ5IHBsYW4KcmVnYXJpbmcg ZGVmYXVsdF9zdGF0aXN0aWNfdGFyZ2V0IHBhcmFtZXRlci4gSSBub3RpY2Vk IHRoYXQgcXVlcnJ5IHBsYW4KY3JlYXRpb24gdGltZSBpbmNyZWFzZXMgd2hl biBkZWZhdWx0X3N0YXRpc3RpY190YXJnZXQgaXMgbW9yZSB0aGFuIDEwMDAu IEl0CnJlYWNoZXMgMTQgc2VjIGZvciBkYXRhYmFzZSB3aXRob3V0IGFueSBs b2FkIHdoZW4gZGVmYXVsdF9zdGF0aXN0aWNfdGFyZ2V0ID0KMTAgMDAwLg0K SSB3b25kZXI6DQoxKSBJcyBzdWNoIGJlaGF2aW9yIGNvbnNpZGVyZWQgbm9y bWFsPyBJZiBpdCBpcywgd2hlcmUgSSBjYW4gZ2V0CmRvY3VtZW50YXRpb24g cmVndWxhdGluZyB0aGlzPw0KMikgSXMgdGhlcmUgYW55IHdheSB0byBsaW1p dCBxdWVycnkgcGxhbiBjcmVhdGlvbiB0aW1lPw0KVGhhbmsgeW91IQ0KDQpQ LlMuIEhlcmUgYXJlIHNvbWUgZXhhbXBsZXM6DQoxOg0Kc3RhdGlzdGljX3Rh cmdldD01MDANCnF1ZXJyeSBwbGFuIGNyZWF0aW9uIHRpbWUgPSA0NjRtcw0K cGdfc3RhdGlzdGljcyBzaXplID0gOTI4IGtiLCAxOTEyIGtiIHRvYXN0LCA3 MiBrYiBpbmRleA0KMjoNCnN0YXRpc3RpY190YXJnZXQgPSAxMDAwMA0KcXVl cnJ5IHBsYW4gY3JlYXRpb24gdGltZSA9IDE0LDMgc2VjDQpwZ19zdGF0aXN0 aWNzIHNpemUgPSA5Mjgga2IsIDEwIE1CIHRvYXN0LCA3MiBrYiBpbmRleAoK
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>:
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