I tried to add index on partition key and it didn't help. we have
autovacuum running. The updates and inserts are very frequent on these
tables. The server kernel version is 3.5.0-22-generic. It has 376G memory.
max_connections = 2500# (change requires restart)
shared_buffers = 32GB
You can give it a try only on that partition just to see if your query plan
gets better. I prefer defining partitioning over ranging attributes like,
for example: cid between 123 and 456. It makes more sense, especially when
there are attributes which value strictly depends on the check attribute.
article_729 has about 0.8 million rows. The rows of the children tables are
variance from several thousands to dozens of millions. How can it help to
create index on the partition key?
2015-08-12 1:03 GMT+08:00 Pietro Pugni :
> Hi Rural Hunter,
> Try to create an index on cid attribute.
> How man
Hi Rural Hunter,
Try to create an index on cid attribute.
How many rows has article_729?
Pietro Pugni
Il 11/ago/2015 16:51, "Rural Hunter" ha scritto:
> yes i'm very sure. from what i observed, it has something to do with the
> concurrent query planing. if i disconnect other connections, the pla
yes i'm very sure. from what i observed, it has something to do with the
concurrent query planing. if i disconnect other connections, the plan is
very quick.
2015-08-11 22:42 GMT+08:00 Maxim Boguk :
>
>
> Check constraints:
>> "article_729_cid_check" CHECK (cid = 729)
>>
>
>
> Used partition
Check constraints:
> "article_729_cid_check" CHECK (cid = 729)
>
Used partition schema looks very simple and straightforward, and should
have no issues with 80 partitions.
Are you sure that you have only 80 partitions but not (lets say) 800?
Are every other partition of the article table use
# \d+ article_729
Table
"public.article_729"
Column|Type |
Modifiers | Storage | Stats target | Description
--+-+--
# \d article_729
Table "public.article_729"
Column|Type |
Modifiers
--+-+---
aid | bigint | not null defaul
On Tue, Aug 11, 2015 at 11:44 PM, Rural Hunter
wrote:
> # \dt+
>
> article_729
> List of relations
> Schema |Name | Type | Owner | Size | Description
> +-+---+++-
> public | article_729 | table | omuser1 |
# \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
On Tue, Aug 11, 2015 at 6:46 PM, Rural Hunter 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 lon
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 c
12 matches
Mail list logo
