Search Postgresql Archives

Re: Slow query using LIMIT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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).

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux