From: Greg Smith <greg@2ndQuadrant.com>
To: Mike McCann <mccann@mbari.org>
Cc: pgsql-performance@postgresql.org, Yuri Levinsky <yuril@celltick.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Julien Cigar <jcigar@ulb.ac.be>
Cc: Arjen van der Meijden <acmmailing@tweakers.net>
Cc: Jeff Janes <jeff.janes@gmail.com>
Subject: Re: Hardware suggestions for maximum read performance
Date: Sun, 19 May 2013 22:44:16 -0400
Message-ID: <51998E00.9010704@2ndQuadrant.com> (raw)
In-Reply-To: <44A45613-5E99-4E61-975E-48F957B971FC@mbari.org>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com, mccann@mbari.org, yuril@celltick.com, scott.marlowe@gmail.com, jcigar@ulb.ac.be, acmmailing@tweakers.net, jeff.janes@gmail.com
Subject: Re: Hardware suggestions for maximum read performance
In-Reply-To: <51998E00.9010704@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox