From: | Rural Hunter <ruralhunter(at)gmail(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Query Plan Performance on Partitioned Table |
Date: | 2015-08-11 08:46:30 |
Message-ID: | CAOe1oo_hMfPvTQ1EwvyMnS4fMDDh8q-z2-gLCqxi-pnprUqcKg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Maxim Boguk | 2015-08-11 11:43:41 | Re: Query Plan Performance on Partitioned Table |
Previous Message | David Rowley | 2015-08-10 03:16:16 | Re: Slow HashAggregate/cache access |