RFC : adding extra indexes on database (purchase / stock)

[legalsylvain]

Hi all,

I am facing problems of increasing slowness on my Odoo instance.
For example, the read of a Purchase order of 3 lines currently takes several seconds.
On closer inspection, I observed that the slowness was in the read of purchase.order.line, and more precisely on a query.
By adding an index to the database, the execution time goes from 4373.627 ms to 0.186 ms. (x23510)
CREATE INDEX stock_move_created_purchase_line_id_index on stock_move (created_purchase_line_id);

In the purchase and stock management flow (display, validation, etc...), I found 2 other indexes that greatly improve the performance.

CREATE INDEX stock_picking_group_id_index on stock_picking (group_id);
CREATE INDEX stock_move_line_picking_id_index on stock_move_line (picking_id);

2 questions:

How to implement it

A) By a patch on odoo/odoo, adding index=True on the original definition of the field.
B) by a custom module, overloading the field. Something like :

class StockMoveLine(models.Model):
    _name = "stock.move.line"
    picking_id = fields.Many2one(index=True)

C) directly by an SQL request.

Do you know which solution is recommended? The one that is the most maintainable over time, during Openupgrade updates?

Bad Side effect

Do you see any disadvantages to add these indexes? (except the size of the index on the hard disk). Are there any points on which I must be careful?

Technical context :

• Odoo CE Version 12.0
• stock_move : 20M records.
• stock_picking : 4M records.
• purchase_order_line : 2M records.

[rvalyi]

Hello, I vote for B) (this is what we used so far) and also bare in mind that adding index also tend to make data insertion slower, so it means we should ensure it has a positive effect case by case (we cannot just add indexes everywhere blindly).

Now eventually Odoo missed some generally useful indexes and eventually we could have some ways to share our index tweaks on large databases. In general things got better with newer versions, so not sure how much such an initiative would meet enough audience though.

[gdgellatly]

B works and I think is best, but eventually it is not enough. Also note that Odoo added that index in later versions.

Yes insertion speed slows, but in truth should not be a major consideration given the Read/Write balance in most ERP's. Increasingly you need more powerful indexing. e.g. composite/partial/trigram.

A good example of where a partial is needed is in sparsely populated columns of huge tables e.g. "account_move_reversed_entry_id_idx" btree (reversed_entry_id) WHERE reversed_entry_id IS NOT NULL

This an Odoo created composite which they do in the init, which I think should be the practice for us

    def init(self):
        super().init()
        self.env.cr.execute("""
            CREATE INDEX IF NOT EXISTS account_move_to_check_idx
            ON account_move(journal_id) WHERE to_check = true;
            CREATE INDEX IF NOT EXISTS account_move_payment_idx
            ON account_move(journal_id, state, payment_state, move_type, date);
        """)

[pedrobaeza]

Note that adding indexes affects also most of the UPDATE operations, as in practice, they are like a DELETE + INSERT, needing to create all the indexes of the updated row. A bit of info in https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/

[gdgellatly]

When we measured, even heavily indexed tables you are talking less than 10% on an already fast operation for INSERTS. And to get HOT benefits, that requires per table tuning. Besides, I really doubt in a default Odoo install, there is any regularly updated, sensible to index field not indexed anymore (not to mention a few hundred nonsensical ones). Mostly it is these sparsely populated fields we find, or particular implementation specific user use cases with reporting, grouping etc. I just feel that in the Odoo context, which is very read heavy, the performance thing for indexes is a bit overblown. If you need an index for a regular, slow user impacting query, add it. For us 30ms is our cutoff for regular SELECTS initiated by users.

60%+ of Odoo default indexes are typically unused anyway, so maybe a better discussion is removing useless indexes :), but for me the biggest threat in adding indexes is screwing up read performance by having the planner choose your new index, instead of another on a better column for execution. Selection fields and company_id fields are good ones for that as you get uneven distributions and we almost always end up going a partial or composite for those cases.

But going really offtopic, you get bad index choice on required fields like company_id as it (unnecessarily) adds OR NULL. Or else adding an Active column, that is one you really need to think about if performance matters.

[yajo]

The problem in Odoo is that you must explicitly indicate index=True for many2one fields. However, for that kind of fields, index=False is almost always a bad idea (I say "almost" for accuracy, but I really mean always 😆).

Long ago I opened odoo/odoo#39332 which should fix this and many other bottlenecks I encountered myself here and there. Of course it fell into oblivion, like any other PR ever to odoo master. Maybe you can try lobbing there a bit.

The more specific solution for your case I would try is A. You will need to send an OPW to Odoo before opening the PR so you get more chances of success.

[rousseldenis]

I would say, we usually try to do the fix for Odoo core. With good metrics in the PR and usually on early state of new versions, Odoo agrees to integrate them.

For the index you talk about, we have included the same one (in a separate branch - as we were tough on the project).

[legalsylvain]

Hi. Thanks a lot for all your interesting answers !

When I was talking about A, it was about a patch in my gitaggregate file. not a PR against odoo/odoo :

• I do not feel expert enough to propose solutions in the core
• I always work on old version (I have planned a migration to V16 in 2024...) the odoo team is still very reluctant to merge in the core, PRs on "stable" or even "deprecated" versions.

As a first step, i started to implement solution B. the advantages i see are :

• it allows to identify very clearly which indexes have been created.
• when I migrate to V16, it will generate a todo list of indexes that I should check. (maybe it will be obsolete).
• If I have a performance problem, I can simply uninstall the module on my test server, and see if the problem comes from the creation of a custom index. (regarding @gdgellatly said about the possibility that adding a custom index disrupts the previous execution plan.)

you can see the new grap_index module here, grap/grap-odoo-custom#307.
Any feedback on the implementation is welcome.

Finally, it seems that there are very opposite point of views, regarding the idea to set an index in many fields, or did I missed something ?

@gdgellatly said :

60%+ of Odoo default indexes are typically unused anyway, so maybe a better discussion is removing useless indexes :)

@yajo said :

However, for that kind of fields, index=False is almost always a bad idea

[yajo]

Any many2one field index will be used massively at some point. That's by default how the ORM works, and also how Postgres itself works. For example, if the field is ondelete="cascade", even if you do a DELETE FROM table WHERE id=1 directly in Postgres, it will do a search to cascade that deletion. And the search will be crazy slow if you have lots of records and no index. Probably @gdgellatly was referring to other kind of indexes.

[gdgellatly]

My favorite trick for index testing is just test it in psql using pg_stat_statements output against the query to fix, and the top 5 or so queries from that table that could be affected. To test,

BEGIN;
CREATE INDEX ...............
EXPLAIN ANALYZE ............
ROLLBACK;

Rinse repeat. Index is never actually committed to postgres as inside transaction.

[gdgellatly]

@yajo For large tables I'd tend to agree that M2O Required should generally be indexed or at least if less likely to be NULL. But take something like reveresed_entry_id, it is an M2O that IMO should always be a partial index or not at all. It is typically less than 1% filled. Or moderator_id of mail_message or thread, I can't remember, that is a huge never used index in most installs. Not all M2O are heavily used.

[gdgellatly]

@legalsylvain Do not mistake me, I am a very big believer in indexes as the solution to many issues, especially composites that can return common queries without accessing the table.

The useless indexes are mostly small tables where it will never get used, e.g. res_company because seq scan is faster. They don't cost much. The other is BTREE indexes on many text/char fields. Those can get big and are often never really used for equality searches, just LIKE/ILIKE (which don't work on BTREE).

[codeagencybe]

Would a 3rd party db cache like eg redis make a huge difference here?
Not saying it would solve a bad query obviously, but with many data and large tables/instances it also comes to checking in which ways we can remove some db stress where it's not necessary.

Redis won't solve anything for deletes or updates, but it can speed up reads.
If an index can negatively impact updates, then maybe applying a db cache is another smart alternative?