Re: Slow select

On Wed, Dec 16, 2009 at 04:56:16AM -0800, yuliada wrote:
> I have a table with column of character varying(100). There are about
> 150.000.000 rows in a table. Index was created as
>
> CREATE INDEX idx_stringv
>   ON bn_stringvalue
>   USING btree
>   (lower(value::text));
>
> I'm trying to execute queries like 'select * from stringvalue where
> value=lower(?)'.

Wouldn't this be "lower(value) = lower(?)" ?

> Making 1000 selects takes about 4-5 min.

So each query is taking approx 300ms?  How much data does each one
return?

> I did vacuum and
> analyze on this table and checked that query plan uses index. What can I do
> to make it faster?

How about combining all 1000 selects into one?  Maybe something like:

  SELECT * FROM stringvalue
  WHERE lower(value) = ANY (ARRAY ['a','b','c']);

--
  Sam  http://samason.me.uk/