PostgreSQL Query Optimization Detailed

1.2.2 PostgreSQL

1.2.2.1 S6 Statement

Review the query execution plan, and the subquery is optimized (materialized so that the subquery results are cached, and the subquery is executed only once).

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 Statement

View the query execution plan, and the subquery is optimized (with a semi-connection).

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 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 Statement

View the query execution plan, and the subquery is optimized (with a semi-connection).

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 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 Statement

View the query execution plan, and the subquery is optimized.

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