Query Plan Performance on Partitioned Table

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?

Responses

Browse pgsql-performance by date

  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