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@xxxxxxxxxxxxxxx Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
