From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Nicholas Piper <nick(at)nickpiper(dot)co(dot)uk> |
Cc: | PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Another seq scan instead of index question |
Date: | 2001-08-07 16:11:41 |
Message-ID: | 8140.997200701@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Nicholas Piper <nick(at)nickpiper(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nicholas Piper | 2001-08-07 16:20:55 | Re: Another seq scan instead of index question |
Previous Message | Tom Lane | 2001-08-07 16:00:56 | Re: Still wondering about random numbers... |