[postgresql子查询语句]PostgreSQL子查询优化详解

时间:2020-10-30  来源:PostgreSQL  阅读:


1.2.2 PostgreSQL

1.2.2.1 S6 语句

查看查询执行计划,子查询被优化(采用物化的方式,使得子查询结果被缓存,既子查询只执行一次)。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT b1 FROM t1);

Nested Loop Semi Join   (cost=0.00..41707.39 rows=680 width=12)

Join Filter: (t3.b3 >= t1.b1)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Materialize   (cost=0.00..40.60 rows=2040 width=4)

->   Seq Scan on t1   (cost=0.00..30.40 rows=2040 width=4)

1.2.2.2 S7 语句

查看查询执行计划,子查询被优化(采用半连接)。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT a1 FROM t1);

Nested Loop Semi Join   (cost=0.15..377.82 rows=680 width=12)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Index Only Scan using t1_a1_key on t1   (cost=0.15..12.08 rows=680 width=4)

Index Cond: (a1 <= t3.b3)

1.2.2.3 S8 语句

查看查询执行计划,子查询被优化(采用半连接)。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 <= SOME (SELECT a1 FROM t1);

Nested Loop Semi Join   (cost=0.15..377.82 rows=680 width=12)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Index Only Scan using t1_a1_key on t1   (cost=0.15..12.08 rows=680 width=4)

Index Cond: (a1 >= t3.b3)

1.2.2.4 S9 语句

查看查询执行计划,子查询被优化。

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 = SOME (SELECT a1 FROM t1);

Hash Semi Join   (cost=55.90..103.00 rows=1020 width=12)

Hash Cond: (t3.b3 = t1.a1)

->   Seq Scan on t3   (cost=0.00..30.40 rows=2040 width=12)

->   Hash   (cost=30.40..30.40 rows=2040 width=4)

->   Seq Sc

[postgresql子查询语句]PostgreSQL子查询优化详解

http://m.bbyears.com/shujuku/107754.html

推荐访问:postgresql菜鸟教程 postgresql和mysql区别
相关阅读 猜你喜欢
本类排行 本类最新