Can LIKE use indexes or not?

From: David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Can LIKE use indexes or not?
Date: 2004-02-05 08:30:07
Message-ID: 4021FF0F.9090108@zara.6.isreserved.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Reading the archives and the FAQ, it seems to be implied that LIKE can
use index (and ILIKE can't; so to do case-insensitive search you need to
create a functional index on LOWER(field) and say: LOWER(field) LIKE
'foo%').

However, EXPLAIN always says seq scan for the test data I'm using. I've
done 'set enable_seqscan to off' and it still says seq scan. I was
curious as to how the index will help this query:

db1=> set enable_seqscan to off;
SET
Time: 5.732 ms
db1=> explain select * from t where f like 'xx%';
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on t (cost=100000000.00..100002698.90 rows=89 width=14)
Filter: (f ~~ 'xx%'::text)
(2 rows)

db1=> explain select * from t where lower(f) like 'xx%';
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on t (cost=100000000.00..100002893.68 rows=390 width=14)
Filter: (lower(f) ~~ 'xx%'::text)
(2 rows)

The table is:

db1=> \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
f | text |
Indexes:
"i1" unique, btree (lower(f))
"i2" unique, btree (f)

It contains +- 250k rows of totally random 10-char-long strings
(containing upper- & lowercase letters and numbers). Here's how the LIKE
performs:

db1=> select * from t where f like 'xx%';
f
------------
xxEqfLZMkH
xxBRRnLetJ
...
xxFPYJEiYf
(98 rows)

Time: 452.613 ms

Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?

db1=> select * from t where lower(f) like 'mmm%';
f
------------
MmmyEVmfSY
MMmzolhHtq
...
mMMWEQzlKm
(16 rows)

Time: 634.470 ms

--
dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kris Jurka 2004-02-05 08:41:06 Re: Improving performance with a Function instead of a
Previous Message John Sidney-Woollett 2004-02-05 07:49:02 Re: dblink: rollback transaction