performance - Reason why Odoo being slow when there is huge data inside the database -

we have observed 1 problem in postgresql doesn't uses multi core of cpu single query. example, have 8 cores in cpu. having 40 million entries in stock.move table. when apply massive query in single database connection generate reporting & observe @ backend side, see 1 core 100% used, other 7 free. due query execution time takes longer , our odoo system being slow. whereas problem inside postgresql core. if anyhow can share query between 2 or more cores can performance boost in postgresql query execution.

i sure solving parallel query execution, can make odoo performance faster. has kind of suggestions regarding ??

----------- * editing question show answer postgresql core committee *---------

here posting answer got 1 of top contributor of postgresql database. ( hope information useful)

hello hiren,

it expected behave. postgresql doesn't support parallel cpu single query. topic under high development, , probably, feature in planned release 9.6 ~ september 2016. table 40m rows isn't big, more cpu should not (there overhead start , processing multi cpu query). have use usual tricks materialized view, preagregations, ... main idea of these tricks - don't try repeat same calculation. check health of postgresql - indexes, vacuum processing, statistics,.. check hw - speed of io. check postgresql configuration - shared_buffers, work_mem. queries can slow due bad estimations - check explain of slow queries. there tools can breaks query more queries , start parallel execution, didn't use it. https://launchpad.net/stado http://www.pgpool.net/docs/latest/tutorial-en.html#parallel

regards pavel stehule

well, think have answer there -- postgresql not support parallel query yet. general advice towards performance apt, , might consider partitioning, might allow truncate partitions instead of deleting parts of table, or increasing memory allocation. it's impossible give advice on without knowing more query.

having had experience sort of issue on non-parallel query oracle systems, suggest consider hardware you're using.

the modern trend towards cpus many cores great web servers or other multi-process systems many short-lived transactions, have data processing system few, large transactions. need correct hardware support that. cpus fewer, more powerful cores better choice, , have pay attention bandwidth memory , storage.

this why engineered systems have been popular big data , data warehousing.