Re: Query optimizer bug

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