Re: Hardware suggestions for maximum read performance

On Mon, May 13, 2013 at 3:36 PM, Mike McCann <mccann(at)mbari(dot)org> wrote:

>
> 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.

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