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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

pá 30. 11. 2018 v 15:37 odesílatel Sanyo Moura <sanyo.moura@xxxxxxxxx> napsal:

Hello again,

At the moment, I've got a palliative solution that has significantly reduced my planning time.

What I did was nest the partitions by creating sub partitions. 

That way, my 730 partitions (2 years of data) were partitioned first in 2 years,

 and each partitioned year in 12 months. 

In turn, each month received the partitions per corresponding day.

That way, the planner needs to go through far fewer partitions to execute the plan.

My planning time has dramatically reduced from 15s to 150ms.

good to know it.

Regards

Pavel

Regards,

Sanyo Moura

Em qua, 28 de nov de 2018 às 23:01, Sanyo Moura <sanyo.moura@xxxxxxxxx> escreveu:

Em qua, 28 de nov de 2018 às 22:40, Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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.moura@xxxxxxxxx> 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