Skip to content

Instantly share code, notes, and snippets.

@florentx
Created April 16, 2014 13:28
Show Gist options
  • Save florentx/10875212 to your computer and use it in GitHub Desktop.
Save florentx/10875212 to your computer and use it in GitHub Desktop.
OpenERP - create missing indexes on Foreign Keys
# -*- coding: utf-8 -*-
from __future__ import absolute_import
import contextlib
__all__ = ['savepoint', 'create_missing_indexes']
@contextlib.contextmanager
def savepoint(cr, name, quiet=False):
# http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
if '"' in name:
name = name.replace('"', '""')
cr.execute('SAVEPOINT "%s";' % name)
try:
yield
except Exception:
cr.execute('ROLLBACK TO "%s";' % name)
if not quiet:
raise
finally:
cr.execute('RELEASE "%s";' % name)
def create_missing_indexes(cr):
"""Create indexes for all the foreign keys (m2o, o2m, ...)."""
# List the missing indexes:
# * ignore the FK on "res_users" (create_uid, write_uid, ...)
# * skip the relations having less than 2 entries in the source or
# destination table
cr.execute("""
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]);
""")
statistics = {'done': [], 'errors': []}
for tbl, col in cr.fetchall():
try:
with savepoint(cr, 'create_index'):
cr.execute('CREATE INDEX "%(tbl)s_%(col)s_fki" '
'ON "%(tbl)s" ("%(col)s")' % {'tbl': tbl, 'col': col})
statistics['done'].append((tbl, col))
except Exception:
statistics['errors'].append((tbl, col))
return statistics
@agb80
Copy link

agb80 commented Jun 12, 2014

Would you mind share on what would be the correct way to use this snipet please?

Copy link

ghost commented Jul 30, 2014

I ran the first SQL, dumped the results into a CSV. Open in Excel and add a new column prior to the table and column - ="CREATE INDEX " & C1 & "_" &D1 &" ON " &C1&" (" &D1 &"); " - then paste the first column contents into a PSQL console.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment