Re: Hardware suggestions for maximum read performance

On Sun, May 19, 2013 at 8:44 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 5/13/13 6:36 PM, Mike McCann wrote:
>>
>> stoqs_march2013_s=# explain analyze select * from
>> stoqs_measuredparameter order by datavalue;
>>
>> QUERY PLAN
>>
>> ------------------------------------------------------------------------------------------------------------------------------------------
>> Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual
>> time=2503.078..2937.130 rows=3381814 loops=1)
>> Sort Key: datavalue
>> Sort Method: quicksort Memory: 362509kB
>> -> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14
>> rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814
>> loops=1)
>> Total runtime: 3094.601 ms
>> (5 rows)
>>
>> I tried changing random_page_cost to from 4 to 1 and saw no change.
>
>
> Have you tried putting an index by datavalue on this table? Once you've
> done that, then changing random_page_cost will make using that index look
> less expensive. Sorting chews through a good bit of CPU time, and that's
> where all of your runtime is being spent at--once you increase work_mem up
> very high that is.

This++ plus cluster on that index if you can.