Re: Query Plan Performance on Partitioned Table

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 # min 128kB
work_mem = 8MB # min 64kB
maintenance_work_mem = 20GB # min 1MB

We usually have around 400 active connections on the db. Most of them are
idle. There are about 100 connections are in active status and I can see
most of the time they are in 'BIND' status in ps command.

We have heavy IO load on the disk of the default tablespace where I believe
table statistics tables are in. Will that impact the query planing greatly?

2015-08-12 15:00 GMT+08:00 Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com>:

> 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. Btw, dozens of millions is not a problem on modern systems. I
> remember of reading about a recommended 20 millions per partition but I
> usually work with 60 millions per partition without any problem.
>
> Do you autovacuum? How frequently do the updates and insert operations
> occur?
> Give us your configuration about work_mem, shared_buffers, max_connections
> etc. Kernel version? If possible avoid 3.2 and 3.8-3.13. Also think to
> upgrade your OS version.
>
> From today I'm on vacancy, so others could help :)
>
> Pietro Pugni
> Il 12/ago/2015 03:49, "Rural Hunter" <ruralhunter(at)gmail(dot)com> ha scritto:
>
>> 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 <pietro(dot)pugni(at)gmail(dot)com>:
>>
>>> 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" <ruralhunter(at)gmail(dot)com> 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 plan
>>>> is very quick.
>>>>
>>>> 2015-08-11 22:42 GMT+08:00 Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>:
>>>>
>>>>>
>>>>>
>>>>> 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 the same general
>>>>> idea of partition check (cid=something)?
>>>>>
>>>>>
>>>>> Maxim Boguk
>>>>> Senior Postgresql DBA
>>>>> http://www.postgresql-consulting.ru/
>>>>> <http://www.postgresql-consulting.com/>
>>>>>
>>>>> Phone RU: +7 910 405 4718
>>>>> Phone AU: +61 45 218 5678
>>>>>
>>>>> LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
>>>>> Skype: maxim.boguk
>>>>> Jabber: maxim(dot)boguk(at)gmail(dot)com
>>>>> МойКруг: http://mboguk.moikrug.ru/
>>>>>
>>>>> "People problems are solved with people.
>>>>> If people cannot solve the problem, try technology.
>>>>> People will then wish they'd listened at the first stage."
>>>>>
>>>>>
>>>>
>>