Re: Functional Indices

On Mon, 21 May 2001, kavoos wrote:

> Hi all,
>
>
> The pg manual, chapter 7 :
> "For example, a common way to do case-insensitive comparisons is to use
> the lower: SELECT * FROM test1 WHERE lower(col1) = 'value';
> In order for that query to be able to use an index, it has to be defined
> on the result of the lower(column) operation:
> CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));"
>
> I have a table like this :
> \d titles
>                                   Table "titles"
>  Attribute |          Type          |                   Modifier
> -----------+------------------------+----------------------------------------------
>  id        | integer                | not null default
> nextval('titles_seq'::text)
>  issn      | character(9)           | not null
>  tag       | integer                | not null
>  prefix    | character varying(32)  |
>  title     | character varying(640) | not null
> Indices: issn,
>          prefix,
>          tag,
>
>
> create index lower_title on titles (lower(title));
> vacuum analyze;
> ...
> 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?