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 >
