From: | Tore Halvorsen <tore(dot)halvorsen(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | citext like query and index usage |
Date: | 2009-09-22 10:11:46 |
Message-ID: | 50e97fa70909220311o63d69facod46e5c8348efeef2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Is it possible to use an index for like queries on a citext column?
I'm using pg 8.4.1 on windows - with no changes to the default configuration.
For example:
CREATE TABLE test ( citext citext NOT NULL );
INSERT INTO test select md5(random()::text) FROM generate_series(0, 1000000, 1);
CREATE INDEX test_citext_idx ON test USING btree(citext);
vacuum analyze test;
explain analyze select * from test where citext like '5555%'
...
Seq Scan on test (cost=0.00..20834.03 rows=5000 width=33)
(actual time=45.916..3691.540 rows=16 loops=1)
Filter: (citext ~~ '5555%'::citext)
Total runtime: 3691.676 ms
set enable_seqscan = off;
explain analyze select * from test where citext like '5555%'
...
Seq Scan on test (cost=10000000000.00..10000020834.03 rows=5000 width=33)
(actual time=45.578..3761.687 rows=16 loops=1)
Filter: (citext ~~ '5555%'::citext)
Total runtime: 3761.860 ms
With equal I'm getting an index scan
explain analyze select * from test where citext =
'55559cb65689f035766eb69ed615afd4'
Index Scan using test_citext_idx on test (cost=0.00..8.56 rows=1 width=33)
(actual time=0.452..0.462 rows=1 loops=1)
Index Cond: (citext = '55559cb65689f035766eb69ed615afd4'::citext)
Total runtime: 0.558 ms
So, is there any way to get the like queries to use the index?
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2009 Tore Halvorsen || +052 0553034554
From | Date | Subject | |
---|---|---|---|
Next Message | Arnold, Sandra | 2009-09-22 13:10:10 | PLJava not installed for PostgreSQL 8.4 on Solaris 10 x86 64 |
Previous Message | agostonbejo | 2009-09-22 10:10:13 | How to have ant's <sql> task insert special chars appropriately? |