From: Gunnar \"Nick\" Bluth <gunnar.bluth@pro-open.de>
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Cc: corrado@ficicchia.net
Subject: Re: Strange sort node/explain result
Date: Tue, 1 Nov 2022 14:13:38 +0100
Message-ID: <e7781c7f-99ba-ef25-e764-16eaf5e39de3@pro-open.de> (raw)
In-Reply-To: <CAApHDvpg-kS3QJCgZ63O7PrHE=x4nyb_zT5j-tay=L4mg5Lg7g@mail.gmail.com>
References: <587a301e-478d-1cbc-288f-6ea77086b097@pro-open.de>
<CAApHDvpg-kS3QJCgZ63O7PrHE=x4nyb_zT5j-tay=L4mg5Lg7g@mail.gmail.com>
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
Attachments:
OpenPGP_signature (application/pgp-signature, 494B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: gunnar.bluth@pro-open.de, dgrowleyml@gmail.com, pgsql-bugs@lists.postgresql.org, corrado@ficicchia.net
Subject: Re: Strange sort node/explain result
In-Reply-To: <e7781c7f-99ba-ef25-e764-16eaf5e39de3@pro-open.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox