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" <ruralhun...@gmail.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.pu...@gmail.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" <ruralhun...@gmail.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.bo...@gmail.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.bo...@gmail.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." >>>> >>>> >>> >
