Best way to select a random row from a derived table

From: Ryan Murphy <ryanfmurphy(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: Best way to select a random row from a derived table
Date: 2018-01-28 06:39:33
Message-ID: CAHeEsBfP2DL2B+VwDZSoxMjz7KPTD2oFg0RDE-SRJ+gRn4wzXA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hello hackers and postgressers,

I am aware of 2 ways to select a random row from a table:

1) select * from table_name order by random() limit 1;
-- terribly inefficient

2) select * from table_name tablesample system_rows(1) limit 1;
-- only works on tables, not views or subqueries

Is there an option that is reasonably efficient and can be used on views
and subqueries?

Thanks!
Ryan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Fabien COELHO 2018-01-28 08:17:46 Re: Best way to select a random row from a derived table
Previous Message Adrian Klaver 2018-01-28 01:43:38 Re: pg 10.1 missing libpq in postgresql-devel

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2018-01-28 08:10:11 Re: General purpose hashing func in pgbench
Previous Message Jeff Davis 2018-01-28 06:06:59 Re: JIT compiling with LLVM v9.0