Re: planning issue

On Fri, 2007-03-16 at 12:17 -0700, Joshua D. Drake wrote:
> >
> > QUERY PLAN
> > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346)
> > Filter: ((id <> 10001) AND (((field_1)::text ~~* '123'::text) OR
> > ((field_2)::text ~~* 'abc'::text)))
> >
> >
> > however, i have the following indexes:
> >
> > "table_a__pkey" PRIMARY KEY, btree (id)
> > "table_a__idx__field_1" btree (field_1)
> > "table_a__idx__field_2" btree (field_2)
> >
> > can anyone offer advice to help me use the indexes on this ?
>
> create a function lower index and instead of calling ilike call ~
> lower('123')
>

To clarify a little:

CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( lower(field_1) = '123' )
OR
( lower(field_2) = 'abc' )
)

That should be able to use your indexes correctly.

Regards,
Jeff Davis