If I am doing
select *
from table_name
order by priority desc
And if I am doing
select *
from table_name
order by priority desc
limit 1
Is the second query faster? Or do both queries take the same amount of time to execute?
I agree that adding limit 1 without any order by clause is much more efficient and faster than a query without limit 1.
But when order by clause is added does it makes sense in using limit 1, because it has already scanned the table to do order by priority.
I am developing a application which performs a such order by with limit queries, and I want to know the whether limit with order by improves performance of a SQL query or not.
Kunj Bosamia is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Your 2 queries:
select * from table_name order by priority desc
select * from table_name order by priority desc limit 1
should have identical execution plans, with the only difference that in the second limit query, Postgres will only return the first record. That being said, I would expect the performance of the two queries to be pretty similar. Perhaps, the second version might execute faster in production, as it is returning potentially a lot less data.
Keep in mind that these 2 queries do not do the same thing. The first query returns all records, sorted by priority. The second query returns just one record, with the highest priority (though, as mentioned above, this still requires sorting the entire table).