pg_memsize.rb

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'pg'
  gem 'activerecord', '5.2.0'
  gem 'memory_profiler'
  gem 'benchmark-ips'
end

require 'active_record'
require 'memory_profiler'
require 'benchmark/ips'

ActiveRecord::Base.establish_connection(
  :adapter => "postgresql",
#   :database => "test_db"
)

pg = ActiveRecord::Base.connection.raw_connection

pg.async_exec <<SQL
drop table if exists topics
SQL

pg.async_exec <<SQL
CREATE TABLE topics (
    id integer NOT NULL,
    title character varying NOT NULL,
    last_posted_at timestamp without time zone,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    views integer DEFAULT 0 NOT NULL,
    posts_count integer DEFAULT 0 NOT NULL,
    user_id integer,
    last_post_user_id integer NOT NULL,
    reply_count integer DEFAULT 0 NOT NULL,
    featured_user1_id integer,
    featured_user2_id integer,
    featured_user3_id integer,
    avg_time integer,
    deleted_at timestamp without time zone,
    highest_post_number integer DEFAULT 0 NOT NULL,
    image_url character varying,
    like_count integer DEFAULT 0 NOT NULL,
    incoming_link_count integer DEFAULT 0 NOT NULL,
    category_id integer,
    visible boolean DEFAULT true NOT NULL,
    moderator_posts_count integer DEFAULT 0 NOT NULL,
    closed boolean DEFAULT false NOT NULL,
    archived boolean DEFAULT false NOT NULL,
    bumped_at timestamp without time zone NOT NULL,
    has_summary boolean DEFAULT false NOT NULL,
    vote_count integer DEFAULT 0 NOT NULL,
    archetype character varying DEFAULT 'regular'::character varying NOT NULL,
    featured_user4_id integer,
    notify_moderators_count integer DEFAULT 0 NOT NULL,
    spam_count integer DEFAULT 0 NOT NULL,
    pinned_at timestamp without time zone,
    score double precision,
    percent_rank double precision DEFAULT 1.0 NOT NULL,
    subtype character varying,
    slug character varying,
    deleted_by_id integer,
    participant_count integer DEFAULT 1,
    word_count integer,
    excerpt character varying(1000),
    pinned_globally boolean DEFAULT false NOT NULL,
    pinned_until timestamp without time zone,
    fancy_title character varying(400),
    highest_staff_post_number integer DEFAULT 0 NOT NULL,
    featured_link character varying
)
SQL

class Topic < ActiveRecord::Base
end


Topic.transaction do
  topic = {
  }
  Topic.columns.each do |c|
    topic[c.name.to_sym] = case c.type
                           when :integer then 1
                           when :datetime then Time.now
                           when :boolean then false
                           else "HELLO WORLD" * 2
                           end
  end

  1000.times do |id|
    topic[:id] = id
    Topic.create!(topic)
  end
end


def raw_all_1_clear
  sql = -"select * from topics limit 1"
  ActiveRecord::Base.connection.raw_connection.async_exec(sql).clear
end

def raw_all_clear
  sql = -"select * from topics limit 1000"
  ActiveRecord::Base.connection.raw_connection.async_exec(sql).clear
end

def raw_id_clear
  sql = -"select id from topics limit 1000"
  ActiveRecord::Base.connection.raw_connection.async_exec(sql).clear
end

def raw_all_1
  sql = -"select * from topics limit 1"
  ActiveRecord::Base.connection.raw_connection.async_exec(sql)
end

def raw_all
  sql = -"select * from topics limit 1000"
  ActiveRecord::Base.connection.raw_connection.async_exec(sql)
end

def raw_id
  sql = -"select id from topics limit 1000"
  ActiveRecord::Base.connection.raw_connection.async_exec(sql)
end

def test(method, memsize)
  puts
  puts "-"*50
  puts "#{method} #{memsize}"
  puts "-"*50
  send method

  MemoryProfiler.report do
    send method
  end.pretty_print(detailed_report: false, retained_strings: false, allocated_strings: false)
end


tests = %i{
  raw_id
  raw_all
  raw_all_1
  raw_id_clear
  raw_all_clear
  raw_all_1_clear
}

tests.each do |t|
  [true, false].each do |memsize|
    ActiveRecord::Base.connection.raw_connection.guess_result_memsize = memsize
    test t, memsize
  end
end

Benchmark.ips do |b|
  [true, false, true, false].each do |memsize|
    ActiveRecord::Base.connection.raw_connection.guess_result_memsize = memsize
    tests.each do |t|
      b.report("#{t} #{memsize}") do |i|
        while i > 0
          send t
          i -= 1
        end
      end
    end
  end
end

# to run deep analysis run
# MemoryProfiler.report do
#   ar
# end.pretty_print