On Mon, May 13, 2013 at 3:36 PM, Mike McCann <mccann@xxxxxxxxx> wrote:
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.
Increasing work_mem to 355 MB improves the performance by a factor of 2:stoqs_march2013_s=# set work_mem='355MB';SETstoqs_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: datavalueSort 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.
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.
Also, what is the type and collation of the column you are sorting on? non-'C' collations of text columns sort about 3 times slower than 'C' collation does.
If all the data is in memory, then will faster CPU and memory be the things that help?
Yes, those would help (it is not clear to me which of the two would help more), but I think you need to rethink your design of sending the entire database table to the application server for each page-view.
Cheers,
Jeff
