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