Re: indexing and LIKE - Mailing list pgsql-sql

Hi

Thanks for your respons, but I'm afraid it didn't help. I've succeeded
with indexing my table using functional indices, but the problem is that
the index I create won't work with my query. Let me illustrate:

kudo=# select version();                          version
--------------------------------------------------------------PostgreSQL 7.1.3 on i386--freebsd4.3, compiled by GCC
2.95.3
(1 row)

kudo=# create index person_lower_lname_idx on person (lower(last_name));
CREATE
kudo=# vacuum analyze person;
VACUUM
kudo=# explain select userid, first_name, last_name from person where lower(last_name) like 'kud%';
NOTICE:  QUERY PLAN:

Seq Scan on person  (cost=0.00..217.44 rows=70 width=36)

EXPLAIN
kudo=# explain select userid, first_name, last_name from person where lower(last_name) = 'kudo';
NOTICE:  QUERY PLAN:

Index Scan using person_lower_lname_idx on person  (cost=0.00..57.82 rows=70 width=36)

As you can see, the functional index is working fine when using the
"normal" = operator. However, it is not used when using the "like"
operator, which I need. I understand that a pattern-matched query probably
can't be made as effective as a query with =, but I think it, at least
theoretically, should be possible to use a btree-index to find matches in
the first query above.

Am I totaly wrong here? What is possible/impossible with Postgres?

Regards,
Patrik Kudo

On Thu, 11 Oct 2001, Ross J. Reedstrom wrote:

> On Thu, Oct 11, 2001 at 02:28:34PM +0200, 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.
> >
> > Is it at all possible to create an index on lower(name), and in that case,
> > 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".
>
> Checking the short help from CREATE INDEX:
>
> template1=# \h create index
> Command:     CREATE INDEX
> Description: Constructs a secondary index
> Syntax:
> CREATE [ UNIQUE ] INDEX index_name ON table
>     [ USING acc_name ] ( column [ ops_name ] [, ...] )
> CREATE [ UNIQUE ] INDEX index_name ON table
>     [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
>
> template1=#
>
> So, you want something like:
>
> CREATE INDEX table1_l_name_idx ON table1 (lower(name));
>
> Multicolumn indices are seldom as useful as you may think at first.
> And I don't think you can combine them with functional indices.
>
> Ross
> --
> Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
> Executive Director                                  phone: 713-348-6166
> Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
> Rice University MS-39
> Houston, TX 77005
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>