On Mon, 7 Oct 2002, Szymon Juraszczyk wrote:
> The table contains some 4,7 milion rows. > > Let's try to have look for entries with account = 570: > It looks to me it's estimating that 4275 rows will match account=570. If you're using 7.2 and have analyzed, you may want to up the number of buckets the analyzer uses in order to get a better sampling. I think if it had a reasonable idea of how many rows it was returning, it'd probably pick the correct index. (As a side note, an index on account,timestamp (or is it timestamp, account) would possibly give the best results.) > There's no such entries. Let's try perform SELECT, anyway. We want the > result ordered by 'timestamp': > > explain analyze select * from login_history where account = 570 order by > timestamp; > NOTICE: QUERY PLAN: > > Sort (cost=16952.48..16952.48 rows=4275 width=19) (actual time=0.21..0.21 > rows=0 loops=1) > -> Index Scan using login_history_acct_idx on login_history > (cost=0.00..16694.67 rows=4275 width=19) (actual time=0.13..0.13 rows=0 > loops=1) > Total runtime: 0.28 msec > > The response is given immediately. However, when we add LIMIT clause to > the query, we'll have to wait for 16 seconds to get the very same, empty > result (!?): > > explain analyze select * from login_history where account = 570 order by > timestamp limit 1; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..27.03 rows=1 width=19) (actual time=16022.11..16022.11 > rows=0 loops=1) > -> Index Scan using login_history_pkey on login_history > (cost=0.00..115531.35 rows=4275 width=19) (actual time=16022.10..16022.10 > rows=0 loops=1) > Total runtime: 16022.19 msec ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
