pá 30. 11. 2018 v 15:37 odesílatel Sanyo Moura <sanyo.mo...@tatic.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.mo...@tatic.net> > escreveu: > >> Em qua, 28 de nov de 2018 às 22:40, Justin Pryzby <pry...@telsasoft.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.mo...@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. >>> >> >> 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 >>> >>
