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?