Skip to content

Instantly share code, notes, and snippets.

@kmatt
Forked from karlseguin/gist:2992426
Created August 14, 2012 18:28
Show Gist options
  • Save kmatt/3351502 to your computer and use it in GitHub Desktop.
Save kmatt/3351502 to your computer and use it in GitHub Desktop.
sample code used by ranking post
# 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
@kmatt
Copy link
Author

kmatt commented Aug 14, 2012

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment