On Tue, Aug 11, 2015 at 6:46 PM, Rural Hunter <ruralhun...@gmail.com> wrote:
> Platform: pg 9.2.9 on Ubuntu 12.04.4 LTS. > I have a table which is partitioned to about 80 children. There are usualy > several dozens of connections accessing these tables concurrently. I found > sometimes the query planing time is very long if I query against the parent > table with partition key. The connections are shown with status 'BIND' by > ps command. > > In normal condition, the plan time of the query is about several hundred > of million seconds while the same query accessing child table directly is > less than 1 million seconds: > # explain select 1 from article where cid=729 and > url_hash='6851f596f55a994b2df417b53523fe45'; > QUERY > PLAN > > ------------------------------------------------------------------------------------------------------------ > Result (cost=0.00..8.68 rows=2 width=0) > -> Append (cost=0.00..8.68 rows=2 width=0) > -> Seq Scan on article (cost=0.00..0.00 rows=1 width=0) > Filter: ((cid = 729) AND (url_hash = > '6851f596f55a994b2df417b53523fe45'::bpchar)) > -> Index Scan using article_729_url_hash on > > article_729 article (cost=0.00..8.68 rows=1 width=0) > Index Cond: (url_hash = > '6851f596f55a994b2df417b53523fe45'::bpchar) > Filter: (cid = 729) > (7 rows) > > Time: 361.401 ms > > # explain select 1 from article_729 where > url_hash='6851f596f55a994b2df417b53523fe45'; > QUERY > PLAN > > --------------------------------------------------------------------------------------------- > Index Only Scan using article_729_url_hash on article_729 > (cost=0.00..8.67 rows=1 width=0) > Index Cond: (url_hash = '6851f596f55a994b2df417b53523fe45'::bpchar) > (2 rows) > > Time: 0.898 ms > > This is only in normal condition. In extreme condition, the planing time > could take several minutes. There seems some locking issue in query > planing. How can I increase the plan performance? Or is it bad to partition > table to 80 children in PostgreSQL? > > Hi, Could you provide full definition of article_729 table (\dt+ article_729)? 80 partitions is adequate amount of partitions for the PostgreSQL, so there are going something unusual (I suspect it may be related to used partitioning schema). -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/>
