Обсуждение: Indexs and prolems with.

Greetings,

I have been having with some indexing problems and was hoping someone might have some insights.

In short.  I have  table1 with charcol1 -  char(9)  and  charcol2  -  char(7)

Here are the results and the puzzle

1)  explain select * from table1 where charcol1 = 'asdfasd'
     Index Scan using idx_charcol1  on table1  (cost=0.00..5.00 rows=1 width=456)

2)  explain select * from table1 where charcol1 like 'asdfa%'
     Index Scan using idx_charcol1 on table1  (cost=0.00..5.00 rows=1 width=456)

3)  explain select * from table1 where charcol2 like "aefsadf%'
     Index Scan using idx_charcol2 on table1  (cost=0.00..5.00 rows=1 width=456)

but when I try

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


I have the proper locale setting for like queries ("C") and have recreated the indexes and vacuum analyzed but cannot
getthe last query to use the index.   

Any help would be greatly appreciated.

Michael

The Oregonian
Portland Oregon

On Wed, 6 Feb 2002, Michael McAlpine wrote:

> Here are the results and the puzzle
>
> 1)  explain select * from table1 where charcol1 = 'asdfasd'
>      Index Scan using idx_charcol1  on table1  (cost=0.00..5.00 rows=1 width=456)
>
> 2)  explain select * from table1 where charcol1 like 'asdfa%'
>      Index Scan using idx_charcol1 on table1  (cost=0.00..5.00 rows=1 width=456)
>
> 3)  explain select * from table1 where charcol2 like "aefsadf%'
>      Index Scan using idx_charcol2 on table1  (cost=0.00..5.00 rows=1 width=456)
>
> but when I try
>
> 4)   explain select * from table1 where charcol2  = 'asdfasd'
>      Seq Scan on table1  (cost=0.00..234872.84 rows=168205 width=456)
>
>
> I have the proper locale setting for like queries ("C") and have
> recreated the indexes and vacuum analyzed but cannot get the last
> query to use the index.

How many rows are in the table?  It's estimating 168205 rows matching
asdfasd which could very possibly push it over to deciding on a sequence
scan.

"Michael McAlpine" <mikem@vis.oregonian.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