Date: Wed, 28 Nov 2018 18:40:19 -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: <20181129004019.GA6805@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:
> On Wed, 28 Nov 2018 at 03:16, Sanyo Moura <sanyo.moura@tatic.net> wrote:
> > 11.0
> > Planning Time: 7.238 ms
> > Planning Time: 2.638 ms
> >
> > 11.5
> > Planning Time: 15138.533 ms
> > Execution Time: 2.310 ms
> 
> Does it still take that long after running ANALYZE on the partitioned table?

Note, I'm sure 11.5 was meant to say 11.1.

Also note this earlier message indicates that "high partitions" tests were with
just 10.6 and 11.1, and that times under 11.0 weren't a useful datapoint:

On Tue, Nov 27, 2018 at 11:36:09PM -0200, Sanyo Moura wrote:
> However, in the test I did in version 11.0, "Precio" is partitioned into
> only 21 partitions.

I reduced the query a bit further:

|postgres=# explain SELECT m-n FROM (SELECT a.i2-b.i2 n FROM partbench a, partbench b WHERE a.i2=b.i2) x, (SELECT max(partbench.i2) m FROM partbench)y WHERE m=n;
|Time: 35182.536 ms (00:35.183)

I should have said, that's with only 1k partitions, not 10k as you used in
June.

I also tried doing what the query seems to be aiming for by using a window
function, but that also experiences 30+ sec planning time:

|explain SELECT rank() OVER(ORDER BY var) AS k FROM (SELECT p.plusalesprice-q.plusalesprice as var from precio p, precio q ) l_variacao
|Time: 34173.401 ms (00:34.173)

Justin