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
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 |