PostgreSQL 7.3.1 on i386-pc-linux-gnu, compiled by GCC 2.95.4
Slow Query:
explain analyze select x.state from monitor_service_events as x where
x.service_id = 784 order by x.event_id desc limit 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..33.17 rows=1 width=8) (actual time=161.62..262.17
rows=1 loops=1)
-> Index Scan Backward using monitor_service_events_pkey on
monitor_service_events x (cost=0.00..2051.77 rows=62 width=8) (actual
time=161.61..262.16 rows=2 loops=1)
Filter: (service_id = 784)
Total runtime: 262.29 msec
(4 rows)
Time: 264.38 ms
Fast Query:
explain analyze select x.state from monitor_service_events as x where
x.service_id = 784 order by x.event_id desc limit 2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=42.57..42.57 rows=2 width=8) (actual time=0.96..0.96
rows=2 loops=1)
-> Sort (cost=42.57..42.72 rows=62 width=8) (actual time=0.95..0.95
rows=3 loops=1)
Sort Key: event_id
-> Index Scan using service_id_monitor_service_events_key on
monitor_service_events x (cost=0.00..40.73 rows=62 width=8) (actual
time=0.08..0.71 rows=32 loops=1)
Index Cond: (service_id = 784)
Total runtime: 1.06 msec
(6 rows)
Time: 3.09 ms
Any ideas why changing the LIMIT from 2 to 1 causes the query to take
almost 100x longer?
PS. Yes, I've vacuum'd full/analyze, as well as reindexed. The table
contains about 72,000 rows.
--
Best Regards,
Mike Benoit
NetNation Communications Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
---------------------------------------
Disclaimer: Opinions expressed here are my own and not
necessarily those of my employer