Slow query using LIMIT

From: Poul Møller Hansen <freebsd(at)pbnet(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow query using LIMIT
Date: 2005-09-27 12:33:25
Message-ID: 43393C15.6050606@pbnet.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-09-27 13:38:46 Re: Extraordinarily slow!!
Previous Message John D. Burger 2005-09-27 12:29:38 Mysterious query plan