Kouber Saparev wrote: > db=# EXPLAIN ANALYZE > SELECT > * > FROM > login_attempt > WHERE > username='kouber' > ORDER BY > login_attempt_sid DESC; > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------ > > Sort (cost=1415.15..1434.93 rows=7914 width=38) (actual > time=0.103..0.104 rows=2 loops=1) > Sort Key: login_attempt_sid > Sort Method: quicksort Memory: 25kB > -> Index Scan using login_attempt_username_idx on login_attempt > (cost=0.00..902.71 rows=7914 width=38) (actual time=0.090..0.091 rows=2 > loops=1) > Index Cond: ((username)::text = 'kouber'::text) > Total runtime: 0.140 ms It's expecting 7914 rows returned and is getting only 2. That is probably the root of the problem. > However when I add a LIMIT clause to the same query the planner no > longer uses the right index, hence the query becomes very slow: > > > db=# EXPLAIN ANALYZE > SELECT > * > FROM > login_attempt > WHERE > username='kouber' > ORDER BY > login_attempt_sid DESC LIMIT 20; Since it's expecting 7914 rows for "kouber" it thinks it will find the 20 rows you want fairly quickly by just looking backward through the login_attempt_pkey index. Try increasing the stats on the username column. ALTER TABLE login_attempt ALTER COLUMN username SET STATISTICS 100; ANALYZE login_attempt; You can try different values of statistics up to 1000, but there's no point in setting it too high. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
