PostgreSQL 10.0 preview 性能增强 - 分区表子表元信息搜索性能增强

背景

PostgreSQL 10.0 增强了分区表的子表搜索性能，对于涉及分区表包含子表特别多的QUERY，可以提升性能。

性能分析

当分区非常多时，执行计划会变慢，原因是get_tabstat_entry, find_all_inheritors成为主要瓶颈。

10.0的改进

Improve performance of find_tabstat_entry()/get_tabstat_entry()

Patch introduces a hash map reloid -> PgStat_TableStatus which improves
performance in case of large number of tables/partitions.

  
Improve performance of find_all_inheritors()

Previous coding uses three nested loops which obviously were a pain for
large number of table's children. Patch replaces inner loop with
a hashmap.

讨论如下

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].  
  

-- init schema  
  
\timing on  
  
CREATE TABLE part_test (pk int not null, k int, v varchar(128)) PARTITION BY RANGE(pk);  
  
do $$  
declare  
    i integer;  
begin  
    for i in 1 .. 10000  
    loop  
        raise notice 'i = %', i;  
        execute ('CREATE TABLE part_test_' || i ||  
                 ' PARTITION OF part_test FOR VALUES FROM (' ||  
                 (1 + (i-1)*1000) || ') to (' || ( (i * 1000) + 1) || ');'  
                );  
    end loop;  
end $$;  
  
-- fill tables with some data  
  
do $$  
declare  
    i integer;  
begin  
    for i in 1 .. 100*1000  
    loop  
        raise notice 'i = %', i;  
        execute ('insert into part_test values ( ceil(random()*(10000-1)*1000), ceil(random()*10000*1000), '''' || ceil(random()*10000*1000) );');  
    end loop;  
end $$;  

  
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 didn't explored the second bottleneck closely yet but at first glance  
it doesn't look much more complicated.  
  
Please don't hesitate to share your thoughts regarding this matter.  
  
[1] http://afiskon.ru/s/e3/5f47af9102_benchmark.txt  
[2] http://afiskon.ru/s/00/2008c4ae66_temp.png  
[3] http://afiskon.ru/s/23/650f0afc89_stack.txt  
[4] http://afiskon.ru/s/03/a7e685a4db_stack2.txt  
  
--   
Best regards,  
Aleksander Alekseev  

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。

参考

https://commitfest.postgresql.org/13/1058/

https://www.postgresql.org/message-id/flat/20170228142509.GA19777@e733.localdomain#20170228142509.GA19777@e733.localdomain

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=090010f2ec9b1f9ac1124dc628b89586f911b641

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=827d6f977940952ebef4bd21fb0f97be4e20c0c4

digoal’s 大量PostgreSQL文章入口