sample code used by ranking post

gistfile1.rb

# http://openmymind.net/Paging-And-Ranking-With-Large-Offsets-MongoDB-vs-Redis-vs-Postgresql/

lids = ['4fe907f1210b2e9e3080f001', '4fe907f1210b2e9e3080f002', '4fe907f1210b2e9e3080f003', '4fe907f1210b2e9e3080f004', '4fe907f1210b2e9e3080f005']
 
 insert the data
 open('data.csv', 'w') do |f| 
   6000000.times do |i| 
        f.puts "#{lids.sample},user_#{i},#{(rand() * 10000000).to_i}"
      end 
  end

# create table scores (lid char(24), u varchar(15), score integer);
# create index scores_lid_u on scores(lid, u);
# create index scores_lid_score on scores(lid, score);
# copy scores from 'data.csv' with (format csv);

 open('ranktest.sql','w') do |f| 
   100.times do |i| 
     f.puts "select score, u from scores where lid = '#{lids.sample}' order by score limit 20 offset #{i * 10000};"
   end 
 end 

# $ time psql -f ranktest.sql test > ranktest.out

select score, u from scores where lid = ___ order by score limit 20 offset ___

$ time psql -f ranktest.sql test > ranktest.out

real 4m29.754s
user 0m0.015s
sys 0m0.010s