Re: Indexs and prolems with.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Michael McAlpine" <mikem(at)vis(dot)oregonian(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Indexs and prolems with.
Date: 2002-02-06 20:50:27
Message-ID: 8607.1013028627@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Michael McAlpine" <mikem(at)vis(dot)oregonian(dot)com> writes:
> 1) explain select * from table1 where charcol1 = 'asdfasd'
> Index Scan using idx_charcol1 on table1 (cost=0.00..5.00 rows=1 width=456)

> 4) explain select * from table1 where charcol2 = 'asdfasd'
> Seq Scan on table1 (cost=0.00..234872.84 rows=168205 width=456)

Notice the difference in the estimated number of matching rows ---
that's what's persuading the planner that an indexscan would be a bad idea.
Why is that? Is charcol2 full of lots of duplicates?

It'd be useful to know what version you are using and what you get from
(assuming 7.1)

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 = 'table1';

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message greg 2002-02-06 20:56:04 Re: Commit , Rollback
Previous Message Sykora, Dale 2002-02-06 20:30:48 Re: Postal code radius searches