Slow query on a large table when using order by

Question

I have a table with ~30M tuples. The table looks like:

id | first_name | last_name | email
-----------------------------------------
1  | foo        | bar       | foo@bar.com

Also there are an index (btree index) for first_name and other for last_name.

The query below tooks about 200ms to return the results:

SELECT  
  * 
FROM my_table 
 WHERE (first_name ILIKE 'a%') 
 LIMIT 10 OFFSET 0

But the next one tooks about 15 seconds (adding the order by)

SELECT  
  * 
FROM my_table 
 WHERE (first_name ILIKE 'a%') 
 ORDER BY last_name asc, first_name asc 
 LIMIT 10 OFFSET 0 

What can I do to improve the performance of the last query?

Answer 1

You have two choices of indexes for this query:

SELECT t.* 
FROM my_table 
WHERE first_name ILIKE 'a%'
ORDER BY last_name asc, first_name asc 
LIMIT 10 OFFSET 0 ;

One is for the WHERE clause. The best index for this is my_table(first_name). The second possibility is to use an index for the ORDER BY, my_table(last_name, first_name).

Which is better depends on the data you have. You might want to try both to see which works better, if overall performance is a goal.

Finally, a computed index might be the best way to go. For your case, write the query as:

SELECT t.* 
FROM my_table 
WHERE lower(substr(first_name, 1, 1)) = 'a'
ORDER BY last_name asc, first_name asc 
LIMIT 10 OFFSET 0 ;

Then, the index you want is mytable(lower(substr(first_name, 1, 1)), last_name, first_name). This index can be used for both the WHERE and the ORDER BY, which should be optimal for this query.