CODEWITHSUNDEEP
sqlpostgresqlindexingpostgresql-9.3postgresql-performance
Victor
Victor

Reputation: 5131

Slow query on a large table when using order by

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

id | first_name | last_name | email
-----------------------------------------
1  | foo        | bar       | [email protected]

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?

Upvotes: 0

Views: 76

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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.

Upvotes: 2

sagneta
sagneta

Reputation: 1701

CREATE INDEX my_table_idx1 ON my_table (last_name ASC NULLS LAST, first_name ASC NULLS LAST);

Some notes in no particular order:

  • The ASC NULLS LAST is the default but I thought I would illustrate in case you want to play withe ordering.
    • This is one of those rare times where a multi-column index will help you. Generally you are advised to create numerous single column indexes and allow the query optimizer the luxury of selecting the indexes to use rather than forcing it into a decision.
    • I noticed an ILIKE in there which is is not performant and might be your real problem unless you are using the pg_trgm package along with a GIST index.

Upvotes: 0

jarlh
jarlh

Reputation: 44805

I assume the following index will speed up the ORDER BY:

create index my_table_lname_fname on my_table (last_name, first_name)

Upvotes: 1

Related Questions