CODEWITHSUNDEEP
postgresqlquery-optimization
Nishan
Nishan

Reputation: 2871

Optimizing select query with limit and order by

Following is my query:

select * from table order by timestamp desc limit 10

this takes too much time compared to

select * from table limit 10

How can I optimize the first query to get to near performance of second query.

UPDATE: I don't have control over the db server, so can not index columns to gain performance.

Upvotes: 1

Views: 2960

Answers (3)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

Quassnoi is correct -- you need an index on timestamp.

That said, if your timestamp field reasonably maps your primary key (e.g. a date_created or an invoice_date field), you can try this workaround:

select *
from (select * from table order by id desc limit 1000) as table
order by timestamp desc limit 10;

Upvotes: 3

Ketema
Ketema

Reputation: 6548

@Nishan is right. There is little you can do. If you do not need every column in the table you may gain a few milliseconds by explicitly asking for just the columns you need

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

Create an index on timestamp.

Upvotes: 4

Related Questions