Slow Query with LIMIT

3 votes

1 answer

1170 views

postgresql select performance postgresql-9.2 offset-fetch postgresql-performance

                          We have this query:

    SELECT "messages".*
    FROM "messages"
    WHERE "messages"."account_id" = 1
    AND "messages"."status" = 'unread'
    AND (created_at >= '2014-04-24 01:00:00.000000')
    ORDER BY id DESC

It runs fine under 0.1ms. But when we add **LIMIT**:

    SELECT "messages".*
    FROM "messages"
    WHERE "messages"."account_id" = 1
    AND "messages"."status" = 'unread'
    AND (created_at >= '2014-04-24 01:00:00.000000')
    ORDER BY id DESC
    LIMIT 5

It runs over 20,000ms. Some notes:

- The messages table has over 300,000 rows, not that big.
- We have indexes on all 3 columns in the WHERE condition.
- The **account_id** and **created_at** conditions filter out 3,000 messages from the 300,000 rows. Out of the 3,000 messages in that **account_id**, there are only 3 unread messages. 

So we isolated each **WHERE** condition and found out about a certain behaviour -- we tweaked the **LIMIT** to correspond with the amount of unread messages:

    SELECT "messages".*
    FROM "messages"
    WHERE "messages"."account_id" = 1
    AND "messages"."status" = 'unread'
    AND (created_at >= '2014-04-24 01:00:00.000000')
    ORDER BY id DESC
    LIMIT 3

It runs under 0.100ms. Any explanation for the difference in performance?
                        

Asked by Ace Subido (31 rep)

Oct 24, 2014, 01:32 AM
Last activity: Feb 10, 2019, 07:01 AM

Slow Query with LIMIT

Related Questions