Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0043aa6

Browse files
committed
Add basic regression tests for semi/antijoin recognition.
Add some simple tests that the planner recognizes all thestandard idioms for SEMI and ANTI joins. Failure to optimizein this way won't necessarily cause any visible change inquery results, so check the plans. We had no similar coveragebefore, at least for some variants of antijoin, as noted byRichard Guo.Discussion:https://postgr.es/m/CAMbWs4-mvPPCJ1W6iK6dD5HiNwoJdi6mZp=-7mE8N9Sh+cd0tQ@mail.gmail.com
1 parent0717f2f commit0043aa6

File tree

2 files changed

+88
-0
lines changed

2 files changed

+88
-0
lines changed

‎src/test/regress/expected/join.out

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2650,6 +2650,69 @@ WHERE d.f1 IS NULL;
26502650
9999
26512651
(3 rows)
26522652

2653+
--
2654+
-- basic semijoin and antijoin recognition tests
2655+
--
2656+
explain (costs off)
2657+
select a.* from tenk1 a
2658+
where unique1 in (select unique2 from tenk1 b);
2659+
QUERY PLAN
2660+
------------------------------------------------------------
2661+
Hash Semi Join
2662+
Hash Cond: (a.unique1 = b.unique2)
2663+
-> Seq Scan on tenk1 a
2664+
-> Hash
2665+
-> Index Only Scan using tenk1_unique2 on tenk1 b
2666+
(5 rows)
2667+
2668+
-- sadly, this is not an antijoin
2669+
explain (costs off)
2670+
select a.* from tenk1 a
2671+
where unique1 not in (select unique2 from tenk1 b);
2672+
QUERY PLAN
2673+
--------------------------------------------------------
2674+
Seq Scan on tenk1 a
2675+
Filter: (NOT (hashed SubPlan 1))
2676+
SubPlan 1
2677+
-> Index Only Scan using tenk1_unique2 on tenk1 b
2678+
(4 rows)
2679+
2680+
explain (costs off)
2681+
select a.* from tenk1 a
2682+
where exists (select 1 from tenk1 b where a.unique1 = b.unique2);
2683+
QUERY PLAN
2684+
------------------------------------------------------------
2685+
Hash Semi Join
2686+
Hash Cond: (a.unique1 = b.unique2)
2687+
-> Seq Scan on tenk1 a
2688+
-> Hash
2689+
-> Index Only Scan using tenk1_unique2 on tenk1 b
2690+
(5 rows)
2691+
2692+
explain (costs off)
2693+
select a.* from tenk1 a
2694+
where not exists (select 1 from tenk1 b where a.unique1 = b.unique2);
2695+
QUERY PLAN
2696+
------------------------------------------------------------
2697+
Hash Anti Join
2698+
Hash Cond: (a.unique1 = b.unique2)
2699+
-> Seq Scan on tenk1 a
2700+
-> Hash
2701+
-> Index Only Scan using tenk1_unique2 on tenk1 b
2702+
(5 rows)
2703+
2704+
explain (costs off)
2705+
select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2
2706+
where b.unique2 is null;
2707+
QUERY PLAN
2708+
------------------------------------------------------------
2709+
Hash Anti Join
2710+
Hash Cond: (a.unique1 = b.unique2)
2711+
-> Seq Scan on tenk1 a
2712+
-> Hash
2713+
-> Index Only Scan using tenk1_unique2 on tenk1 b
2714+
(5 rows)
2715+
26532716
--
26542717
-- regression test for proper handling of outer joins within antijoins
26552718
--

‎src/test/regress/sql/join.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -624,6 +624,31 @@ LEFT JOIN (
624624
)AS dON (a.f1=d.f1)
625625
WHEREd.f1 ISNULL;
626626

627+
--
628+
-- basic semijoin and antijoin recognition tests
629+
--
630+
631+
explain (costs off)
632+
select a.*from tenk1 a
633+
where unique1in (select unique2from tenk1 b);
634+
635+
-- sadly, this is not an antijoin
636+
explain (costs off)
637+
select a.*from tenk1 a
638+
where unique1 notin (select unique2from tenk1 b);
639+
640+
explain (costs off)
641+
select a.*from tenk1 a
642+
where exists (select1from tenk1 bwherea.unique1=b.unique2);
643+
644+
explain (costs off)
645+
select a.*from tenk1 a
646+
where not exists (select1from tenk1 bwherea.unique1=b.unique2);
647+
648+
explain (costs off)
649+
select a.*from tenk1 aleft join tenk1 bona.unique1=b.unique2
650+
whereb.unique2 isnull;
651+
627652
--
628653
-- regression test for proper handling of outer joins within antijoins
629654
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp