Hi, On 2017/02/28 23:25, Aleksander Alekseev wrote: > Hello. > > I decided to figure out whether current implementation of declarative > partitioning has any bottlenecks when there is a lot of partitions. Here > is what I did [1].
Thanks for sharing. > Then: > > ``` > # 2580 is some pk that exists > echo 'select * from part_test where pk = 2580;' > t.sql > pgbench -j 7 -c 7 -f t.sql -P 1 -T 300 eax > ``` > > `perf top` showed to bottlenecks [2]. A stacktrace for the first one > looks like this [3]: > > ``` > 0x00000000007a42e2 in get_tabstat_entry (rel_id=25696, isshared=0 '\000') at > pgstat.c:1689 > 1689 if (entry->t_id == rel_id) > #0 0x00000000007a42e2 in get_tabstat_entry (rel_id=25696, isshared=0 '\000') > at pgstat.c:1689 > #1 0x00000000007a4275 in pgstat_initstats (rel=0x7f4af3fd41f8) at > pgstat.c:1666 > #2 0x00000000004c7090 in relation_open (relationId=25696, lockmode=0) at > heapam.c:1137 > #3 0x00000000004c72c9 in heap_open (relationId=25696, lockmode=0) at > heapam.c:1291 > (skipped) > ``` > > And here is a stacktrace for the second bottleneck [4]: > > ``` > 0x0000000000584fb1 in find_all_inheritors (parentrelId=16393, lockmode=1, > numparents=0x0) at pg_inherits.c:199 > 199 forboth(lo, rels_list, li, rel_numparents) > #0 0x0000000000584fb1 in find_all_inheritors (parentrelId=16393, lockmode=1, > numparents=0x0) at pg_inherits.c:199 > #1 0x000000000077fc9f in expand_inherited_rtentry (root=0x1badcb8, > rte=0x1b630b8, rti=1) at prepunion.c:1408 > #2 0x000000000077fb67 in expand_inherited_tables (root=0x1badcb8) at > prepunion.c:1335 > #3 0x0000000000767526 in subquery_planner (glob=0x1b63cc0, parse=0x1b62fa0, > parent_root=0x0, hasRecursion=0 '\000', tuple_fraction=0) at planner.c:568 > (skipped) > ``` > > The first one could be easily fixed by introducing a hash table > (rel_id -> pgStatList entry). Perhaps hash table should be used only > after some threshold. Unless there are any objections I will send a > corresponding patch shortly. I have never thought about this one really. > I didn't explored the second bottleneck closely yet but at first glance > it doesn't look much more complicated. I don't know which way you're thinking of fixing this, but a planner patch to implement faster partition-pruning will have taken care of this, I think. As you may know, even declarative partitioned tables currently depend on constraint exclusion for partition-pruning and planner's current approach of handling inheritance requires to open all the child tables (partitions), whereas the new approach hopefully shouldn't need to do that. I am not sure if looking for a more localized fix for this would be worthwhile, although I may be wrong. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
