Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

Em qua, 28 de nov de 2018 às 22:40, Justin Pryzby <pryzby(at)telsasoft(dot)com>
escreveu:

> On Wed, Nov 28, 2018 at 05:03:15PM +1300, David Rowley wrote:
> > On Wed, 28 Nov 2018 at 03:16, Sanyo Moura <sanyo(dot)moura(at)tatic(dot)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.
>

Yeah, 11.1, sorry for mistake.

>
> 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:
>

That's true, at 11.0 version I had tested with only 21 partitions because
by this time I didn't have
realized that it was an issue with a huge number of partitions.
In both versions 10.6 and 11.1 I have tested with 730 partitions each
(2 years of data partitioned by day).

Sanyo

>
> 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
>