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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com