From: | digoal(at)126(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14157: PostgreSQL 9.6 foreign table can improve with this case |
Date: | 2016-05-25 09:52:07 |
Message-ID: | 20160525095207.19427.14190@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14157
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.6beta1
Operating system: CentOS 6.x x64
Description:
1. IF we hope to use foreign sort, must disable enable_sort first.
```
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)
```
<br />
2. parent table should not to query if we will use fdw for sharding only,
how about to do ? add new syntax to improve sql parser?
3. this query contain tab.id=tbl.id and mod(tbl.id,4)=1
can predict :
and mod(tab.id,4)=1
so tab table only need query tab1.
this is a optermizer improve.
```
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)
```
From | Date | Subject | |
---|---|---|---|
Next Message | digoal | 2016-05-25 09:54:02 | BUG #14158: PostgreSQL 9.6 bloom don't support unlogged table? |
Previous Message | daniudass | 2016-05-25 08:55:49 | BUG #14156: Password Autehentication |