Subject: Re: Queries are taking way longer in 9.6 than 9.5
To: Merlin Moncure <mmoncure@gmail.com>,
 Adrian Klaver <adrian.klaver@aklaver.com>
References: 
 <MWHPR02MB2655591A4E27B425FABD5E93807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
 <a0680eb9d03df0f54690eb2a7351744e@smtp.hushmail.com>
 <MWHPR02MB265508B2200BD5A17DDE5DA3807C0@MWHPR02MB2655.namprd02.prod.outlook.com>
 <70190c66-cf64-4741-1cd7-a6cf08f4101f@2ndquadrant.com>
 <dfa066c765ed68b8564a30322467e6dc@smtp.hushmail.com>
 <MWHPR02MB2655A38548EDFA9C874C5EDF807F0@MWHPR02MB2655.namprd02.prod.outlook.com>
 <CAHyXU0xSSy0C-oZ9KFWJ0SK0v7NNNqTZ0A1whguaDLAAKNEmxA@mail.gmail.com>
 <CANu8Fiy0Ln9a8XJ+DH2NNKXAL6sLadhQGQbS_bGVZkrPaX77qg@mail.gmail.com>
 <CAHyXU0yUtRHu_uPoK+aqinzF+b1MPoWLVUoDqeThi-_tUv7TSw@mail.gmail.com>
 <e1c2aa5f-f85f-5644-b1c1-606cb907dc76@aklaver.com>
 <CAHyXU0wB9WUWEnd7hyLCTpVbDEGjJ0vVhrPz2BbPYagV0Y-k7Q@mail.gmail.com>
Cc: Melvin Davidson <melvin6925@gmail.com>,
 Ravi Tammineni <rtammineni@partner.aligntech.com>,
 Chris Mair <chris@1006.org>,
 "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <bf32da61-da56-d400-9537-d3fc01bac07b@2ndquadrant.com>
Date: Thu, 19 Jan 2017 08:28:40 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAHyXU0wB9WUWEnd7hyLCTpVbDEGjJ0vVhrPz2BbPYagV0Y-k7Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

On 01/19/2017 06:21 AM, Merlin Moncure wrote:
 >
...
>
> yes.  Either way, I would like to very much understand how server is
> preferring 3m cost plan to 2.6k cost plan or is otherwise unable to
> access the cheap plan.
>

Perhaps it doesn't even see the 2.6k cost - it may not generate the plan 
for some reason, or it arrives to different estimates.

The OP was repeatedly asked for details of the configuration, I've even 
sent a query for doing that a few days back. It's difficult to help when 
such basic info is not provided, though.

It'd also be interesting to see the statistics for the tables involved, 
so something like

    SELECT * FROM pg_stats WHERE tablename IN (... tables ...)

and

    SELECT relname, relpages, reltuples, relallvisible
      FROM pg_class WHERE relame IN (... tables ...)

might shed some light on what the planner assumes about the data.

Of course, there are other things we might need to know. For example if 
there are foreign keys between the tables, 9.6 is using that for the 
estimates (unlike 9.5). And so on.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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