Re: Hardware suggestions for maximum read performance - Mailing list pgsql-performance

Increasing work_mem to 355 MB improves the performance by a factor of 2:

stoqs_march2013_s=# set work_mem='355MB';

SET

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.

I'm wondering now what changes might get this query to run in less than one second.  

I think you are worrying about the wrong thing here.  What is a web app going to do with 3,381,814 rows, once it obtains them?  Your current testing is not testing the time it takes to stream that data to the client, or for the client to do something meaningful with that data.

If you only plan to actually fetch a few dozen of those rows, then you probably need to incorporate that into your test, either by using a LIMIT, or by using a mock-up of the actual application to do some timings.

If all the data is in memory, then will faster CPU and memory be the things that help?