Search Postgresql Archives

postgres_fdw how to pushdown parent table's JOIN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




PostgreSQL continued in sharding technology fdw based roots, the 9.6 Subject to the conditions set, and JOIN support SORT pushed down to the data node execution.
Here is a test
Create several shard databases

for subfix in 0 1 2 3
do
psql -c "create database db $ subfix"
done
Create a master database

psql -c "create database master;"
psql master -c "create extension postgres_fdw;"
Create a foreign server and user mapping in the master database

for subfix in 0 1 2 3
do
psql master -c "create server db $ subfix foreign data wrapper postgres_fdw options (hostaddr 'xxx.xxx.xxx.xxx', dbname 'db $ subfix', port '1923');"
psql master -c "create user mapping for postgres server db $ subfix options (user 'postgres', password 'postgres');"
done
Create slice table shard databases

for subfix in 0 1 2 3
do
psql db $ subfix -c "drop table if exists tbl; create table tbl (id int primary key, info text)"
psql db $ subfix -c "drop table if exists tab; create table tab (id int primary key, info text)"
done
Create a foreign table in the master database, and set constraints

for subfix in 0 1 2 3
do
psql master -c "drop foreign table if exists tbl $ subfix; create foreign table tbl $ subfix (id int not null, info text) server db $ subfix options (schema_name 'public', table_name 'tbl');"
psql master -c "alter foreign table tbl $ subfix add constraint ck1 check (mod (id, 4) = $ subfix);"

psql master -c "drop foreign table if exists tab $ subfix; create foreign table tab $ subfix (id int not null, info text) server db $ subfix options (schema_name 'public', table_name 'tab');"
psql master -c "alter foreign table tab $ subfix add constraint ck1 check (mod (id, 4) = $ subfix);"
done
View

psql master << EOF
\ Det
EOF
result

 List of foreign tables
 Schema | Table | Server
-------- + ------- + --------
 public | tab0 | db0
 public | tab1 | db1
 public | tab2 | db2
 public | tab3 | db3
 public | tbl0 | db0
 public | tbl1 | db1
 public | tbl2 | db2
 public | tbl3 | db3
(8 rows)
Create the parent table in the master database

psql master -c "create table tbl (id int primary key, info text);"
psql master -c "create table tab (id int primary key, info text);"
Creating inheritance foreign and parent tables in the master database

for subfix in 0 1 2 3
do
psql master -c "alter foreign table tbl $ subfix inherit tbl;"
psql master -c "alter foreign table tab $ subfix inherit tab;"
done
Test JOIN pushdown

master = # explain verbose select * from tbl1, tab1 where tab1.id = tbl1.id and mod (tbl1.id, 4) = 1;
                                                                     QUERY PLAN
-------------------------------------------------- -------------------------------------------------- ------------------------------------------------
 Foreign Scan (cost = 100.00..226.75 rows = 48 width = 72)
   Output: tbl1.id, tbl1.info, tab1.id, tab1.info
   Relations: (public.tbl1) INNER JOIN (public.tab1)
   Remote SQL: SELECT r1.id, r1.info, r2.id, r2.info FROM (public.tbl r1 INNER JOIN public.tab r2 ON (((r1.id = r2.id)) AND ((mod (r1 .id, 4) = 1))))
(4 rows)
Need to close the current sort pushdown switch enable_sort optimizer will pushdown is worth improvement

master = # set enable_sort = off;
SET
master = # explain verbose select * from tbl1 where mod (id, 4) = mod (100,4) order by id;
                                            QUERY PLAN
-------------------------------------------------- -------------------------------------------------
 Foreign Scan on public.tbl1 (cost = 100.00..136.71 rows = 7 width = 36)
   Output: id, info
   Remote SQL: SELECT id, info FROM public.tbl WHERE ((mod (id, 4) = 0)) ORDER BY id ASC NULLS LAST
(3 rows)


Also in need of improvement
What such a query optimizer to optimize?
1. If you want more pure sharding, the parent table should not be involved in the calculation, it is just an alias. Thus JOIN pushdown can prerequisite.
2. tab.id = tbl.id and mod (tbl.id, 4) = 1 can deduce and mod (tab.id, 4) = 1. Therefore tab table only needs to scan tab1.

master = # explain verbose select * from tbl, tab where tab.id = tbl.id and mod (tbl.id, 4) = 1;
                                           QUERY PLAN
-------------------------------------------------- ----------------------------------------------
 Gather (cost = 0.00..0.00 rows = 0 width = 0)
   Output: tbl.id, tbl.info, tab.id, tab.info
   Workers Planned: 1
   Single Copy: true
   -> Hash Join (cost = 130.71..757.17 rows = 218 width = 72)
         Output: tbl.id, tbl.info, tab.id, tab.info
         Hash Cond: (tab.id = tbl.id)
         -> Append (cost = 0.00..603.80 rows = 5461 width = 36)
               -> Seq Scan on public.tab (cost = 0.00..0.00 rows = 1 width = 36)
                     Output: tab.id, tab.info
               -> Foreign Scan on public.tab0 (cost = 100.00..150.95 rows = 1365 width = 36)
                     Output: tab0.id, tab0.info
                     Remote SQL: SELECT id, info FROM public.tab
               -> Foreign Scan on public.tab1 (cost = 100.00..150.95 rows = 1365 width = 36)
                     Output: tab1.id, tab1.info
                     Remote SQL: SELECT id, info FROM public.tab
               -> Foreign Scan on public.tab2 (cost = 100.00..150.95 rows = 1365 width = 36)
                     Output: tab2.id, tab2.info
                     Remote SQL: SELECT id, info FROM public.tab
               -> Foreign Scan on public.tab3 (cost = 100.00..150.95 rows = 1365 width = 36)
                     Output: tab3.id, tab3.info
                     Remote SQL: SELECT id, info FROM public.tab
         -> Hash (cost = 130.61..130.61 rows = 8 width = 36)
               Output: tbl.id, tbl.info
               -> Append (cost = 0.00..130.61 rows = 8 width = 36)
                     -> Seq Scan on public.tbl (cost = 0.00..0.00 rows = 1 width = 36)
                           Output: tbl.id, tbl.info
                           Filter: (mod (tbl.id, 4) = 1)
                     -> Foreign Scan on public.tbl1 (cost = 100.00..130.61 rows = 7 width = 36)
                           Output: tbl1.id, tbl1.info
                           Remote SQL: SELECT id, info FROM public.tbl WHERE ((mod (id, 4) = 1))
(31 rows)
so i modify the source code, allows to delete the inherited constraints
vi src / backend / commands / tablecmds.c

                / * Do not drop inherited constraints * /
// If (con-> coninhcount> 0 &&! Recursing)
// Ereport (ERROR,
// (Errcode (ERRCODE_INVALID_TABLE_DEFINITION),
// Errmsg ( "can not drop inherited constraint \"% s \ "of relation \"% s \ "",
// ConstrName, RelationGetRelationName (rel))));
Recompile and restart

make && make install
pg_ctl restart -m fast
Add Constraints primary table, delete the child table constraints, resulting in the illusion of the main table is not accessed.

alter table tab add constraint tab_ck check (mod (id, 4) is null);
alter table tbl add constraint tbl_ck check (mod (id, 4) is null);

alter table only tab0 drop constraint tab_ck;
alter table only tab1 drop constraint tab_ck;
alter table only tab2 drop constraint tab_ck;
alter table only tab3 drop constraint tab_ck;

alter table only tbl0 drop constraint tbl_ck;
alter table only tbl1 drop constraint tbl_ck;
alter table only tbl2 drop constraint tbl_ck;
alter table only tbl3 drop constraint tbl_ck;
My goal is to be able to do so pushdown JOIN, but not actually push down, this is very painful.
A real sub-library of middleware should solve this problem.

master = # explain verbose select * from tbl, tab where tab.id = tbl.id and mod (tbl.id, 4) = 1 and mod (tab.id, 4) = 1;
                                        QUERY PLAN
-------------------------------------------------- ----------------------------------------
 Nested Loop (cost = 200.00..261.98 rows = 7 width = 72)
   Output: tbl1.id, tbl1.info, tab1.id, tab1.info
   Join Filter: (tbl1.id = tab1.id)
   -> Append (cost = 100.00..130.61 rows = 7 width = 36)
         -> Foreign Scan on public.tbl1 (cost = 100.00..130.61 rows = 7 width = 36)
               Output: tbl1.id, tbl1.info
               Remote SQL: SELECT id, info FROM public.tbl WHERE ((mod (id, 4) = 1))
   -> Materialize (cost = 100.00..130.65 rows = 7 width = 36)
         Output: tab1.id, tab1.info
         -> Append (cost = 100.00..130.61 rows = 7 width = 36)
               -> Foreign Scan on public.tab1 (cost = 100.00..130.61 rows = 7 width = 36)
                     Output: tab1.id, tab1.info
                     Remote SQL: SELECT id, info FROM public.tab WHERE ((mod (id, 4) = 1))
(13 rows)
This is what I want results

                                                                                  QUERY PLAN
-------------------------
 Foreign Scan (cost = 100.00..161.58 rows = 1 width = 72)
   Output: tbl.id, tbl.info, tab.id, tab.info
   Relations: (public.tbl1 tbl) INNER JOIN (public.tab1 tab)
   Remote SQL: SELECT r1.id, r1.info, r2.id, r2.info FROM (public.tbl r1 INNER JOIN public.tab r2 ON (((r1.id = r2.id)) AND ((mod (r2 .id, 4) = 1)) AND ((mod (r1.id, 4) = 1))))
(4 rows)



--
公益是一辈子的事,I'm Digoal,Just Do It.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux