From: Patrik Kudo <kudo@partitur.se>
To: Ross J. Reedstrom <reedstrm@rice.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: indexing and LIKE
Date: Fri, 12 Oct 2001 10:16:33 +0200 (CEST)
Message-ID: <20011012100747.I99068-100000@tb303.partitur.se> (raw)
In-Reply-To: <20011011150526.A22528@rice.edu>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kudo@partitur.se, reedstrm@rice.edu
Subject: Re: indexing and LIKE
In-Reply-To: <20011012100747.I99068-100000@tb303.partitur.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox