Re: Query Plan Performance on Partitioned Table

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."
>>>>
>>>>
>>>
>