Re: Removing unneeded self joins
Heikki Linnakangas <hlinnaka@iki.fi>
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Remove GUC_NOT_IN_SAMPLE from enable_self_join_elimination
- 717d0e8dd945 18.0 landed
-
Put enable_self_join_elimination into postgresql.conf.sample
- c2d329260cd8 18.0 landed
-
Get rid of ojrelid local variable in remove_rel_from_query()
- e167191dc146 18.0 landed
-
Implement Self-Join Elimination
- fc069a3a6319 18.0 cited
-
Revert: Remove useless self-joins
- d1d286d83c0e 17.0 landed
-
Replace lateral references to removed rels in subqueries
- 466979ef031a 17.0 landed
-
Replace relids in lateral subquery parse tree during SJE
- 489072ab7a9e 17.0 landed
-
Forbid SJE with result relation
- 8c441c082797 17.0 landed
-
Fix misuse of RelOptInfo.unique_for_rels cache by SJE
- 30b4955a4668 17.0 landed
-
Replace the relid in some missing fields during SJE
- a7928a57b9f0 17.0 landed
-
Revert 56-bit relfilenode change and follow-up commits.
- a448e49bcbe4 16.0 cited
-
Stabilize timetz test across DST transitions.
- 4a071afbd056 14.0 cited
-
Speed up finding EquivalenceClasses for a given set of rels
- 3373c7155350 13.0 cited
-
Fix mark-and-restore-skipping test case to not be a self-join.
- 24d08f3c0a1f 12.0 landed
On 28/11/2020 19:21, Andrey Lepikhov wrote:
> On 27.11.2020 21:49, Heikki Linnakangas wrote:
>> On 31/10/2020 11:26, Andrey V. Lepikhov wrote:
>>> + /*
>>> + * Process restrictlist to seperate out the self join
>>> quals from
>>> + * the other quals. e.g x = x goes to selfjoinquals and a
>>> = b to
>>> + * otherjoinquals.
>>> + */
>>> + split_selfjoin_quals(root, restrictlist, &selfjoinquals,
>>> + &otherjoinquals);
>>> +
>>> + if (list_length(selfjoinquals) == 0)
>>> + {
>>> + /*
>>> + * Have a chance to remove join if target list
>>> contains vars from
>>> + * the only one relation.
>>> + */
>>
>> I don't understand the logic here. If 'selfjoinquals' is empty, it means
>> that there is no join qual between the two relations, right? How can we
>> ever remove the join in that case? And how does the target list affect
>> that? Can you give an example query of that?
>
> Maybe it is a problem of variable naming. Following the idea of David
> Rowley, we split quals into two subsets: {x==x} and another, for example
> {x=y}.
> First set is an trivial case of self-join: if we have unique index on
> the attribute 'x', then this join is self-join.
> Second set is give us a chance: if right side is unique for right side
> of the qual and no vars from right side end up in the target list of the
> join, then this is a self-join case. Example:
>
> CREATE TABLE a(x int, y int);
> CREATE UNIQUE INDEX ON a(x);
> SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.x; -- self-join
> CREATE UNIQUE INDEX ON a(y);
> SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.y; -- self-join too
The latter join is not "useless". The patch is returning incorrect
result for that query:
> postgres=# insert into a values (1, 2);
> INSERT 0 1
> postgres=# insert into a values (2, 1);
> INSERT 0 1
> postgres=# SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.y; -- WRONG RESULT
> x | y
> ---+---
> (0 rows)
>
> postgres=# set enable_self_join_removal=off;
> SET
> postgres=# SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.y; -- CORRECT RESULT
> x | y
> ---+---
> 1 | 2
> 2 | 1
> (2 rows)
- Heikki