Sql Performance for Rails

@h6165

Abhishek Yadav

ரூபீ ப்ரோக்ராமர்

Co-organizer: Chennai.rb

Topics covered

• Indexing
• N+1s
• Joins
• Pagination and Reports
• Geoquerying
• Serialization

Indexing

Indexing - the goal

The goal is the avoid full table scans

Indexing - the basics

• An index is a on disk data structure
• Like the index in a book, it can point to the row where our data sits
• We have to create indexes manually, just like we create tables
• An index is created for a table and a column or columns

Indexing - the basics

Example (Rails and mysql) -

-- Create an index

CREATE INDEX users_on_email ON users (email);


-- It is used for this query
select * from users where email="abhishek@example.com";

ActiveRecord::Migration.add_index :users, :email

User.where(email: "abhishek@example.com")

Indexing - the essentials

• We need indexes for WHERE clause
• We need indexes for ORDER BY clause
• We need indexes for reference columns
• We need compound indexes sometimes
• We don't need low selectivity indexes
• We need function indexes sometimes
• We need partial indexes sometimes

Indexing - ORDER BY clause

A query requesting less data will also do a full table scan if the ORDER BY clause has no index

-- Full table scan if there is no index on created_at

EXPLAIN SELECT * from users ORDER BY created_at DESC LIMIT 10;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 50019 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

Indexing - ORDER BY clause - composite index

A query with multiple columns in ORDER BY needs a composite index

-- Full table scan if there is no composite index

explain SELECT * from users ORDER BY created_at, first_name LIMIT 10;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 50019 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+

Indexing - ORDER BY clause - composite index

• For a composite index sequence is also important
• Best to keep high-cardinalty column first

-- create the compound index

CREATE INDEX users_on_created_at_first_name ON users (created_at, first_name);


-- Check explain

explain SELECT * from users ORDER BY created_at, first_name LIMIT 10;
+----+-------------+-------+------------+-------+---------------+--------------------------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key                            | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | users_on_created_at_first_name | 773     | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+--------------------------------+---------+------+------+----------+-------+

Indexing - Pattern match 

• A %abhishek% will not be able to use any index
• A  abhishek% will be able to use an index

Indexing - Polymorphic associations - composite index

• For a polymorphic association, we need a composite index on the (id,type) combination

Indexing - Reference IDs (foreign-keys)

• The foreign-key ids can use indexes (belongs_to, habtm)
• Need not add index if a foreign key constraint is present

Indexing - Function indexes

• SQL functions applied on a column will not be able to use indexes
• Mysql (pre 5.7) doesn't have function indexes
• Postgresql has them

-- Does not use the index on email (lower is not needed in mysql)
select * from users where lower(email)="abhishek@example.com";

Indexing - Partial indexes

• Partial indexes allow us to index part a subset of a table
• Useful when we have a pseudo-delete implementation
• Available in Postgres

Indexing - Low selectivity indexes

• Indexes on columns that have few distinct values are useless
• They also add to write cost
• Example - gender

N+1s

Handling N+1s

• Are the biggest cause of performance problems in Rails apps
• Problem much bigger with APIs and reports
• Easy to introduce, not as easy to fix

Handling N+1s - the basics

• Happen when we run a query inside a loop
• Can be fixed by eager-loading

# Leads to N+1
@users = User.featured.order(:rating).limit(20)

<% @users.each do |user| %>
  <%= user.name %> has <%= user.posts.size %> posts
<% end %>


# Eager loading
@users = User.featured.order(:rating).limit(20).includes(:posts)
@users = User.featured.order(:rating).limit(20).eager_load(:posts)

Handling N+1s - eager loading

• includes fires another query, eager_load does an OUTER JOIN
• includes generally sufficient 80% of times
• eager_load can lead to a slow query

# Leads to N+1
@users = User.featured.order(:rating).limit(20)

<% @users.each do |user| %>
  <%= user.name %> has <%= user.posts.size %> posts
<% end %>


# Eager loading
@users = User.featured.order(:rating).limit(20).includes(:posts)
@users = User.featured.order(:rating).limit(20).eager_load(:posts)

Handling N+1s - unintended

• Api building library generally require us to specify methods that get the needed data. And then the methods are run over a collection or records
• If any of the methods contain a query, N+1s happen
• Eager loading doesn't help

# This method will always fire a query. Eager loading or not
def ratings_count
  self.ratings.count
end

Handling N+1s - unintended

• The only solution is to not use any query methods in such scenario (API building, reports etc)
• Better to use Ruby code based alternatives in some places
• Eg1 - count

# counting in Ruby is better than using SQL count

def ratings_count
  self.ratings.to_a.count
end

Handling N+1s - unintended

• Eg2 - overuse of scopes

class User

  # Leads N+1s, despite the eager-loading,
  # because Rating.active is a scope that fires another query
  def active_ratings
    self.ratings.active
  end

  # A simple alternative, doesn't cause N+1
  def active_ratings2
    self.ratings{ |r| r.active? }
  end

end

Handling N+1s - unintended

• Eg2 - avoid the pluck

# Pluck will always fire a query
def ratings_sum
  ratings.pluck(:stars).sum
end

# Here if ratings is eager-loaded, we avoid the extra query
def ratings_sum2
  ratings.select(&:stars).sum
end

Handling N+1s - eager-load overdone

• Eager load calls can end-up loading too much data

# Restaurants in the city may be few
# but some of them may have too many comments or check-ins

Restaurant.in_city(city)
  .includes(:ratings => [:rater])
  .includes(:comments => [:commenter, :comment_ratings])
  .includes(:check_ins => [:customer])

Joins

Joins

• Joins can easily become bottlenecks if not done right
• Outer joins and inner joins work differently
• Use of indexes is confusing
• De-normalizing can help

Joins - the risk

• The biggest risk is when we request a join of two large tables
• Database may have to do MxN amount of work
• Indexes can help somewhat, but at least one table will undergo full scan
• With habtms, we might be joining three tables

# Get top twenty restaurants by ratings
# Doest show those without ratings

Restaurant.joins(:ratings)
    .select("restaurants.id, count(ratings.restaurant_id) as ratings_count")
    .group("ratings.restaurant_id")
    .order("ratings_count desc")
    .limit(20)

Joins - recommendations

• Inner Joins work best when one set is small
• We can unsure that by introducing some kind of scope, on at least one side

# Get top twenty Restaurants by recent ratings
# Doest show restaurants without ratings

Restaurant.joins(:ratings)
    .where("ratings.created_at > ?", 3.month_ago)
    .select("restaurants.id, count(ratings.restaurant_id) as ratings_count")
    .group("ratings.restaurant_id")
    .order("ratings_count desc")
    .limit(20)

Joins - sub-queries

• Inner Joins can sometimes be avoided by sub-queries

# Get restaurants that were rated by the user'r friends

rating_ids = Rating.where(user_id: users.friend_ids)
                   .select(:restaurant_id)
Restaurant.where(id: rating_ids)

# The above goes as one query

Joins - denormalize

• Add columns that keep the data we are getting from the joins

# Add a column ratings_count that keeps the count
# Can use Rails's counter-cache

User.order("ratings_count desc")
    .limit(20)

Pagination and reports

Pagination - exports

• In some scenarios, like csv exports, pagination is not naturally required. This can lead to query performance degradation
• Sometimes the degradation is noticed after a duration of use.
• Also happened to me for non csv exports exports (API, we-report)

Pagination - exports

• Solution-1: always have some kind of limits (like data from last one year). Product owners don't always agree to this, so having something arbitrary and sizeable is also ok.
• Solution-2: do reports in background, send them via email

Pagination - pages

• Page based pagination also suffers from performance problem on larger offsets (when page nos are high)
• This is because the sql offset can't work without full table scan
• When data-set grows in size, use alternatives like window-functions
• Infinite scroll type pagination always performs fine, should be chosen when possible. 

Geo-quering

Geo-querying - closest to given location

• 'Show me ten restaurants closest to my location' is a common query, specially in mobile applications
• Simplest way of doing this is to calculate distance in a sql query

SELECT
restaurants.*,
  ROUND(
    3956 * 2 * ASIN ( SQRT (
      POWER( SIN((13.14 - restaurants.latitude)*pi()/180 / 2),2)
      + COS(orig_lat * pi()/180) 
      * COS(restaurants.latitude *pi()/180) 
      * POWER(SIN((80.12 - restaurants.longitude) *pi()/180 / 2), 2) 
    ) ),2) as distance
FROM
 restaurants;

Geo-querying - closest to given location

• The calculation is slow on large datasets as it performs full table scan
• Indexing is difficult, and database dependent

SELECT
restaurants.*,
  ROUND(
    3956 * 2 * ASIN ( SQRT (
      POWER( SIN((13.14 - restaurants.latitude)*pi()/180 / 2),2)
      + COS(orig_lat * pi()/180) 
      * COS(restaurants.latitude *pi()/180) 
      * POWER(SIN((80.12 - restaurants.longitude) *pi()/180 / 2), 2) 
    ) ),2) as distance
FROM
 restaurants;

Geo-querying - possible alternatives - 1

• Add a max distance clause

SELECT
restaurants.*,
  ROUND(
    3956 * 2 * ASIN ( SQRT (
      POWER( SIN((13.14 - restaurants.latitude)*pi()/180 / 2),2)
      + COS(orig_lat * pi()/180) 
      * COS(restaurants.latitude *pi()/180) 
      * POWER(SIN((80.12 - restaurants.longitude) *pi()/180 / 2), 2) 
    ) ),2) as distance
FROM
 restaurants;
WHERE 
 distance < 100

Geo-querying - possible alternatives - 2

• Use geo-spacial indexes
• Postgres has them for a while
• Mysql-5.7 has them

Geo-querying - possible alternatives - 3

• Use Elasticsearch or similar