Re: [SQL] Index selection on a large table

"Michael Richards" <michael(at)fastmail(dot)ca> writes:
> The following query yeilds the questionable query plan:

> explain select * from logins where ip='38.30.141.44';
> NOTICE: QUERY PLAN:
> Seq Scan on logins (cost=0.00..25248.51 rows=11602 width=48)

If there really were 11602 matching rows, the seq scan likely would be
the way to go. So the issue here is poor selectivity estimation.

You said you did a VACUUM, but you didn't mention if you'd ever done
a VACUUM ANALYZE. That looks to me like it's using the default 1%
selectivity estimate, which is what you'd get if you hadn't ever
done an ANALYZE.

If you have done one, what do you get from

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'logins';

?

regards, tom lane