Skip to content

Instantly share code, notes, and snippets.

@BurntBrunch

BurntBrunch/gist:1868909

Created February 20, 2012 11:49
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save BurntBrunch/1868909 to your computer and use it in GitHub Desktop.
Save BurntBrunch/1868909 to your computer and use it in GitHub Desktop.
Download ZIP
ORDER BY RANDOM() vs random offsets
Raw
gistfile1.sql
== ORDER BY RANDOM() ==
hs=> explain analyze select * from requests_request order by random() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=21.12..21.13 rows=1 width=242) (actual time=1.447..1.448 rows=1 loops=1)
-> Sort (cost=21.12..22.00 rows=350 width=242) (actual time=1.445..1.445 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 18kB
-> Seq Scan on requests_request (cost=0.00..19.38 rows=350 width=242) (actual time=0.014..0.743 rows=352 loops=1)
Total runtime: 1.501 ms
(6 rows)
hs=> explain analyze select * from requests_request order by random() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=21.12..21.13 rows=1 width=242) (actual time=1.466..1.467 rows=1 loops=1)
-> Sort (cost=21.12..22.00 rows=350 width=242) (actual time=1.464..1.464 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 19kB
-> Seq Scan on requests_request (cost=0.00..19.38 rows=350 width=242) (actual time=0.015..0.758 rows=352 loops=1)
Total runtime: 1.520 ms
(6 rows)
hs=> explain analyze select * from requests_request order by random() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=21.12..21.13 rows=1 width=242) (actual time=1.301..1.302 rows=1 loops=1)
-> Sort (cost=21.12..22.00 rows=350 width=242) (actual time=1.298..1.298 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 17kB
-> Seq Scan on requests_request (cost=0.00..19.38 rows=350 width=242) (actual time=0.014..0.679 rows=352 loops=1)
Total runtime: 1.354 ms
(6 rows)
hs=> explain analyze select * from requests_request order by random() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=21.12..21.13 rows=1 width=242) (actual time=1.434..1.435 rows=1 loops=1)
-> Sort (cost=21.12..22.00 rows=350 width=242) (actual time=1.432..1.432 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 17kB
-> Seq Scan on requests_request (cost=0.00..19.38 rows=350 width=242) (actual time=0.015..0.755 rows=352 loops=1)
Total runtime: 1.489 ms
(6 rows)
hs=> explain analyze select * from requests_request order by random() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Limit (cost=21.12..21.13 rows=1 width=242) (actual time=1.438..1.439 rows=1 loops=1)
-> Sort (cost=21.12..22.00 rows=350 width=242) (actual time=1.436..1.436 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 17kB
-> Seq Scan on requests_request (cost=0.00..19.38 rows=350 width=242) (actual time=0.014..0.744 rows=352 loops=1)
Total runtime: 1.492 ms
(6 rows)
== RANDOM OFFSET ==
create or replace function randomoffset()
returns double precision as $body$
select ceil(random()*(count(*)-1)) from requests_request;
$body$ language sql;
hs=> explain analyze select * from requests_request offset randomoffset() limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=1.85..1.90 rows=1 width=242) (actual time=0.394..0.395 rows=1 loops=1)
-> Seq Scan on requests_request (cost=0.00..18.50 rows=350 width=242) (actual time=0.004..0.068 rows=75 loops=1)
Total runtime: 0.432 ms
(3 rows)
hs=> explain analyze select * from requests_request offset randomoffset() limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=1.85..1.90 rows=1 width=242) (actual time=0.315..0.315 rows=1 loops=1)
-> Seq Scan on requests_request (cost=0.00..18.50 rows=350 width=242) (actual time=0.003..0.031 rows=31 loops=1)
Total runtime: 0.352 ms
(3 rows)
hs=> explain analyze select * from requests_request offset randomoffset() limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=1.85..1.90 rows=1 width=242) (actual time=0.378..0.379 rows=1 loops=1)
-> Seq Scan on requests_request (cost=0.00..18.50 rows=350 width=242) (actual time=0.003..0.059 rows=62 loops=1)
Total runtime: 0.413 ms
(3 rows)
hs=> explain analyze select * from requests_request offset randomoffset() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Limit (cost=1.85..1.90 rows=1 width=242) (actual time=0.531..0.532 rows=1 loops=1)
-> Seq Scan on requests_request (cost=0.00..18.50 rows=350 width=242) (actual time=0.003..0.145 rows=174 loops=1)
Total runtime: 0.569 ms
(3 rows)
hs=> explain analyze select * from requests_request offset randomoffset() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Limit (cost=1.85..1.90 rows=1 width=242) (actual time=0.556..0.557 rows=1 loops=1)
-> Seq Scan on requests_request (cost=0.00..18.50 rows=350 width=242) (actual time=0.004..0.174 rows=184 loops=1)
Total runtime: 0.593 ms
(3 rows)
hs=> explain analyze select * from requests_request offset randomoffset() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Limit (cost=1.85..1.90 rows=1 width=242) (actual time=1.129..1.130 rows=1 loops=1)
-> Seq Scan on requests_request (cost=0.00..18.50 rows=350 width=242) (actual time=0.006..0.406 rows=334 loops=1)
Total runtime: 1.186 ms
(3 rows)
== RANDOM OFFSET WITH INDEX SCAN ==
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.332..0.333 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.021..0.049 rows=29 loops=1)
Total runtime: 0.371 ms
(3 rows)
hs=> explain analyze select * from requests_request order by id offset randomoffset() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.818..0.819 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.021..0.347 rows=323 loops=1)
Total runtime: 0.862 ms
(3 rows)
hs=> explain analyze select * from requests_request order by id offset randomoffset() limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.430..0.431 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.020..0.111 rows=87 loops=1)
Total runtime: 0.468 ms
(3 rows)
hs=> explain analyze select * from requests_request order by id offset randomoffset() limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.377..0.378 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.021..0.066 rows=51 loops=1)
Total runtime: 0.417 ms
(3 rows)
hs=> explain analyze select * from requests_request order by id offset randomoffset() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.820..0.821 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.020..0.328 rows=297 loops=1)
Total runtime: 0.858 ms
(3 rows)
hs=> explain analyze select * from requests_request order by id offset randomoffset() limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.740..0.741 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.021..0.290 rows=266 loops=1)
Total runtime: 0.780 ms
(3 rows)
hs=> explain analyze select * from requests_request order by id offset randomoffset() limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3.54..3.64 rows=1 width=242) (actual time=0.515..0.516 rows=1 loops=1)
-> Index Scan using requests_request_pkey on requests_request (cost=0.00..35.37 rows=350 width=242) (actual time=0.032..0.076 rows=29 loops=1)
Total runtime: 0.579 ms
(3 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment