Re: Re: Functional Indices

On Tue, 22 May 2001, mordicus wrote:

> Stephan Szabo wrote:
> >> explain select * from titles where lower(title) = 'monde';
> >> Seq Scan on titles (cost=0.00..39392.10 rows=14145 width=44)
> >
> > How many rows are in titles? It seems to estimate 14000+
> > rows are going to match. If that's true, sequence scan may
> > be a better plan than the index. Or, perhaps, do you have
> > a very common title value that's throwing off the statistics?
> >
> Hello,
>
> register=# select count(title) from titles;
> count
> ---------
> 1414473
> (1 row)
>
> I have solved the probleme by setting enable_seqscan to false and now it
> use index, but i don't understand why it choose to do a seq scan.

It was probably estimating the cost of the non-sequential reads to get
those 14000 rows to be larger than the sequential reads on the table.
Postgres still needs to go to the heap file for things that meet the
criteria in the index in order to see if the row is visible to the
current transaction.

My guess would be that:
select count(*) from titles where lower(title) = 'monde'
returns something much lower than 14000, is that correct?