Menu ▾ ▴

#465 Simple join pushdown does not work

1.2 Dev Q
open
Ashutosh Bapat
None
7
2013-12-12
2013-12-12
Koichi Suzuki
No

Simple join pushdown as follows does not work.

<<<<<<<< Script >>>>>>>>
CREATE TABLE A(
id varchar(3),
data text
)
DISTRIBUTE BY HASH(id)
;
CREATE TABLE B (
id varchar(3),
data text
)
DISTRIBUTE BY HASH(id)
;

INSERT INTO A VALUES ('1','a');
INSERT INTO A VALUES ('2','b');
INSERT INTO A VALUES ('3','c');

INSERT INTO B VALUES ('1','A');
INSERT INTO B VALUES ('2','B');
INSERT INTO B VALUES ('4','D');

EXPLAIN ANALYZE VERBOSE
SELECT

FROM
A INNER JOIN B
ON (A.id = B.id)
;
EXPLAIN ANALYZE VERBOSE
SELECT

FROM
A LEFT OUTER JOIN B
ON (A.id = B.id)
;
EXPLAIN ANALYZE VERBOSE
SELECT

FROM
A RIGHT OUTER JOIN B
ON (A.id = B.id)
;
EXPLAIN ANALYZE VERBOSE
SELECT

FROM
A FULL OUTER JOIN B
ON (A.id = B.id)
;

<<<<<<<< Result (REL1_1_STABLE) >>>>>>>>

koichi=# CREATE TABLE A(
koichi(# id varchar(3),
koichi(# data text
koichi(# )
koichi-# DISTRIBUTE BY HASH(id)
koichi-# ;
CREATE TABLE
koichi=# CREATE TABLE B (
koichi(# id varchar(3),
koichi(# data text
koichi(# )
koichi-# DISTRIBUTE BY HASH(id)
koichi-# ;
CREATE TABLE
koichi=#
koichi=# INSERT INTO A VALUES ('1','a');
INSERT 0 1
koichi=# INSERT INTO A VALUES ('2','b');
INSERT 0 1
koichi=# INSERT INTO A VALUES ('3','c');
INSERT 0 1
koichi=#
koichi=# INSERT INTO B VALUES ('1','A');
INSERT 0 1
koichi=# INSERT INTO B VALUES ('2','B');
INSERT 0 1
koichi=# INSERT INTO B VALUES ('4','D');
INSERT 0 1
koichi=#
koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A INNER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.746..0.865 rows=2 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((a.id)::text = (b.id)::text)
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.263..0.379 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.461..0.461 rows=3 loops=1)
Output: b.id, b.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.429..0.457 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
Total runtime: 0.888 ms
(15 rows)

koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A LEFT OUTER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Left Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.579..0.582 rows=3 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((a.id)::text = (b.id)::text)
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.314..0.316 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.255..0.255 rows=3 loops=1)
Output: b.id, b.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.249..0.253 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
Total runtime: 0.595 ms
(15 rows)

koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A RIGHT OUTER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Left Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.574..0.577 rows=3 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((b.id)::text = (a.id)::text)
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.319..0.322 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.249..0.249 rows=3 loops=1)
Output: a.id, a.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.243..0.246 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
Total runtime: 0.588 ms
(15 rows)

koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A FULL OUTER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Full Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.568..0.578 rows=4 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((a.id)::text = (b.id)::text)
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.241..0.245 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.322..0.322 rows=3 loops=1)
Output: b.id, b.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.315..0.321 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
Total runtime: 0.590 ms
(15 rows)

koichi=#

<<<<<<<< Result (master: 8a62d77553dd9dc51f9936729756fac547852576) >>>>>>>>

koichi=# CREATE TABLE A(
koichi(# id varchar(3),
koichi(# data text
koichi(# )
koichi-# DISTRIBUTE BY HASH(id)
koichi-# ;
CREATE TABLE
koichi=# CREATE TABLE B (
koichi(# id varchar(3),
koichi(# data text
koichi(# )
koichi-# DISTRIBUTE BY HASH(id)
koichi-# ;
CREATE TABLE
koichi=#
koichi=# INSERT INTO A VALUES ('1','a');
INSERT 0 1
koichi=# INSERT INTO A VALUES ('2','b');
INSERT 0 1
koichi=# INSERT INTO A VALUES ('3','c');
INSERT 0 1
koichi=#
koichi=# INSERT INTO B VALUES ('1','A');
INSERT 0 1
koichi=# INSERT INTO B VALUES ('2','B');
INSERT 0 1
koichi=# INSERT INTO B VALUES ('4','D');
INSERT 0 1
koichi=#
koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A INNER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.722..0.855 rows=2 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((a.id)::text = (b.id)::text)
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.244..0.373 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.451..0.451 rows=3 loops=1)
Output: b.id, b.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.424..0.446 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
Total runtime: 0.875 ms
(15 rows)

koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A LEFT OUTER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Left Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.525..0.528 rows=3 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((a.id)::text = (b.id)::text)
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.245..0.248 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.271..0.271 rows=3 loops=1)
Output: b.id, b.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.239..0.271 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
Total runtime: 0.551 ms
(15 rows)

koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A RIGHT OUTER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Left Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.523..0.525 rows=3 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((b.id)::text = (a.id)::text)
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.244..0.245 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.274..0.274 rows=3 loops=1)
Output: a.id, a.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.252..0.272 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
Total runtime: 0.537 ms
(15 rows)

koichi=# EXPLAIN ANALYZE VERBOSE
koichi-# SELECT
koichi-# *
koichi-# FROM
koichi-# A FULL OUTER JOIN B
koichi-# ON (A.id = B.id)
koichi-# ;
QUERY PLAN

Hash Full Join (cost=0.12..0.26 rows=10 width=96) (actual time=0.527..0.536 rows=4 loops=1)
Output: a.id, a.data, b.id, b.data
Hash Cond: ((a.id)::text = (b.id)::text)
-> Data Node Scan on a "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.232..0.237 rows=3 loops=1)
Output: a.id, a.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.a WHERE true
-> Hash (cost=0.00..0.00 rows=1000 width=48) (actual time=0.290..0.290 rows=3 loops=1)
Output: b.id, b.data
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Data Node Scan on b "REMOTE_TABLE_QUERY" (cost=0.00..0.00 rows=1000 width=48) (actual time=0.283..0.287 rows=3 loops=1)
Output: b.id, b.data
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT id, data FROM ONLY public.b WHERE true
Total runtime: 0.549 ms
(15 rows)

koichi=#
koichi=#

Discussion

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.