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

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.

Regards,

Sanyo Moura

Em qua, 28 de nov de 2018 às 23:01, Sanyo Moura <sanyo(dot)moura(at)tatic(dot)net>
escreveu:

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