Message-ID: <51998E00.9010704@2ndQuadrant.com>
Date: Sun, 19 May 2013 22:44:16 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7;
	rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
To: Mike McCann <mccann@mbari.org>
CC: pgsql-performance@postgresql.org, Yuri Levinsky <yuril@celltick.com>,
	Scott Marlowe <scott.marlowe@gmail.com>, Julien Cigar <jcigar@ulb.ac.be>,
	Arjen van der Meijden <acmmailing@tweakers.net>,
	Jeff Janes <jeff.janes@gmail.com>
Subject: Re: Hardware suggestions for maximum read performance
References: <3C9B31A5-F8FE-4161-A374-971D5DF69E8A@mbari.org>
	<CAOR=d=2_unb+0YROVJtj5f67s1RMeMhCBb7ij0vwsSgcdfQYxg@mail.gmail.com>
	<CAMkU=1xrdZkV3A2NH3F-uYrO8XpQAQ7UX6sfcMQP33JT7A3gCw@mail.gmail.com>
	<44A45613-5E99-4E61-975E-48F957B971FC@mbari.org>
In-Reply-To: <44A45613-5E99-4E61-975E-48F957B971FC@mbari.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance