Postgresql: Random and count in one query

Question

I use Postgresql 9.1. I have a table and I would like to get two things done in one query:

First: select one random sample row:

select * from table order by random() limit 1;

Second: count the number of rows the sample was selected from:

select count(1) from table;

Since a proper random() function has to know about all rows, I wonder if there is a way to get both results in one query.

I am guessing for something link:

select count(1), first.* from table order by random();

but i don't really know how to do it.

So I want a random row and the total number of rows.

Thanks for any ideas.

Steve

This question is very similar to stackoverflow.com/questions/5297396/… which has interesting answers... — alfonx
– alfonx, Commented Aug 15, 2015 at 9:20

Community · Accepted Answer · 2017-05-23 12:25:37Z

2

One way to do this:

SELECT *, (SELECT count(*) FROM tbl) AS ct
FROM   tbl
ORDER  BY random()
LIMIT  1;

If the table is huge and performance is an issue, there are tricks to speed up the random selection. Having a serial column without gaps would help a lot with that ...

If that is for you, have a look at this related answer: Random Row in MySQL
Works in PostgreSQL, too. Only substitute random() for rand().

BTW, the random() function works absolutely independent of the number of rows.

Here is a variant that produces the same with only one sequential scan:

SELECT *, count(*) OVER () AS ct
FROM   ef.adr
ORDER  BY random()
LIMIT  1;

A quick test with a table of 5k rows was slower, though.
Another one with a table of 400k rows shows a similar result. slightly slower. Window functions can be performance hogs. And count(*) is known to be rather slow for large tables in PostgreSQL, because every tuple has to be visited.

Index-only scans ("covering indexes") has recently been implemented in the devel version of 9.2. So we are in for a speedup with the next version here. More about that in a related post on dba.stackexchange.com:

In the meantime, 230 ms for the 400k table is not too bad.

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Nov 17, 2011 at 17:13

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alfonx Over a year ago

UPDATE: On PG 9.4 this is really running faster (compared to PG 9.1 which I was using when you answered my question)

Collectives™ on Stack Overflow

Postgresql: Random and count in one query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related