I need to quickly select row from PostgreSQL query. I've read Best way to select random rows PostgreSQL . quick random row selection in Postgres
By far quickest of I've read is:
CREATE EXTENSION IF NOT EXISTS tsm_system_rows;
SELECT myid FROM mytable TABLESAMPLE SYSTEM_ROWS(1);
2 ms average. But as noted in comments it is not "completely random".
I've tried
SELECT id FROM a OFFSET floor(random()*3000000) LIMIT 1;
15-200 ms.
The simplest idea is to select by id as my ids are continuous. But
select floor(random ()*1000); 2ms
select * from a where id=233; 2ms (and again 2ms for other constants)
but
SELECT * FROM a where id = floor(random ()*1000)::integer; 300ms!!!
Why 300 not 4? Is is possible to reorder somehow, hints etc. to make 4 ms?
This is because random() is defined as volatile, so Postgres evaluates it for each and every row again - effectively going through all rows.
If you want to prevent that, "hide" it behind a (otherwise useless) subselect:
SELECT *
FROM a
where id = (select trunc(random ()*1000)::integer);