References: <5416B396.1080200@fnb.co.za> 
Message-ID: <1410787544.68356.YahooMailNeo@web122306.mail.ne1.yahoo.com>
Date: Mon, 15 Sep 2014 06:25:44 -0700
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Strange performance problem with query
To: "Van Der Berg, Stefan" <SvanderBerg@fnb.co.za>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <5416B396.1080200@fnb.co.za>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

"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