Skip to content

Performance problem with postgresql adapter primary_key function #8414

Closed
mccuskk opened this Issue Dec 4, 2012 · 9 comments

5 participants

@mccuskk
mccuskk commented Dec 4, 2012

Sorry to bother, but could I run this pas you?

We have a database with a large number of schemas and tables (~400,000 rows in pg_depend, ~700,000 in pg_attribute). I noticed that rails controller actions were spending a lot of time in ActiveRecord relative to the amount of actual query time.

Upping the logging level on the database showed that the primary_key function was taking about 17ms per call.

A reading the postgresql documentation suggests that the following SQL should yield the same outcome i.e. no need to link with pg_depend and takes 0.350 ms per call

SELECT attr.attname
FROM pg_attribute attr
INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1]
WHERE cons.contype = 'p'
AND cons.conrelid = '#{quote_table_name(table)}'::regclass

I am using RAILS 3.2.9, and would appreciate any insights you may have.

Many Thanks

Kieran

@steveklabnik
Ruby on Rails member

This is not a rails bug.

If you want to execute that SQL, translate it to ActiveRelation, or use ActiveRecord::Base#execute.

@rafaelfranca rafaelfranca reopened this Dec 4, 2012
@steveklabnik
Ruby on Rails member

Ah! So sorry! I read the issue, and thought, "Oh, a performance issue where custom SQL needs to be run to make it be fast." My misunderstanding.

@mccuskk so sorry!

@kennyj
kennyj commented Dec 4, 2012

@mccuskk I think you are right !
I compared 2 queries with sample data (http://www.commandprompt.com/ppbook/booktown.sql).

With your fix.

# explain SELECT attr.attname FROM pg_attribute attr INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND cons.conrelid = 'books'::regclass;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..9.32 rows=1 width=64)
   ->  Seq Scan on pg_constraint cons  (cost=0.00..1.03 rows=1 width=36)
         Filter: ((conrelid = 522702::oid) AND (contype = 'p'::"char"))
   ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute attr  (cost=0.00..8.27 rows=1 width=70)
         Index Cond: ((attrelid = 522702::oid) AND (attnum = (cons.conkey)[1]))

Without your fix.

# explain SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND dep.refobjid = 'books'::regclass;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=131.72..131.73 rows=1 width=64)
   ->  Nested Loop  (cost=0.00..131.72 rows=1 width=64)
         Join Filter: (attr.attnum = cons.conkey[1])
         ->  Nested Loop  (cost=0.00..130.67 rows=1 width=74)
               ->  Seq Scan on pg_depend dep  (cost=0.00..122.39 rows=1 width=8)
                     Filter: (refobjid = 522702::oid)
               ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute attr  (cost=0.00..8.27 rows=1 width=70)
                     Index Cond: ((attrelid = 522702::oid) AND (attnum = dep.refobjsubid))
         ->  Seq Scan on pg_constraint cons  (cost=0.00..1.03 rows=1 width=36)
               Filter: ((conrelid = 522702::oid) AND (contype = 'p'::"char"))

BTW...

# select count(*) from pg_constraint;
 count
-------
    16
# select count(*) from pg_attribute;
 count
-------
  2698
# select count(*) from pg_depend;
 count
-------
  6140

Basically, I guess pg_depend has many records and your fix is right.

@kennyj
kennyj commented Dec 4, 2012

p.s. All tests are green (edge rails with postgresql).

@rafaelfranca
Ruby on Rails member

@kennyj I think the fix is fine

@kennyj
kennyj commented Dec 4, 2012

I sent the above PR. Thanks guys :)

@kennyj kennyj closed this in 634d28e Dec 4, 2012
@guilleiguaran
Ruby on Rails member

@mccuskk can you confirm that the fix is working for you with 3-2-stable branch? 😄

@mccuskk
mccuskk commented Dec 5, 2012

I've checked the patch. It looks fine. I've already monkey patched my own environment and it works great. Thanks for the speedy turnaround

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.