pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlan weirdness. A sort produces more rows than the node beneath it
7+ messages / 3 participants
[nested] [flat]
* Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 14:59 Dane Foster <studdugie@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Dane Foster @ 2023-08-04 14:59 UTC (permalink / raw)
To: pgsql-performance
Hello,
I'm trying to understand a bit of weirdness in a plan output. There is a
sort node above a sequential scan node where the scan node produces 26,026
rows yet the sort node above it produces 42,995,408. How is it possible to
sort more data than you received?
https://explain.dalibo.com/plan/1ee665h69f92chc5
The PostgreSQL version is 14.2 running on Amazon's RDS. Thanks.
Dane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 15:07 Jeff Janes <jeff.janes@gmail.com>
parent: Dane Foster <studdugie@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jeff Janes @ 2023-08-04 15:07 UTC (permalink / raw)
To: Dane Foster <studdugie@gmail.com>; +Cc: pgsql-performance
On Fri, Aug 4, 2023 at 11:00 AM Dane Foster <studdugie@gmail.com> wrote:
> Hello,
>
> I'm trying to understand a bit of weirdness in a plan output. There is a
> sort node above a sequential scan node where the scan node produces 26,026
> rows yet the sort node above it produces 42,995,408. How is it possible
> to sort more data than you received?
>
This is normal for a merge join. For every tie in the first input, the
qualifying part of the 2nd input must be rescanned, and the rows are
tallied again (in the sort node) each time they are rescanned.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 15:09 Dane Foster <studdugie@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dane Foster @ 2023-08-04 15:09 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
Thanks for the explanation.
Dane
On Fri, Aug 4, 2023 at 11:07 AM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Fri, Aug 4, 2023 at 11:00 AM Dane Foster <studdugie@gmail.com> wrote:
>
>> Hello,
>>
>> I'm trying to understand a bit of weirdness in a plan output. There is a
>> sort node above a sequential scan node where the scan node produces 26,026
>> rows yet the sort node above it produces 42,995,408. How is it possible
>> to sort more data than you received?
>>
>
> This is normal for a merge join. For every tie in the first input, the
> qualifying part of the 2nd input must be rescanned, and the rows are
> tallied again (in the sort node) each time they are rescanned.
>
> Cheers,
>
> Jeff
>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dane Foster <studdugie@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-08-04 15:10 UTC (permalink / raw)
To: Dane Foster <studdugie@gmail.com>; +Cc: pgsql-performance
Dane Foster <studdugie@gmail.com> writes:
> I'm trying to understand a bit of weirdness in a plan output. There is a
> sort node above a sequential scan node where the scan node produces 26,026
> rows yet the sort node above it produces 42,995,408. How is it possible to
> sort more data than you received?
If the sort is the inner input to a merge join, this could reflect
mark-and-restore rescanning of the sort's output. Are there a
whole lot of duplicate keys on the merge's other side?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 15:15 Dane Foster <studdugie@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Dane Foster @ 2023-08-04 15:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> If the sort is the inner input to a merge join, this could reflect
> mark-and-restore rescanning of the sort's output. Are there a
> whole lot of duplicate keys on the merge's other side?
Yes. The course_id column's values repeat a LOT on the merge side.
Dane
On Fri, Aug 4, 2023 at 11:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dane Foster <studdugie@gmail.com> writes:
> > I'm trying to understand a bit of weirdness in a plan output. There is a
> > sort node above a sequential scan node where the scan node produces
> 26,026
> > rows yet the sort node above it produces 42,995,408. How is it possible
> to
> > sort more data than you received?
>
> If the sort is the inner input to a merge join, this could reflect
> mark-and-restore rescanning of the sort's output. Are there a
> whole lot of duplicate keys on the merge's other side?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dane Foster <studdugie@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-08-04 15:31 UTC (permalink / raw)
To: Dane Foster <studdugie@gmail.com>; +Cc: pgsql-performance
Dane Foster <studdugie@gmail.com> writes:
>> If the sort is the inner input to a merge join, this could reflect
>> mark-and-restore rescanning of the sort's output. Are there a
>> whole lot of duplicate keys on the merge's other side?
> Yes. The course_id column's values repeat a LOT on the merge side.
Hmm. The planner should avoid using a merge join if it knows that
to be true. Maybe analyze'ing that table would prompt it to use
some other join method?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plan weirdness. A sort produces more rows than the node beneath it
@ 2023-08-04 16:41 Dane Foster <studdugie@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Dane Foster @ 2023-08-04 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
>
> Hmm. The planner should avoid using a merge join if it knows that
> to be true. Maybe analyze'ing that table would prompt it to use
> some other join method?
The planner has updated stats on the table and wants to use a nested loop:
https://explain.dalibo.com/plan/3814d5356cc82528
But the nested loop version is around 8 seconds slower so I forced the
issue. But thanks to this conversation I now understand what's happening
with the row count. This understanding helped make the nested loops' plan
easier to understand. Unfortunately, there doesn't seem to be any hope for
the merge join variant in terms of being easily understood. The uninitiated
sees a scan node and its parent sort node and their brain defaults to
thinking: the sort node will produce the same number of rows as the node
feeding it.
Cheers,
Dane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-08-04 16:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-04 14:59 Plan weirdness. A sort produces more rows than the node beneath it Dane Foster <studdugie@gmail.com>
2023-08-04 15:07 ` Jeff Janes <jeff.janes@gmail.com>
2023-08-04 15:09 ` Dane Foster <studdugie@gmail.com>
2023-08-04 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-04 15:15 ` Dane Foster <studdugie@gmail.com>
2023-08-04 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-04 16:41 ` Dane Foster <studdugie@gmail.com>
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