CODEWITHSUNDEEP
sqlpostgresqlindexing
cdxf
cdxf

Reputation: 5648

Postgres performs seq scan when using with Order By

select
    thread.id,
    thread.last_modified,
    count(*) as count
from
    thread 
inner join
    comment 
        on thread.id=comment.thread_id 
group by
    thread.id 
    limit 20

This query will use index scan as intendedenter image description here

But now if I add order by into the query:

select
    thread.id,
    thread.last_modified,
    count(*) as count
from
    thread 
inner join
    comment 
        on thread.id=comment.thread_id 
group by
    thread.id 
order by thread.last_modified
    limit 20

Postgres will perform 2 seq scan on both comment and thread like this: enter image description here

Even though I have an index on lastModified:

select thread.id
from thread
order by last_modified
limit 10

enter image description here

So what's wrong with my query?

Upvotes: 2

Views: 282

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246383

The first query only has to find the comments to the first 20 threads, while the second one has to select the comments for all threads. A sequential scan is the most efficient way to do that.

You can improve the query if you put the LIMIT into a subselect in the FROM clause:

... FROM (SELECT id, last_modified
         FROM thread
         ORDER BY last_modified LIMIT 20) thread

Upvotes: 2

Related Questions