Re: Query Plan Performance on Partitioned Table

On Tue, Aug 11, 2015 at 6:46 PM, Rural Hunter <ruralhunter(at)gmail(dot)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/>