Am 31.10.22 um 21:40 schrieb David Rowley:
> On Tue, 1 Nov 2022 at 03:20, Gunnar "Nick" Bluth
> <gunnar.bluth@pro-open.de> wrote:
>> What puzzles us is the part where the CTE "oneyear" somehow explodes
>> into a sort node of almost 10 mio (but not the same amount as the index
>> scan emits!) rows, taking ~ 0.4 seconds but only using 4x-5x kB of memory:
>>
>> -> Sort (cost=69.83..72.33 rows=1000 width=4) (actual
>> time=0.418..448.397 rows=9855001 loops=1)
>
> I think you're interpreting the EXPLAIN output wrongly. It's not that
> the Sort node emits 9855001 rows, it's that 9855001 are read from the
> Sort node.
Thanks for looking into this, David!
> The reason more rows are read from it than are produced is because
> Merge Join must perform mark and restore to "rewind" the inner side of
> the scan back for the subsequent outer tuple which has the same value.
Ok, I get it. We kind of had that suspicion.
So the sort could also say
"rows=366 loops=26926"
instead of
"rows=9855001 loops=1"
(which I myself would find reasonable...)?
< snip >
> There are only 366 rows for the Sort node to sort. 42kb seems like
> reasonable memory use for that.
Absolutely!
Thanks again,
--
Gunnar "Nick" Bluth
Eimermacherweg 106
D-48159 Münster
Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
"Ceterum censeo SystemD esse delendam" - Cato