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

Commitccd8f97

Browse files
committed
postgres_fdw: Consider requesting sorted data so we can do a merge join.
When use_remote_estimate is enabled, consider adding ORDER BY to thequery we sending to the remote server so that we can use that ordereddata for a merge join. Commitf18c944arranges to push down the query pathkeys, which seems like the casemostly likely to be a win, but testing shows this can sometimes win,too.For a regular table, we know which indexes are present and thereforetest whether the ordering provided by each such index is useful. Here,we take the opposite approach: guess what orderings would be useful ifthey could be generated cheaply, and then ask the remote side what thosewill cost.Ashutosh Bapat, with very substantial cosmetic revisions by me. Alsoreviewed by Rushabh Lathia.
1 parentf5a4370 commitccd8f97

File tree

5 files changed

+410
-41
lines changed

5 files changed

+410
-41
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out‎

Lines changed: 165 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -343,6 +343,76 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
343343
fixed |
344344
(1 row)
345345

346+
-- Test forcing the remote server to produce sorted data for a merge join.
347+
SET enable_hashjoin TO false;
348+
SET enable_nestloop TO false;
349+
-- inner join; expressions in the clauses appear in the equivalence class list
350+
EXPLAIN (VERBOSE, COSTS false)
351+
SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
352+
QUERY PLAN
353+
----------------------------------------------------------------------------
354+
Limit
355+
Output: t1.c1, t2."C 1"
356+
-> Merge Join
357+
Output: t1.c1, t2."C 1"
358+
Merge Cond: (t1.c1 = t2."C 1")
359+
-> Foreign Scan on public.ft2 t1
360+
Output: t1.c1
361+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
362+
-> Index Only Scan using t1_pkey on "S 1"."T 1" t2
363+
Output: t2."C 1"
364+
(10 rows)
365+
366+
SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
367+
c1 | C 1
368+
-----+-----
369+
101 | 101
370+
102 | 102
371+
103 | 103
372+
104 | 104
373+
105 | 105
374+
106 | 106
375+
107 | 107
376+
108 | 108
377+
109 | 109
378+
110 | 110
379+
(10 rows)
380+
381+
-- outer join; expressions in the clauses do not appear in equivalence class
382+
-- list but no output change as compared to the previous query
383+
EXPLAIN (VERBOSE, COSTS false)
384+
SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
385+
QUERY PLAN
386+
----------------------------------------------------------------------------
387+
Limit
388+
Output: t1.c1, t2."C 1"
389+
-> Merge Left Join
390+
Output: t1.c1, t2."C 1"
391+
Merge Cond: (t1.c1 = t2."C 1")
392+
-> Foreign Scan on public.ft2 t1
393+
Output: t1.c1
394+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
395+
-> Index Only Scan using t1_pkey on "S 1"."T 1" t2
396+
Output: t2."C 1"
397+
(10 rows)
398+
399+
SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
400+
c1 | C 1
401+
-----+-----
402+
101 | 101
403+
102 | 102
404+
103 | 103
405+
104 | 104
406+
105 | 105
407+
106 | 106
408+
107 | 107
409+
108 | 108
410+
109 | 109
411+
110 | 110
412+
(10 rows)
413+
414+
RESET enable_hashjoin;
415+
RESET enable_nestloop;
346416
-- ===================================================================
347417
-- WHERE with remotely-executable conditions
348418
-- ===================================================================
@@ -3538,6 +3608,101 @@ select tableoid::regclass, * from bar order by 1,2;
35383608
bar2 | 7 | 177
35393609
(6 rows)
35403610

3611+
-- Test forcing the remote server to produce sorted data for a merge join,
3612+
-- but the foreign table is an inheritance child.
3613+
truncate table loct1;
3614+
truncate table only foo;
3615+
\set num_rows_foo 2000
3616+
insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2);
3617+
insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1, :num_rows_foo, 2);
3618+
SET enable_hashjoin to false;
3619+
SET enable_nestloop to false;
3620+
alter foreign table foo2 options (use_remote_estimate 'true');
3621+
create index i_loct1_f1 on loct1(f1);
3622+
create index i_foo_f1 on foo(f1);
3623+
analyze foo;
3624+
analyze loct1;
3625+
-- inner join; expressions in the clauses appear in the equivalence class list
3626+
explain (verbose, costs off)
3627+
select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
3628+
QUERY PLAN
3629+
---------------------------------------------------------------------------------------
3630+
Limit
3631+
Output: foo.f1, loct1.f1, foo.f2
3632+
-> Sort
3633+
Output: foo.f1, loct1.f1, foo.f2
3634+
Sort Key: foo.f2
3635+
-> Merge Join
3636+
Output: foo.f1, loct1.f1, foo.f2
3637+
Merge Cond: (foo.f1 = loct1.f1)
3638+
-> Merge Append
3639+
Sort Key: foo.f1
3640+
-> Index Scan using i_foo_f1 on public.foo
3641+
Output: foo.f1, foo.f2
3642+
-> Foreign Scan on public.foo2
3643+
Output: foo2.f1, foo2.f2
3644+
Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC
3645+
-> Index Only Scan using i_loct1_f1 on public.loct1
3646+
Output: loct1.f1
3647+
(17 rows)
3648+
3649+
select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
3650+
f1 | f1
3651+
----+----
3652+
20 | 20
3653+
22 | 22
3654+
24 | 24
3655+
26 | 26
3656+
28 | 28
3657+
30 | 30
3658+
32 | 32
3659+
34 | 34
3660+
36 | 36
3661+
38 | 38
3662+
(10 rows)
3663+
3664+
-- outer join; expressions in the clauses do not appear in equivalence class
3665+
-- list but no output change as compared to the previous query
3666+
explain (verbose, costs off)
3667+
select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
3668+
QUERY PLAN
3669+
---------------------------------------------------------------------------------------
3670+
Limit
3671+
Output: foo.f1, loct1.f1, foo.f2
3672+
-> Sort
3673+
Output: foo.f1, loct1.f1, foo.f2
3674+
Sort Key: foo.f2
3675+
-> Merge Left Join
3676+
Output: foo.f1, loct1.f1, foo.f2
3677+
Merge Cond: (foo.f1 = loct1.f1)
3678+
-> Merge Append
3679+
Sort Key: foo.f1
3680+
-> Index Scan using i_foo_f1 on public.foo
3681+
Output: foo.f1, foo.f2
3682+
-> Foreign Scan on public.foo2
3683+
Output: foo2.f1, foo2.f2
3684+
Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC
3685+
-> Index Only Scan using i_loct1_f1 on public.loct1
3686+
Output: loct1.f1
3687+
(17 rows)
3688+
3689+
select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
3690+
f1 | f1
3691+
----+----
3692+
10 | 10
3693+
11 |
3694+
12 | 12
3695+
13 |
3696+
14 | 14
3697+
15 |
3698+
16 | 16
3699+
17 |
3700+
18 | 18
3701+
19 |
3702+
(10 rows)
3703+
3704+
RESET enable_hashjoin;
3705+
RESET enable_nestloop;
35413706
-- Test that WHERE CURRENT OF is not supported
35423707
begin;
35433708
declare c cursor for select * from bar where f1 = 7;

0 commit comments

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