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

Question

We have observed one problem in Postgresql as it doesn't uses multi core of CPU for single query. For example, I have 8 cores in cpu. We are having 40 Million entries in stock.move table. When we apply massive query in single database connection to generate reporting & observe at backend side, we see only one core is 100% used, where as all other 7 are free. Due to that query execution time takes so longer and our odoo system being slow. Whereas problem is inside postgresql core. If by anyhow we can share a query between two or more cores than we can get performance boost in postgresql query execution.

I am sure by solving parallel query execution, we can make Odoo performance even faster. Anyone has any kind of suggestions regarding this ??

----------- * Editing this question to show you answer from Postgresql Core committee *---------

Here I am posting the answer which I got from one of top contributor of Postgresql database. ( I hope this information will be useful)

Hello Hiren,

It is expected behave. PostgreSQL doesn't support parallel CPU for single query. This topic is under high development, and probably, this feature will be in planned release 9.6 ~ September 2016. But table with 40M rows isn't too big, so probably more CPU should not too help to you (there is some overhead with start and processing multi CPU query). You have to use some usual tricks like materialized view, preagregations, ... the main idea of these tricks - don't try to repeat often same calculation. Check health of PostgreSQL - indexes, vacuum processing, statistics,.. Check hw - speed of IO. Check PostgreSQL configuration - shared_buffers, work_mem. Some queries can be slow due bad estimations - check a explain of slow queries. There are some tools that can breaks some query to more queries and start parallel execution, but I didn't use it. https://launchpad.net/stado http://www.pgpool.net/docs/latest/tutorial-en.html#parallel

Regards Pavel Stehule

Answer 1

Well, I think you have your answer there -- PostgreSQL does not currently support parallel query yet. The general advice towards performance is very apt, and you might also consider partitioning, which might allow you to truncate partitions instead of deleting parts of a table, or increasing memory allocation. It's impossible to give good advice on that without knowing more about the query.

Having had experience with this sort of issue on non-parallel query Oracle systems, I suggest that you also consider what hardware you're using.

The modern trend towards CPUs with very many cores is a great help for web servers or other multi-process systems with many short-lived transactions, but you have a data processing system with few, large transactions. You need the correct hardware to support that. CPUs with fewer, more powerful cores are a better choice, and you have to pay attention to bandwidth to memory and storage.

This is why engineered systems have been popular with big data and data warehousing.