From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jorge Torralba <jorge.torralba@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Query Plan difference between 9.3 and 9.5 for the worse. Help !!!
Date: Wed, 28 Sep 2016 18:28:09 -0400
Message-ID: <31745.1475101689@sss.pgh.pa.us> (raw)
In-Reply-To: <CACut7uQajVzTL9R-9=qs1B69+yiGJxUBs4B0=OHk1GOBWwwwwg@mail.gmail.com>
References: <CACut7uQajVzTL9R-9=qs1B69+yiGJxUBs4B0=OHk1GOBWwwwwg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Jorge Torralba <jorge.torralba@gmail.com> writes:
> We have been struggling with a query on our system that under 9.3 was
> taking 15ms or so to run and after upgrading to 9.5 it is taking over
> 2500ms.
> You can see the explains are very different but I cannot figure out why
> when using the same data on both system.
Hard to comment on this when you haven't shown us the query nor the
table schemas. But I'm going to guess based on the lack of a Limit
node in the second plan that your query involves WHERE EXISTS(SELECT
... LIMIT 1). The LIMIT 1 would have defeated optimization till recently,
but now the planner knows that it's a no-op in this context and throws it
away, and then is able to convert the sub-select to a semijoin. Usually
that results in a better plan; there's far from enough info here to
guess why it's worse for you.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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: tgl@sss.pgh.pa.us, jorge.torralba@gmail.com
Subject: Re: Query Plan difference between 9.3 and 9.5 for the worse. Help !!!
In-Reply-To: <31745.1475101689@sss.pgh.pa.us>
* 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