pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Merlin Moncure <mmoncure@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Melvin Davidson <melvin6925@gmail.com>
Cc: Ravi Tammineni <rtammineni@partner.aligntech.com>
Cc: Chris Mair <chris@1006.org>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: Queries are taking way longer in 9.6 than 9.5
Date: Thu, 19 Jan 2017 08:28:40 +0100
Message-ID: <bf32da61-da56-d400-9537-d3fc01bac07b@2ndquadrant.com> (raw)
In-Reply-To: <CAHyXU0wB9WUWEnd7hyLCTpVbDEGjJ0vVhrPz2BbPYagV0Y-k7Q@mail.gmail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>

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



view thread (19+ messages)  latest in thread

Message-ID: <bf32da61-da56-d400-9537-d3fc01bac07b@2ndquadrant.com>
Permalink:  ../bf32da61-da56-d400-9537-d3fc01bac07b@2ndquadrant.com/
Also on:    postgresql.org/message-id/bf32da61-da56-d400-9537-d3fc01bac07b@2ndquadrant.com

 · 

reply
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-admin@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, mmoncure@gmail.com, adrian.klaver@aklaver.com, melvin6925@gmail.com, rtammineni@partner.aligntech.com, chris@1006.org, pgsql-general@postgresql.org
  Subject: Re: Queries are taking way longer in 9.6 than 9.5
  In-Reply-To: <bf32da61-da56-d400-9537-d3fc01bac07b@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