diff options
author | Jordan Lewis <jordanthelewis@gmail.com> | 2017-02-08 17:46:54 -0500 |
---|---|---|
committer | Jeremy Daer <jeremydaer@gmail.com> | 2017-02-09 14:26:25 -0700 |
commit | d6529af2954a67bd57fda45286fa9cfd0ff6b5ac (patch) | |
tree | d4fe2cf3b028310a83d924574aa8c2a293d40ef0 /activerecord/lib | |
parent | 2b4d145f31eb2edfbfc5b0c8c65efc07321d4378 (diff) | |
download | rails-d6529af2954a67bd57fda45286fa9cfd0ff6b5ac.tar.gz rails-d6529af2954a67bd57fda45286fa9cfd0ff6b5ac.tar.bz2 rails-d6529af2954a67bd57fda45286fa9cfd0ff6b5ac.zip |
Simplify and speed up Postgres query for primary_keys
primary_keys(table) needs to query various metadata tables in Postgres to
determine the primary key for the table. Previously, it did so using a
complex common table expression against pg_constraint and pg_attribute.
This patch simplifies the query by joining pg_index against pg_attribute
instead of going through pg_constraint. This avoids an expensive unnest,
window function query, and common table expression.
EXPLAINing these queries in Postgres against a database with a single
table with a composite primary key shows a 66% reduction in the plan and
execute latencies. This is significant during application startup time,
especially against very large schemas, where these queries would be even
slower and more numerous.
Closes #27949
Diffstat (limited to 'activerecord/lib')
-rw-r--r-- | activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb | 18 |
1 files changed, 8 insertions, 10 deletions
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb index a4b1723fc7..bd8aab526e 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -426,16 +426,14 @@ module ActiveRecord def primary_keys(table_name) # :nodoc: select_values(<<-SQL.strip_heredoc, "SCHEMA") - WITH pk_constraint AS ( - SELECT conrelid, unnest(conkey) AS connum FROM pg_constraint - WHERE contype = 'p' - AND conrelid = #{quote(quote_table_name(table_name))}::regclass - ), cons AS ( - SELECT conrelid, connum, row_number() OVER() AS rownum FROM pk_constraint - ) - SELECT attr.attname FROM pg_attribute attr - INNER JOIN cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.connum - ORDER BY cons.rownum + SELECT a.attname + FROM pg_index i + CROSS JOIN unnest(i.indkey) as k + JOIN pg_attribute a + ON a.attrelid = i.indrelid + AND a.attnum = k + WHERE i.indrelid = #{quote(quote_table_name(table_name))}::regclass + AND i.indisprimary SQL end |