Indexs and prolems with.

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