Odoo performance with 8, 9, 10 & 11 and PostgreSQL 9.6
With the arrival of PostgreSQL 9,6, Odoo performance can now benefit from parallel query functions. From the PostgreSQL side, this is excellent news because this means many SELECT queries operated by the Odoo ORM can be run faster than they used to be. On the paper, the advantages are:
- Faster query results (PostgreSQL claim up to 32x faster than on previous versions such as 9.5)
- Improvement in GIN, BRIN and B-tree Indexing modes.
- Better Row compression
Yet, when one looks at Odoo performance, one must understand that Odoo does not have capacity to use these very efficiently since the above imply two major factors which are absent from Odoo:
- An evolved indexing strategy oriented in type according to the contents (Yet, Odoo only handles B-tree as a standard)
- Capacity to optimize Indexing parsing (Must be done from the PostgreSQL side & is not accessible from the Odoo side)
All in all, changing previous versions of PostgreSQL to migrate to 9.6 without making proper adjustments to your database engine and your data models will give no sensible improvement in terms of performance and query / response time.
You can check Index relevance and efficiency from the PSQL side by executing the following query:
SELECT schemaname, relname, seq_scan, idx_scan, cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS idx_scan_pct FROM pg_stat_user_tables WHERE (idx_scan + seq_scan)>0 ORDER BY idx_scan_pct;
This helps pitching the efficiency of Indexing compared to sequential read. Of course, the idea is to reduce sequential scans as much as you can since they are more expensive and generate more table locks / memory / CPU consumption.