From: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi> |
---|---|
To: | Richard Guo <guofenglinux(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Using each rel as both outer and inner for JOIN_ANTI |
Date: | 2021-06-24 13:28:48 |
Message-ID: | 4fd55525-c023-ab1f-a56f-0d6cca1afc2c@iki.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 24/06/2021 12:50, Richard Guo wrote:
> Hi hackers,
>
> We may have anti-joins in several cases. Sublinks of 'NOT EXISTS' may be
> pulled up as anti-joins. Left joins whose join quals are strict for any
> nullable var that is forced null by higher qual levels will also be
> reduced to anti-joins. So anti-joins are very commonly used in practice.
>
> Currently when populating anti-join with paths, we do not try to swap
> the outer and inner to get both paths. That may make us miss some
> cheaper paths.
>
> # insert into foo select i, i from generate_series(1,10)i;
> INSERT 0 10
>
> # insert into bar select i, i from generate_series(1,5000000)i;
> INSERT 0 5000000
>
> # explain select * from foo left join bar on foo.a = bar.c where bar.c
> is null;
> QUERY PLAN
> -------------------------------------------------------------------------
> Hash Anti Join (cost=154156.00..173691.19 rows=1 width=16)
> Hash Cond: (foo.a = bar.c)
> -> Seq Scan on foo (cost=0.00..1.10 rows=10 width=8)
> -> Hash (cost=72124.00..72124.00 rows=5000000 width=8)
> -> Seq Scan on bar (cost=0.00..72124.00 rows=5000000 width=8)
> (5 rows)
>
> I believe if we use the smaller table 'foo' as inner side for this
> query, we would have a cheaper plan.
How would that work?
- Heikki
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2021-06-24 13:42:20 | Re: Add tests for UNBOUNDED syntax ambiguity |
Previous Message | Masahiko Sawada | 2021-06-24 13:27:40 | Re: Transactions involving multiple postgres foreign servers, take 2 |