Slow performing PostgreSQL Query, not understood why

Question

I have the following query in rails which uses PostgreSQL 9 as the DB.

@user_random = User.where(:id => User.where(:pubic_profile_visible => true).order('random()').limit(1).select(:id).collect(&:id)).first

Which then on the DB Server:

Explain plan

Query plan  Limit  (cost=48655.53..48655.53 rows=1 width=4)
Query plan    ->  Sort  (cost=48655.53..50880.35 rows=889930 width=4)
Query plan          Sort Key: (random())
Query plan          ->  Seq Scan on users  (cost=0.00..44205.88 rows=889930 width=4)
Query plan                Filter: (pubic_profile_visible AND (deleted_at IS NULL))

Any suggestions on why this is causing a bottleneck? Thank you

Well, it appears you are sorting 800K records on random(), to pick only one. — wildplasser
– wildplasser, Commented Nov 10, 2012 at 18:07

Richard Huxton · Accepted Answer · 2012-11-10 19:07:58Z

1

Like @wildplasser says this query is doing exactly what you asked it to do.

It can't pick the lowest random() for your "LIMIT 1" without fetching all 800,000 rows, generating a random number and then sorting them.

Try googling "postgresql select random row" and you'll get half a dozen useful stackoverflow questions and also a link to an old blog post on depesz.com which covers the various options:

http://www.depesz.com/2007/09/16/my-thoughts-on-getting-random-row/

answered Nov 10, 2012 at 19:07

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

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

1 Comment

Richard Huxton Over a year ago

Note that in 9.5 you can now use TABLESAMPLE in the FROM clause: postgresql.org/docs/current/static/sql-select.html#SQL-FROM and blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2

Collectives™ on Stack Overflow

Slow performing PostgreSQL Query, not understood why

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related