CODEWITHSUNDEEP
sqlpostgresqllimit
AMADANON Inc.
AMADANON Inc.

Reputation: 5919

Simple query gets much slower when I add a LIMIT clause

I have a table (of snmp traps, but that's neither here nor there).

I have a query which pulls some records, which goes something like this:

SELECT * 
FROM traps_trap 
WHERE summary_id = 1600
ORDER BY traps_trap."trapTime";

This responds instantaneously, with 6 records.

When I add LIMIT 50 (since not all results will have only 6 records), it is very, VERY slow (to the point of not returning at all).

There is an index on the summary_id column, I can only assume that it isn't being used for the second query.

I understand that the tool to solve this is explain, but I'm not familiar enough with it to understand the results.

The explain analyse verbose for the first (quick) query is as follows:

                                                                   QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=14491.51..14502.48 rows=4387 width=263) (actual time=0.128..0.130 rows=6 loops=1)
   Output: id, summary_id, "trapTime", packet
   Sort Key: traps_trap."trapTime"
   Sort Method: quicksort  Memory: 28kB
   ->  Index Scan using traps_trap_summary_id on public.traps_trap  (cost=0.00..13683.62 rows=4387 width=263) (actual time=0.060..0.108 rows=6 loops=1)
         Output: id, summary_id, "trapTime", packet
         Index Cond: (traps_trap.summary_id = 1600)
 Total runtime: 0.205 ms
(8 rows)

explain for the second is:

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..2538.69 rows=10 width=263)
   ->  Index Scan using "traps_trap_trapTime" on traps_trap  (cost=0.00..1113975.68 rows=4388 width=263)
         Filter: (summary_id = 1600)
(3 rows)

I run VACUUM and ANALYZE daily, I understand that is supposed to improve the plan. Any other pointers?

Upvotes: 3

Views: 151

Answers (1)

klin
klin

Reputation: 121604

Index scan using trapTime is much slower than one using summary_id. I would try to nest the query (to use plan #1):

select * from (
    SELECT * 
    FROM traps_trap 
    WHERE summary_id = 1600
    ORDER BY traps_trap."trapTime"
) t
limit 50;

Edit:

After doing some tests I learned that simple query nesting (as above) has no effect on the planner. To force the planner to use traps_trap_summary_id index you can use CTE (my tests confirm this method):

with t as (
    SELECT * 
    FROM traps_trap 
    WHERE summary_id = 1600
    ORDER BY traps_trap."trapTime"
)
select * from t
limit 50;

Upvotes: 1

Related Questions