search

Tencent / TBase

TBase is an enterprise-level distributed HTAP database. Through a single database cluster to provide users with highly consistent distributed database services and high-performance data warehouse services, a set of integrated enterprise-level solutions is formed.
Other
1.39k stars 261 forks source link

improve inner join planning #47

Closed jinyyu closed 4 years ago

jinyyu commented 4 years ago

create table a (ida int); create table b (idb int);

--original query plan（Node/s: dn1, dn2） postgres=# explain select * from a join b on (ida=idb) where ida=1; QUERY PLAN

Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn1, dn2 -> Nested Loop (cost=0.00..56.88 rows=49 width=8) -> Seq Scan on a (cost=0.00..28.12 rows=7 width=4) Filter: (ida = 1) -> Materialize (cost=0.00..28.16 rows=7 width=4) -> Seq Scan on b (cost=0.00..28.12 rows=7 width=4) Filter: (idb = 1)

--improved query plan (Node/s: dn1) postgres=# explain select * from a join b on (ida=idb) where ida=1; QUERY PLAN

Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) Node/s: dn1 -> Nested Loop (cost=0.00..56.88 rows=49 width=8) -> Seq Scan on a (cost=0.00..28.12 rows=7 width=4) Filter: (ida = 1) -> Materialize (cost=0.00..28.16 rows=7 width=4) -> Seq Scan on b (cost=0.00..28.12 rows=7 width=4) Filter: (idb = 1)