Date: Tue, 27 Nov 2018 22:17:50 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: sanyo.moura@tatic.net, pgsql-performance@lists.postgresql.org
Subject: Re: Query with high planning time at version 11.1 compared versions
 10.5 and 11.0
Message-ID: <20181128041750.GH30707@telsasoft.com>
References: 
 <CAO698qZnrxoZu7MEtfiJmpmUtz3AVYFVnwzR+pqjF=rmKBTgpw@mail.gmail.com>
 <CAKJS1f8cZUzzGQnYKSfzeEe-fscQc9s5wPy4ZNPn-Uq=pDcODw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKJS1f8cZUzzGQnYKSfzeEe-fscQc9s5wPy4ZNPn-Uq=pDcODw@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Wed, Nov 28, 2018 at 05:03:15PM +1300, David Rowley wrote:
> Does it still take that long after running ANALYZE on the partitioned table?

Yes ; I've just reproduced the problem with a variation on Sanyo's query,
retrofitted onto the empty "partbench" table you used for testing in July:
https://www.postgresql.org/message-id/CAKJS1f8qkcwr2DULd%2B04rBmubHkKzp4abuFykgoPUsVM-4-38g%40mail.gmail.com

Note, Sanyo's original query appears to be a poor-man's window function,
joining two subqueries on a.value=max(b.value).

I reduced issue to this:

|postgres=# ANALYZE partbench;
|postgres=# explain SELECT * FROM (SELECT a.i2-b.i2 n FROM partbench a, (SELECT i2 FROM partbench)b)b, (SELECT max(partbench.i3) m FROM partbench, (SELECT i3 FROM partbench)y )y WHERE m=n;
|Time: 31555.582 ms (00:31.556)

Justin