Poor query performance when using limit 1 compared to limit 2?
От | Mike Benoit |
---|---|
Тема | Poor query performance when using limit 1 compared to limit 2? |
Дата | |
Msg-id | 1053964544.31752.78.camel@mikeb.staff.netnation.com обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: