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

pá 30. 11. 2018 v 15:37 odesílatel Sanyo Moura <sanyo(dot)moura(at)tatic(dot)net>
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(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
>>>
>>