Re: Query Plan Performance on Partitioned Table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



# \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.boguk@xxxxxxxxx>:


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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux