|From:||Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>|
|To:||Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru>, pgsql-hackers(at)postgresql(dot)org|
|Subject:||Re: Declarative partitioning optimization for large amount of partitions|
|Views:||Raw Message | Whole Thread | Download mbox|
On 2017/02/28 23:25, Aleksander Alekseev wrote:
> 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 .
Thanks for sharing.
> # 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 . A stacktrace for the first one
> looks like this :
> 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
> And here is a stacktrace for the second bottleneck :
> 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
> 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.
|Next Message||David Steele||2017-03-01 01:58:00||PATCH: Configurable file mode mask|
|Previous Message||Lukas Fittl||2017-03-01 01:01:29||[PATCH] Use $ parameters as replacement characters for pg_stat_statements|