From: Kevin Grittner <kgrittn@ymail.com>
To: Van Der Berg, Stefan <SvanderBerg@fnb.co.za>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Strange performance problem with query
Date: Mon, 15 Sep 2014 06:25:44 -0700
Message-ID: <1410787544.68356.YahooMailNeo@web122306.mail.ne1.yahoo.com> (raw)
In-Reply-To: <5416B396.1080200@fnb.co.za>
References: <5416B396.1080200@fnb.co.za>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"Van Der Berg, Stefan" <SvanderBerg@fnb.co.za> wrote:
> I get a similar plan selected on the original query if I set
> enable_seqscan to off. I much prefer the second result.
> My questions are:
> 1. Why is this happening?
Your cost factors don't accurately model actual costs.
> 2. How can I encourage the behavior of the second query without
> changing the original query?
You didn't give enough information to really give solid advice, but
when people see what you are seeing, some common tuning needed is:
Set shared_buffers to about 25% of system RAM or 8GB, whichever is
lower.
Set effective_cache_size to 50% to 75% of system RAM.
Set work_mem to about 25% of system RAM divided by max_connections.
If you have a high cache hit ratio (which you apparently do) reduce
random_page_cost, possibly to something near or equal to
seq_page_cost.
Increase cpu_tuple_cost, perhaps to 0.03.
You might want to play with the above, and if you still have a
problem, read this page and post with more detail:
http://wiki.postgresql.org/wiki/SlowQueryQuestions> Is there some column level setting I can set?
The statistics looked pretty accurate, so that shouldn't be
necessary.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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: kgrittn@ymail.com, SvanderBerg@fnb.co.za
Subject: Re: Strange performance problem with query
In-Reply-To: <1410787544.68356.YahooMailNeo@web122306.mail.ne1.yahoo.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