From: Justin Pryzby <pryzby@telsasoft.com>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: sanyo.moura@tatic.net
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Date: Wed, 28 Nov 2018 18:40:19 -0600
Message-ID: <20181129004019.GA6805@telsasoft.com> (raw)
In-Reply-To: <CAKJS1f8cZUzzGQnYKSfzeEe-fscQc9s5wPy4ZNPn-Uq=pDcODw@mail.gmail.com>
References: <CAO698qZnrxoZu7MEtfiJmpmUtz3AVYFVnwzR+pqjF=rmKBTgpw@mail.gmail.com>
<CAKJS1f8cZUzzGQnYKSfzeEe-fscQc9s5wPy4ZNPn-Uq=pDcODw@mail.gmail.com>
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
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-performance@postgresql.org
Cc: pryzby@telsasoft.com, david.rowley@2ndquadrant.com, 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
In-Reply-To: <20181129004019.GA6805@telsasoft.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