Re: optimizer

On Wed, 27 Feb 2002, Zhang, Anna wrote:

> Hi,
> I have a table named Domain that has 14M rows, here is the defination:
>
> xdap_regr=# \d domain
> Table "domain"
> Attribute | Type | Modifier
> -----------------+-------+----------
> domainhandle | text | not null
> domainname | text | not null
> parentdomain | text | not null
> holdername | text |
> street | text |
> city | text |
> state | text |
> postalcode | text |
> country | text |
> Indices: idx_domain_domainname,
> idx_domain_holdername,
> idx_domain_parentdoamin,
> pk_domain
>
> An index on holdname has been created and the table analized.
>
> xdap_regr=# explain select domainname from domain where holdername =
> 'NAMEZERO.COM';
> NOTICE: QUERY PLAN:
>
> Seq Scan on domain (cost=0.00..483192.89 rows=1532369 width=12)

Is the estimate above (1.5M rows) reasonable? If so, it's probably
doing the right thing. If not, what version are you using and are
there any very common values that may throw off the estimates; what
does select * from pg_statistic where starelid=(select oid from
pg_class where relname='domain'); give?

> Can we force optimizer to index scan?
set enable_seqscan=off;