Re: Query Plan Performance on Partitioned Table

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