Re: Hardware suggestions for maximum read performance

On Sun, May 19, 2013 at 8:44 PM, Greg Smith <greg@2ndquadrant.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.