Re: Query Plan Performance on Partitioned Table

# \dt+ article_729
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------+-------+--------+--------+-------------
public | article_729 | table | omuser1 | 655 MB |
(1 row)
The problem exists on not only this specific child table, but with all of
them.

2015-08-11 19:43 GMT+08:00 Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>:

>
>
> 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/>
>
>