From a0af70cfb4b7ab179014050ce026ab054025a65e Mon Sep 17 00:00:00 2001 From: Tom Hughes Date: Mon, 14 Jun 2010 12:43:39 +0100 Subject: [PATCH] Monkey patch rails to make system table query efficient Rails tries to query the system tables to find the primary key and it's controlling sequence, but it doesn't specify that it is looking for objects of class "pg_class" so the pg_depend table has to be sequentially scanned instead of being accessed using the index. Tests on the production database show that the time taken for this query drops from 3.5s to 15ms if the index is used. --- config/initializers/postgresql_adapter.rb | 34 +++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 config/initializers/postgresql_adapter.rb diff --git a/config/initializers/postgresql_adapter.rb b/config/initializers/postgresql_adapter.rb new file mode 100644 index 000000000..0420a7495 --- /dev/null +++ b/config/initializers/postgresql_adapter.rb @@ -0,0 +1,34 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter + alias_method :old_pk_and_sequence_for, :pk_and_sequence_for + + def pk_and_sequence_for(table) + result = query(<<-end_sql, 'PK and serial sequence')[0] + SELECT attr.attname, seq.relname + FROM pg_class seq, + pg_attribute attr, + pg_depend dep, + pg_namespace name, + pg_constraint cons + WHERE seq.oid = dep.objid + AND seq.relkind = 'S' + AND attr.attrelid = dep.refobjid + AND attr.attnum = dep.refobjsubid + AND attr.attrelid = cons.conrelid + AND attr.attnum = cons.conkey[1] + AND cons.contype = 'p' + AND dep.classid = '"pg_class"'::regclass + AND dep.refclassid = '"pg_class"'::regclass + AND dep.refobjid = '#{quote_table_name(table)}'::regclass + end_sql + + if result.nil? or result.empty? + old_pk_and_sequence_for(table) + else + [result.first, result.last] + end + end + end + end +end -- 2.39.5