Michael's Blog

Home | Gallery | Stats | Downloads | Links | Scripts | Fuel Stats | Wiki | RSS
We found out what your problem is, you don't have a problem.
-- Dan     Add quote.

Selecting random records in postgresql

2015-08-18 09:15:00 by Michael 0 Comments
Tags: postgresql sql code

In an attempt to make my site more responsive I have been working on optimizing the SQL code used on the backend, this includes the random quote generator that I have set up on the main page.

The old code used a query similar to below.

SELECT quote, name FROM quotes ORDER BY RANDOM() LIMIT 1

This works fine if you have small tables and fast disks but consider the issue when there is a table with millions of rows. To find *one* record the server must read through the table, sort the records, and then discard every result but one. This operation is slow and inefficient.

To improve performance you can reduce the number of rows read by using a primary key on the table. Each row has a unique ID number which can then be used as the limit for the random() function. For example, the following query will select a random record based on the last index number created:

SELECT quote_text, name FROM quotes WHERE quote_id = (SELECT floor(random() * (SELECT last_value from quotes_quote_id_seq)+1)) ;

This query is not perfect and may result in empty results which your code will need to accommodate for but it is still more efficient than reading the entire table every time the page is loaded.