1. Mailing Lists
  2. Contributors
  3. RE: Postgres Optimisation

Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Re: Postgres Optimisation

Re: Postgres Optimisation

Avatar

RE: Postgres Optimisation

by
Bista Solutions, Ray Carnes
- 19/11/2015 19:40:40

The only thing I’ve ever needed to do specific to Odoo was to create indices on some tables where they are missing.  A major culprit is Community modules where indexing isn’t part of the module implementation.

 

I run this SQL to work out which tables are being joined to by foreign keys that aren’t indexed:

 

SELECT conrelid::regclass, attname

      FROM pg_constraint

      JOIN pg_class c ON (c.oid = conrelid)

      JOIN pg_class fc ON (fc.oid = confrelid)

      JOIN pg_attribute ON (attrelid = conrelid AND attnum = ANY(conkey))

     WHERE contype = 'f'

       AND confrelid != 'res_users'::regclass

       AND c.reltuples > 1 AND fc.reltuples > 1

       AND NOT EXISTS (

      SELECT 1 FROM pg_index

       WHERE indrelid = conrelid AND indkey[0] = conkey[1]);

 

 

It comes from this post https://gist.github.com/florentx/10875212

 

On a v9 system with all App installed, this SQL returns the following missing indices:

 

ir_model_fields                    | serialization_field_id

ir_module_module_dependency        | module_id

ir_ui_view                         | model_data_id

ir_act_report_xml                  | ir_values_id

ir_act_window                      | search_view_id

ir_act_window                      | view_id

ir_values                          | model_id

res_country                        | currency_id

res_country_state                  | country_id

res_partner                        | commercial_partner_id

res_partner                        | country_id

res_partner                        | state_id

res_users                          | partner_id

account_financial_html_report_line | parent_id

purchase_order_line                | partner_id

purchase_order_line                | currency_id

 

This is a LOT less than the last time I ran this, back in v8, where it would return almost 100 tables.

 

Ray.

 

From: David Arnold [mailto:dar@devco.co]
Sent: Wednesday, November 18, 2015 8:53 PM
To: Contributors <contributors@odoo-community.org>
Subject: Postgres Optimisation

 

Hi

 

is anyone interested in an open discussion about PSQL optimisation?

 

Knowledge about PostgreSQL Configuration and Deployment is mostly privately held or simply very scars in the ecosystem. Many of us go probably with the defaults in their daily operations. This however is probably perceived as an uncomfortable black box and one would wish to have more knowledge dispersal in the odoo world to raise the overall deployment quality and establish best practices.

 

Here is an invitation to join this ad-hoc initiative to do something about this and gather, collect and consolidate the available odoo-specific knowledge. I chose and suggest the following approach:

 

The 212 config options of postgres9.4 are clustered into 34 (sub)categories, which I copied here for convenience. A comprehensive list of options you can find here: https://goo.gl/DPfDwK

 

If you A) know something about any of those categories, please consider replying to this email with a odoo-specific short description, like so:

"category X can be used to do Y. It is noteworthy altering some default values in the context of odoo deployment because Z"

 

AND/XOR

 

If you have B) some odoo-contextualized knowledge about a specific parameter (of the 212, see linked spreadsheet) please feel free to comment in the sheet iteself.

 

Throughout gathering phase I would consolidate Knowledge in the excel sheet and once, ideally, gathered sufficient Input, I would then write up a shared authorship blog post about the topic. 

 

Some hints and general odoo optimizaiton stuff can be found here: http://de.slideshare.net/openobject/performance2014-35689113

PostgreSQL guides:

 

Best Regards and thanks for your interest

 

David

 

Autovacuum

Version and Platform Compatibility / Previous PostgreSQL Versions

Client Connection Defaults / Locale and Formatting

Client Connection Defaults / Other Defaults

Client Connection Defaults / Shared Library Preloading

Client Connection Defaults / Statement Behavior

File Locations

Connections and Authentication / Connection Settings

Connections and Authentication / Security and Authentication

Error Handling

Lock Management

Query Tuning / Genetic Query Optimizer

Query Tuning / Other Planner Options

Query Tuning / Planner Cost Constants

Query Tuning / Planner Method Configuration

Replication / Master Server

Replication / Sending Servers

Replication / Standby Servers

Reporting and Logging / What to Log

Reporting and Logging / When to Log

Reporting and Logging / Where to Log

Resource Usage / Asynchronous Behavior

Resource Usage / Background Writer

Statistics / Query and Index Statistics Collector

Resource Usage / Cost-Based Vacuum Delay

Resource Usage / Disk

Resource Usage / Kernel Resources

Resource Usage / Memory

Developer Options

Version and Platform Compatibility / Other Platforms and Clients

Statistics / Monitoring

Write-Ahead Log / Archiving

Write-Ahead Log / Checkpoints

Write-Ahead Log / Settings

Reference