Using each rel as both outer and inner for JOIN_ANTI

Поиск
Список
Период
Сортировка
От Richard Guo
Тема Using each rel as both outer and inner for JOIN_ANTI
Дата
Msg-id CAMbWs48xh9hMzXzSy3VaPzGAz+fkxXXTUbCLohX1_L8THFRm2Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Using each rel as both outer and inner for JOIN_ANTI  (Heikki Linnakangas <hlinnaka@iki.fi>)
Список pgsql-hackers
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.

So I'm wondering whether it's worthwhile to use each rel as both outer
and inner for anti-joins, maybe by inventing a JOIN_REVERSE_ANTI join
type.

Thanks
Richard

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: Doc patch for Logical Replication Message Formats (PG14)
Следующее
От: Daniel Gustafsson
Дата:
Сообщение: Re: SSL/TLS instead of SSL in docs