Re: Another seq scan instead of index question

Nicholas Piper <nick@nickpiper.co.uk> writes:
> There are 4210874 rows, which is a lot compared to the expected rows
> returned, so why does it still use seq scan ?

Well, no, it isn't "a lot".  The row estimate is just about 1% of the
total rows, which suggests strongly that you're getting a default
selectivity estimate rather than anything real.  Note also that you have
about 100 rows per disk page (4210874/41232).  So it's estimating that
it will need to fetch about one row out of every page, on which basis
the indexscan looks pretty unattractive --- it can't save any I/O.

Your real problem is the bogus selectivity estimate.  What version
are you running?  If 7.0, see contrib/likeplanning/.  If 7.1, I'd
be interested to see what you get from

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

            regards, tom lane