On Thu, 2006-03-30 at 13:59 +0200, Peter Eisentraut wrote: > Can anyone explain this: > > EXPLAIN ANALYZE select activity_id from activity where state in (10000, 10001) > order by activity_id limit 100; > > QUERY PLAN > > Limit (cost=0.00..622.72 rows=100 width=8) (actual > time=207356.054..207356.876 rows=100 loops=1) > -> Index Scan using activity_pk on activity (cost=0.00..40717259.91 > rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1) > Filter: ((state = 10000) OR (state = 10001)) > Total runtime: 207357.000 ms > ...just adding to Tom's comments: The interesting thing about this query is it *looks* like the index is being used to retrieve the matching rows and so the startup time looks wrong. However the index is being used instead of a sort to satisfy the ORDER BY, with the state clauses being applied as after-scan filters since those columns aren't part of the index. So the Index Scan starts at the leftmost page and scans the whole index... If the query had chosen a sort, the startup time would have been easily understandable, but there's no indication from the EXPLAIN as to why the Index Scan exists. Perhaps it should be a TODO item to make the EXPLAIN say explicitly when an Index Scan is being used to provide sorted output? Best Regards, Simon Riggs
