Re: Slow select

On Wed, Dec 16, 2009 at 05:18:12PM -0800, yuliada wrote:
> Sam Mason wrote:
> > How about combining all 1000 selects into one?
>
> I can't combine these selects into one, I need to run them one after
> another.

Hum, difficult. What other information is in the row that you need
back? Can you turn the table structure around somehow so that the
"value" is the primary key and hence only a single row needs to be found
each time.

Other than that, I think you just need faster disks.

> "Bitmap Heap Scan on bn_stringvalue v (cost=228.40..8688.70 rows=2172 width=90) (actual time=1129.767..1781.403 rows=104 loops=1)"
> " Recheck Cond: (lower((value)::text) = 'esr'::text)"
> " -> Bitmap Index Scan on idx_stringv (cost=0.00..227.86 rows=2172 width=0) (actual time=1107.974..1107.974 rows=104 loops=1)"
> " Index Cond: (lower((value)::text) = 'esr'::text)"
> "Total runtime: 1781.566 ms"

It looks like it's doing reasonable things. I assume you've got a
single disk servicing this, 1781 / (104*2) = 8ms average seek time.

Clustering on "value" may help, but it's going to take a while. Its
value depends on how common this operation is compared to other ones.

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