Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52173=pgsql-performance-ar@postgresql.org>)
 id 1XTmbw-0005cc-4k
 for pgsql-performance@arkaria.postgresql.org; Tue, 16 Sep 2014 06:51:36 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52173=pgsql-performance-ar@postgresql.org>)
 id 1XTmbv-0005WJ-Kr
 for pgsql-performance@arkaria.postgresql.org; Tue, 16 Sep 2014 06:51:35 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <SvanderBerg@fnb.co.za>)
 id 1XTmbu-0005WC-Ca
 for pgsql-performance@postgresql.org; Tue, 16 Sep 2014 06:51:34 +0000
Received: from rbgcon01.fnb.co.za ([196.11.133.170])
 by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <SvanderBerg@fnb.co.za>)
 id 1XTmbi-0000dL-Mj
 for pgsql-performance@postgresql.org; Tue, 16 Sep 2014 06:51:25 +0000
Received: from fnbwsdssblsmpt1.fnb.co.za (Not Verified[10.33.31.205]) by
 rbgcon01.fnb.co.za
 id <B5417dddc0004>; Tue, 16 Sep 2014 08:51:08 +0200
Received: from FRGR3HT02.services.firstrand.net ([10.33.38.132]) by
 fnbwsdssblsmpt1.fnb.co.za with Microsoft SMTPSVC(6.0.3790.4675);
 Tue, 16 Sep 2014 08:51:09 +0200
Received: from FRGR1D2MX06.services.firstrand.net
 ([fe80::697c:e3d2:a80d:3ad1]) by FRGR3HT02.services.firstrand.net
 ([fe80::61c9:322e:277:1cbe%12]) with mapi id 14.03.0158.001; Tue, 16 Sep 2014
 08:51:08 +0200
From: "Van Der Berg, Stefan" <SvanderBerg@fnb.co.za>
To: Kevin Grittner <kgrittn@ymail.com>, "pgsql-performance@postgresql.org"
 <pgsql-performance@postgresql.org>
Subject: Re: Strange performance problem with query
Thread-Topic: [PERFORM] Strange performance problem with query
Thread-Index: AQHP0MjP7/OiQ+FeB0WQIIZgU48jTpwCDgIAgAEkE4A=
Date: Tue, 16 Sep 2014 06:51:07 +0000
Message-ID: <5417DDDB.4000600@fnb.co.za>
References: <5416B396.1080200@fnb.co.za>
 <1410787544.68356.YahooMailNeo@web122306.mail.ne1.yahoo.com>
In-Reply-To: <1410787544.68356.YahooMailNeo@web122306.mail.ne1.yahoo.com>
Accept-Language: en-US, en-ZA
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
user-agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101
 Thunderbird/24.1.0
x-originating-ip: [10.204.21.137]
Content-Type: text/plain; charset="iso-8859-1"
Content-ID: <8520DFD7B068864DB5D2151C4B70399A@services.firstrand.net>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginalArrivalTime: 16 Sep 2014 06:51:09.0077 (UTC)
 FILETIME=[9870AC50:01CFD17A]
X-Pg-Spam-Score: -4.2 (----)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi Kevin,

Thanks for the advice.

I opted for setting the random_page_cost a bit lower, as that made the=20
most sense in the context of the current setup where there is quite a=20
high cache hit ratio. Is 97% high enough?:

=3D# SELECT
     'cache hit rate' AS name,
      sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS=20
ratio
FROM pg_statio_user_tables;
       name      |         ratio
----------------+------------------------
  cache hit rate | 0.97344836172381212996

When I set the random_page_cost down from 4 to 2, the query plan changes=20
to the faster one.

Kind Regards
Stefan

Cell : 072-380-1479
Desk : 087-577-7241

On 2014/09/15 03:25 PM, Kevin Grittner wrote:
> "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
To read FirstRand Bank's Disclaimer for this email click on the following a=
ddress or copy into your Internet browser:=20
https://www.fnb.co.za/disclaimer.html=20

If you are unable to access the Disclaimer, send a blank e-mail to
firstrandbankdisclaimer@fnb.co.za and we will send you a copy of the Discla=
imer.


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