Dumb postgres query comparison between int4/varchar

description.md

This is a complete naive comparison, don't take it for granted!

Docker for MAC: Version 18.06.0-ce-mac70 (26399)

Image: postgres:9.4.10-alpine

Results

Postgres did a sequence scan for both queries with and without indexes.

insert.rake

namespace :lucas do
  task :run => :environment do |_, args|
    100_000.times { |i|
      puts Product.create!(name: SecureRandom.hex, status: 0, status2: 'active')
      puts Product.create!(name: SecureRandom.hex, status: 1, status2: 'archived')
    }
  end
end

product.rb

class Product < ApplicationRecord
  enum status: { active: 0, archived: 1 }
  #status2: [ :active, :archived ]
end

# == Schema Information
#
# Table name: products
#
#  id         :integer          not null, primary key
#  name       :string
#  status     :integer
#  status2    :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
# Indexes
#
#  index_products_on_status   (status)
#  index_products_on_status2  (status2)
#

queries.sql

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT * FROM products WHERE status = 1;

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT * FROM products WHERE status2 = 'active';