Re: Strange sort node/explain result

Am 31.10.22 um 21:40 schrieb David Rowley:
> On Tue, 1 Nov 2022 at 03:20, Gunnar "Nick" Bluth
> <gunnar(dot)bluth(at)pro-open(dot)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(dot)bluth(at)pro-open(dot)de
__________________________________________________________________________
"Ceterum censeo SystemD esse delendam" - Cato