Thread: BUG #14263: Query planner is slow to plan UPDATE on a table with many partitions
BUG #14263: Query planner is slow to plan UPDATE on a table with many partitions
From
shirshegsm@gmail.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI2MwpMb2dnZWQgYnk6ICAg ICAgICAgIExpbmFzIFZhbGl1a2FzCkVtYWlsIGFkZHJlc3M6ICAgICAgc2hp cnNoZWdzbUBnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjMuMTMK T3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgMTIuMDQuNSBMVFMKRGVzY3Jp cHRpb246ICAgICAgICAKCkhpIHRoZXJlLA0KDQpXZSBoYXZlIGEgcGFydGl0 aW9uZWQgdGFibGUsICJiaXRseV9jbGlja3NfdG90YWwiLCB3aXRoIDYwMCBw YXJ0aXRpb25zLCB1cAp0byAxbSByb3dzIGluIGVhY2ggc3ViLXRhYmxlLiBQ cm9wZXJ0eSBjb25zdHJhaW50X2V4Y2x1c2lvbiBpcwoncGFydGl0aW9uJy4N Cg0KU0VMRUNUcyBvbiBzYWlkIHRhYmxlIHdvcmsgYXMgZXhwZWN0ZWQsIGJ1 dCBVUERBVEVzIHNwZW5kIGEgbG90IG9mIHRpbWUgb24KcXVlcnkgcGxhbm5l ciBwaGFzZSwgZXZlbiB0aG91Z2ggZXhlY3V0aW5nIHRoZSBwbGFuIGFmdGVy d2FyZHMgaXMgdmVyeSBmYXN0LAppLmUuIHNlZSBodHRwczovL3Bhc3RlLmZl ZG9yYXByb2plY3Qub3JnLzM5Mzc1MC8xNjc5NzkxNC8uDQoNCkhlcmUncyBh IHdheSB0byByZXBsaWNhdGUgdGhlIGlzc3VlIG9uIGFuIGVtcHR5IHRhYmxl IHdpdGggNjAwIHBhcnRpdGlvbnMKdG9vLCBzbyBpdCBkb2Vzbid0IGRlcGVu ZCBvbiB0aGUgbnVtYmVyIG9mIHJvd3MgaW4gdGhlIHBhcnRpdGlvbmVkIHRh YmxlOgpodHRwczovL3Bhc3RlLmZlZG9yYXByb2plY3Qub3JnLzM5Mzc1MS85 MTY4MDI0MS8NCg0KQWZ0ZXIgc29tZSBkaXNjdXNzaW9uIG9uICNwb3N0Z3Jl c3FsIEAgRnJlZW5vZGUgKGFicmlkZ2VkIHZlcnNpb246Cmh0dHBzOi8vcGFz dGUuZmVkb3JhcHJvamVjdC5vcmcvMzkzNzUyLzE2ODA1OTE0LyksIGl0IHNl ZW1zIHRoYXQgdGhlIHF1ZXJ5CnBsYW5uZXIgaGFzIE8obl4yKSBjb21wbGV4 aXR5IGZvciB0aGUgbnVtYmVyIG9mIHBhcnRpdGlvbnMgaW4gYSBwYXJ0aXRp b25lZAp0YWJsZS4gVGh1cywgSSd2ZSBiZWVuIGFkdmlzZWQgdG8gc3BsaXQg dGhlIHRhYmxlIGludG8gbGVzcyBwYXJ0aXRpb25zLCBidXQKSSBmb3VuZCBp dCB1bmV4cGVjdGVkIHRoYXQgYSBzaGVlciBudW1iZXIgb2YgdGFibGVzIGNv dWxkIHNsb3cgZG93biBVUERBVEVzCmxpa2UgdGhhdCwgc28gSSB0aGluayB0 aGF0IHRoaXMgYmVoYXZpb3VyIGNvdWxkIGJlIGF0IHZlcnkgbGVhc3QgZG9j dW1lbnRlZAphY2NvcmRpbmdseS4NCg0KUmVnYXJkcywNCg0KTGluYXMNCgoK
Re: BUG #14263: Query planner is slow to plan UPDATE on a table with many partitions
From
Andrew Gierth
Date:
>>>>> "shirshegsm" == shirshegsm <shirshegsm@gmail.com> writes: shirshegsm> After some discussion on #postgresql @ Freenode (abridged shirshegsm> version: https://paste.fedoraproject.org/393752/16805914/), shirshegsm> it seems that the query planner has O(n^2) complexity for shirshegsm> the number of partitions in a partitioned table. To sum up my part of that discussion: the repeated calls to adjust_appendrel_attrs cause an O(N^2) number of calls to palloc, as query_tree_mutator calls range_table_mutator which does two pallocs per RTE (one for the RTE and one for the listcell). Also none of those get freed, as far as I can tell, so the memory usage is O(N^2) too. -- Andrew (irc:RhodiumToad)