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?