Re: Hardware suggestions for maximum read performance

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.

> I'm wondering now what changes might get this query to run in less than
> one second.  If all the data is in memory, then will faster CPU and
> memory be the things that help?

You're trying to fix a fundamental design issue with hardware.  That
usually doesn't go well.  Once you get a box big enough to hold the
whole database in RAM, beyond that the differences between server
systems are relatively small.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com