Re: Arg! PG not using index on an analyzed table

On Wed, 2 Feb 2005, Josh Berkus wrote:

> Stephen,
>
> > You may also be able to get away with another index on the column using
> > text_pattern_ops as the operator class if you need/want other en_US
> > features.
>
> Huh. Can you give me an example?

template1=# show LC_COLLATE;
lc_collate
------------
en_US
(1 row)

template1=# create table test(a text);
CREATE TABLE
template1=# create index testindex1 on test(a);
CREATE INDEX

-- fill table in here

template1=# explain select * from test where a='a';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using testindex1 on test (cost=0.00..814.09 rows=264
width=32)
Index Cond: (a = 'a'::text)
(2 rows)

template1=# explain select * from test where a ~ '^a';
QUERY PLAN
----------------------------------------------------------
Seq Scan on test (cost=0.00..1136.80 rows=276 width=32)
Filter: (a ~ '^a'::text)
(2 rows)

template1=# create index testindex2 on test(a text_pattern_ops);
CREATE INDEX
template1=# explain select * from test where a ~ '^a';
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using testindex2 on test (cost=0.00..294.85 rows=79 width=32)
Index Cond: ((a ~>=~ 'a'::text) AND (a ~<~ 'b'::text))
Filter: (a ~ '^a'::text)
(3 rows)