Обсуждение: Slow query using LIMIT

Doing the following query:
explain analyze SELECT * FROM my.tablename WHERE node = '1234567890' AND
date BETWEEN '2005-03-27' AND NOW() ORDER BY id DESC

takes 1,3 sec. with this result:
 Sort  (cost=52971.52..53033.26 rows=24693 width=93) (actual
time=1141.002..1252.995 rows=25109 loops=1)
   Sort Key: id
   ->  Index Scan using test on tablename  (cost=0.00..50343.48
rows=24693 width=93) (actual time=1.968..363.499 rows=25109 loops=1)
         Index Cond: (((node)::text = '1234567890'::text) AND (date >=
'2005-03-27'::date) AND (date <= now()))
 Total runtime: 1322.876 ms

but when adding a LIMIT it takes almost 2 min...
explain analyze SELECT * FROM my.tablename WHERE node = '1234567890' AND
date BETWEEN '2005-03-27' AND NOW() ORDER BY id DESC LIMIT 10
this result:
 Limit  (cost=0.00..75.24 rows=10 width=93) (actual
time=115401.715..115401.879 rows=10 loops=1)
   ->  Index Scan Backward using unistat_pkey on tablename
(cost=0.00..185781.72 rows=24693 width=93) (actual
time=115401.697..115401.814 rows=10 loops=1)
         Filter: (((node)::text = '1234567890'::text) AND (date >=
'2005-03-27'::date) AND (date <= now()))
 Total runtime: 115402.183 ms

The index test looks like this: "test" btree (node, date, id) and it's a
version 8.0.1
Can anyone please explain to me why it behaves like that and what I can do ?


Thanks,
 Poul

Poul Møller Hansen wrote:
> Doing the following query:
> explain analyze SELECT * FROM my.tablename WHERE node = '1234567890' AND
> date BETWEEN '2005-03-27' AND NOW() ORDER BY id DESC
>
> takes 1,3 sec. with this result:
> Sort  (cost=52971.52..53033.26 rows=24693 width=93) (actual
> time=1141.002..1252.995 rows=25109 loops=1)
>   Sort Key: id
>   ->  Index Scan using test on tablename  (cost=0.00..50343.48
> rows=24693 width=93) (actual time=1.968..363.499 rows=25109 loops=1)
>         Index Cond: (((node)::text = '1234567890'::text) AND (date >=
> '2005-03-27'::date) AND (date <= now()))
> Total runtime: 1322.876 ms
>
> but when adding a LIMIT it takes almost 2 min...
> explain analyze SELECT * FROM my.tablename WHERE node = '1234567890' AND
> date BETWEEN '2005-03-27' AND NOW() ORDER BY id DESC LIMIT 10
> this result:
> Limit  (cost=0.00..75.24 rows=10 width=93) (actual
> time=115401.715..115401.879 rows=10 loops=1)
>   ->  Index Scan Backward using unistat_pkey on tablename
> (cost=0.00..185781.72 rows=24693 width=93) (actual
> time=115401.697..115401.814 rows=10 loops=1)
>         Filter: (((node)::text = '1234567890'::text) AND (date >=
> '2005-03-27'::date) AND (date <= now()))
> Total runtime: 115402.183 ms
>
> The index test looks like this: "test" btree (node, date, id) and it's a
> version 8.0.1
> Can anyone please explain to me why it behaves like that and what I can
> do ?
>
>
> Thanks,
> Poul
>

Notice that at the first query it used the "test" index while at the
second query it used an index called "unistat_pkey" (also used it
backward though I dont know if its harder to the server or not).

> Doing the following query:
> explain analyze SELECT * FROM my.tablename WHERE node = '1234567890'
> AND date BETWEEN '2005-03-27' AND NOW() ORDER BY id DESC

ORDER BY node,id is forcing the planner to use the right index.

Thanks,
 Poul