Database indexes for foreign keys

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Database indexes for foreign keys

Alexandre Fayolle - camptocamp
Hello,

I came across this while analyzing pg logs with pgbadger : postgreSQL
does not automatically create indexes for foreign key columns, and I was
surprised when I noticed that the ORM in OpenERP does not do this by
default. Has anyone been doing some analysis on this and concluded that
manual creation of such indexes was the correct thing to do because the
read-time gains were nullified by the write-time costs ?

My gut feeling is that in most cases we want such index to be created,
and that select should default to True for m2o fields (and then we could
always explicitely disable index creation for the few fields for which
this would cause harm).

--
Alexandre Fayolle
Chef de Projet
Tel : + 33 (0)4 79 26 57 94

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex
http://www.camptocamp.com
F


_______________________________________________
Mailing list: https://launchpad.net/~openerp-expert-framework
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-expert-framework
More help   : https://help.launchpad.net/ListHelp
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database indexes for foreign keys

Florent
Alexandre,

Actually, we did the same analysis some time ago when looking for
easy-wins on our project, and I've added a small script which is
called after each OpenERP refresh ( >>> client.upgrade("base") ) to
check if some index is missing.
We've seen big improvements on database usage, especially when
deleting entries in heavy tables having foreign keys.

https://gist.github.com/florentx/10875212


--
Florent

2014-04-16 15:23 GMT+02:00 Alexandre Fayolle <[hidden email]>:

> Hello,
>
> I came across this while analyzing pg logs with pgbadger : postgreSQL
> does not automatically create indexes for foreign key columns, and I was
> surprised when I noticed that the ORM in OpenERP does not do this by
> default. Has anyone been doing some analysis on this and concluded that
> manual creation of such indexes was the correct thing to do because the
> read-time gains were nullified by the write-time costs ?
>
> My gut feeling is that in most cases we want such index to be created,
> and that select should default to True for m2o fields (and then we could
> always explicitely disable index creation for the few fields for which
> this would cause harm).
>
> --
> Alexandre Fayolle
> Chef de Projet
> Tel : + 33 (0)4 79 26 57 94
>
> Camptocamp France SAS
> Savoie Technolac, BP 352
> 73377 Le Bourget du Lac Cedex
> http://www.camptocamp.com
> F
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~openerp-expert-framework
> Post to     : [hidden email]
> Unsubscribe : https://launchpad.net/~openerp-expert-framework
> More help   : https://help.launchpad.net/ListHelp

_______________________________________________
Mailing list: https://launchpad.net/~openerp-expert-framework
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-expert-framework
More help   : https://help.launchpad.net/ListHelp
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database indexes for foreign keys

Alexandre Fayolle - camptocamp
On mer. 16 avril 2014 15:34:23 CEST, Florent wrote:

> Alexandre,
>
> Actually, we did the same analysis some time ago when looking for
> easy-wins on our project, and I've added a small script which is
> called after each OpenERP refresh ( >>> client.upgrade("base") ) to
> check if some index is missing.
> We've seen big improvements on database usage, especially when
> deleting entries in heavy tables having foreign keys.
>
> https://gist.github.com/florentx/10875212
>
>

If this is so, maybe this behaviour could be considered as part of v8 ?

I'm also wondering about having hash indexes on selection fields.

--
Alexandre Fayolle
Chef de Projet
Tel : + 33 (0)4 79 26 57 94

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex
http://www.camptocamp.com


_______________________________________________
Mailing list: https://launchpad.net/~openerp-expert-framework
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-expert-framework
More help   : https://help.launchpad.net/ListHelp
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database indexes for foreign keys

Lorenzo Battistini
In reply to this post by Alexandre Fayolle - camptocamp
On 04/16/2014 03:23 PM, Alexandre Fayolle wrote:
> Hello,
>
> I came across this while analyzing pg logs with pgbadger : postgreSQL
> does not automatically create indexes for foreign key columns, and I was
> surprised when I noticed that the ORM in OpenERP does not do this by
> default. Has anyone been doing some analysis on this and concluded that
> manual creation of such indexes was the correct thing to do because the
> read-time gains were nullified by the write-time costs ?

Sometimes I had to manually create indexes for foreign keys, especially
when pg logs reported very slow queries.
This increased the performances.


>
> My gut feeling is that in most cases we want such index to be created,
> and that select should default to True for m2o fields (and then we could
> always explicitely disable index creation for the few fields for which
> this would cause harm).

In general, the queries based on foreign keys (like JOIN) are very
selective, so it is advised to have indexes on them.

So, +1


--
Lorenzo Battistini
Tel (CH): +41 91 210 23 40
Tel (IT): +39 011 198 25481
http://www.agilebg.com


_______________________________________________
Mailing list: https://launchpad.net/~openerp-expert-framework
Post to     : [hidden email]
Unsubscribe : https://launchpad.net/~openerp-expert-framework
More help   : https://help.launchpad.net/ListHelp
Loading...