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.

diff --git a/config/initializers/postgresql_adapter.rb b/config/initializers/postgresql_adapter.rb
new file mode 100644 (file)
index 0000000..0420a74
--- /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