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
