From: Tom Lane <tgl@sss.pgh.pa.us>
To: dastapov@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18234: Nested Loop joint strategy is ignored for a tiny table joined with UNION ALL of two filtered parts
Date: Thu, 07 Dec 2023 16:53:07 -0500
Message-ID: <1320817.1701985987@sss.pgh.pa.us> (raw)
In-Reply-To: <18234-571586e70bd0658d@postgresql.org>
References: <18234-571586e70bd0658d@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> Summary of the issue: for a (5-row recordset) JOIN (massive partitioned
> recordset indexed by id) USING (id), the (Nested Loop over 5 values)
> strategy is completely ignored, and Hash Join or Merge Join is done instead,
> which does SeqScan over the "massive recordset".
> Reproduction in DB Fiddle:
> https://www.db-fiddle.com/f/sJUUWNgW7pqPWcJwihVoj5/1 (this demonstrates both
> the bad behaviour and a way to work around it)
We are generally not too happy with non-self-contained bug reports.
Once that DB Fiddle entry disappears, this bug report will be useless.
However ...
> 1)The massive recordset on the right side of the JOIN must come from the
> UNION ALL of two parts, both of which have a filter, like this view in my
> reproduction:
> create view vw_broken as
> select id from huge where filter_out
> union all
> select id from medium where filter_out;
I suspect the WHERE clauses trigger the problem because the resulting
sub-selects can't be pulled up to become an "appendrel", per
is_safe_append_member:
* It's only safe to pull up the child if its jointree contains exactly
* one RTE, else the AppendRelInfo data structure breaks. The one base RTE
* could be buried in several levels of FromExpr, however. Also, if the
* child's jointree is completely empty, we can pull up because
* pull_up_simple_subquery will insert a single RTE_RESULT RTE instead.
*
* Also, the child can't have any WHERE quals because there's no place to
* put them in an appendrel. (This is a bit annoying...)
That means the sub-selects will be planned independently and there's
no chance to consider the nestloop-with-inner-indexscan plan you are
hoping for.
This is a longstanding wart, but improving matters would require some
fairly painstaking work. The "appendrel" mechanism is core to both
traditional inheritance and partitioning; I don't recommend trying
to blow it up and start over. I vaguely recall previous discussions
that identified some semantic issues with trying to just attach
WHERE clauses to appendrel members, but it was a long time ago and
the details escape me.
regards, tom lane
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: tgl@sss.pgh.pa.us, dastapov@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18234: Nested Loop joint strategy is ignored for a tiny table joined with UNION ALL of two filtered parts
In-Reply-To: <1320817.1701985987@sss.pgh.pa.us>
* 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