Super-charge ActiveRecord#explain with pg-eyeballs 👀

November 02 Bullet_white By Derek Bullet_white Comments Comments

When I have a slow Postgres ActiveRecord query, one of the first tools I reach for is EXPLAIN. pg-eyeballs is a Ruby gem that makes acting on this frequently noisy output easier.

Those of you that know your way around ActiveRecord might be confused. ActiveRecord comes with an #explain method. What void does pg-eyeballs fill? An important one:

  • ActiveRecord just runs EXPLAIN, not EXPLAIN ANALYZE on the query. Without ANALYZE, the query isn't actually executed, which means you can't compare the query planner's estimates with what actually happened.
  • The Postgres EXPLAIN output is noisy. It is difficult to grok if you aren't running the command frequently. pg-eyeballs makes it easy to export the output to tools that provide a more user-friendly view of the query plan.

Let's look at pg-eyeballs, maintained by Brad Urani, in practice.

Visualizing queries in rails console

I use pg-eyeballs to better visualize the output of EXPLAIN directly from rails console. pg-eyeballs plays well with the gocmdpev, which annotates the query nodes in helpful fashion.

Here's an example:

gocmdpev

Notice how gocmdpev applies friendly labels to nodes that deserve more attention (slowest, costliest, largest, etc).

gocmdpev is a command-line Go app. You'll need to install this separately.

Outputing EXPLAIN results to Pev

While gocmdpev is my default eyeballs flavor, there are a couple of cases where I need a different visualization tool:

  • There are many nodes in the query plan. The gocmdpev output, which is displayed in the terminal, doesn't have the interactivity that a web UI can provide.
  • The ops team isn't excited about installing another binary on app servers.

In the above cases, I reach for Pev, the inspiration for gocmdpev. I can export content in a pev-friendly way via #log_json:

pev

TL;DR

pg-eyeballs + gocmdpev and/or Pev makes it easier to understand ActiveRecord Postgres EXPLAIN output. Find it at your nearest Ruby gem store.

Subscribe for more ActiveRecord optimization tips 👇.

Get notified of new posts.

Once a month, we'll deliver a finely-curated selection of optimization tips to your inbox.

Comments

comments powered by Disqus