citext like query and index usage - Mailing list 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