CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlheroku
Pahlevi Fikri Auliya
Pahlevi Fikri Auliya

Reputation: 4527

How to fix a slow implicit query on pg_attribute table in Rails

In our production environment, we noticed frequent spikes (~every 1 hour) in our Rails application. Digging deeper, it's due to the following query which cumulatively runs in >1.5 s (called 100x) in a single HTTP request.

SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = ?::regclass AND a.attnum > ? AND NOT a.attisdropped 
ORDER BY a.attnum

We don't have code calling that table explicitly but seems it's called by Rails to figure out the attributes for each model. "Unexpected SQL queries to Postgres database on Rails/Heroku" is related.

But shouldn't it be called non-repetitively by Rails?

How do we speed this up?

Upvotes: 21

Views: 2398

Answers (3)

lobati
lobati

Reputation: 10215

As recommended in this issue comment, you can add it to your Procfile:

web: rails db:schema:cache:dump && rails s ...

If you're on Heroku, you cannot simply run it using heroku run ... because the schema dump is only generated on a per-server basis and the command will run on a one-off dyno, which will disappear immediately after the command is run. You need to make sure it runs on each server as it boots.

Upvotes: 0

matthewd
matthewd

Reputation: 4420

In production, each Rails process will run that query once for each table/model it encounters. That's once per rails s, not per request: if you're seeing it repeatedly, I'd investigate whether your processes are being restarted frequently for some reason.

To eliminate those runtime queries entirely, you can generate a schema cache file on your server:

RAILS_ENV=production rails db:schema:cache:dump

(Rails 4: RAILS_ENV=production bin/rake db:schema:cache:dump)

That command will perform the queries immediately, and then write their results to a cache file, which future Rails processes will directly load instead of inspecting the database. Naturally, you'll then need to regenerate the cache after any future database schema changes.

Upvotes: 3

Edgars Jekabsons
Edgars Jekabsons

Reputation: 2853

I have not experienced this issue in any Rails application I've worked so far. I think your solution is to add active-record-query-trace to your project and check, what is triggering this query.

At work I use this setup:

# Gemfile
group :test, :development do
  gem "active-record-query-trace"
end

# config/initializers/ar_tracer.rb
if ENV.has_key?("AR_TRACER") && defined? ActiveRecordQueryTrace
  ActiveRecordQueryTrace.enabled = true
  ActiveRecordQueryTrace.lines = 20 # you may want to increase if not enough
end

Then simply start your rails server like this: AR_TRACER=1 bundle exec rails s.

Upvotes: 0

Related Questions