Re: indexing and LIKE

On Thu, 11 Oct 2001, Patrik Kudo wrote:

> Hi!
>
> If I want to be able to search for stringmatches using LIKE, doing
> something like the following:
>
> select id, name from table1 where lower(name) like 'somestring%';
>
> Actually I will be joining with some other table on id too, but the join
> will produce a substancial amount of data to be filtered with the LIKE
> clause so I figure if it'd be possible to index on lower(name) somehow,
> it would result in an appreciated speed gain.

You can make functional indexes:
create index <name> on table(lower(<col>));

If you're running in a locale other than C however I don't think postgres
will use it in any case in 7.1 and earlier (I'm not sure about 7.2)

> what type of index and using what syntax? Is it possible to create a
> multicolumn index on both id and name? Both id and name are of type
> "text".

Yes, you can do a multicolumn index, but if you want an id and lower(name)
index, it's a little more complicated and probably wouldn't do what you
want (I think the functional indexes are limited to a single function with
only column references as parameter).