From d79f2d4036da2e5fd022e92c178ea342855db586 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 30 Jan 2005 04:20:52 +0000 Subject: Remove IN/slow FAQ item because it only applies to <=7.3.X. --- doc/src/FAQ/FAQ.html | 48 +++++++++++------------------------------------- 1 file changed, 11 insertions(+), 37 deletions(-) (limited to 'doc/src/FAQ/FAQ.html') diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index 0b8401ec2ac..d79d62c06e4 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -10,7 +10,7 @@ alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

-

Last updated: Sat Jan 29 23:15:42 EST 2005

+

Last updated: Sat Jan 29 23:20:03 EST 2005

Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
@@ -114,16 +114,14 @@ "invalid large obj descriptor"?
4.17) How do I create a column that will default to the current time?
- 4.18) Why are my subqueries using - IN so slow?
- 4.19) How do I perform an outer join?
- 4.20) How do I perform queries using multiple + 4.18) How do I perform an outer join?
+ 4.19) How do I perform queries using multiple databases?
- 4.21) How do I return multiple rows or columns + 4.20) How do I return multiple rows or columns from a function?
- 4.22) Why can't I reliably create/drop + 4.21) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?
- 4.23) What encryption options are available?
+ 4.22) What encryption options are available?

Extending PostgreSQL

@@ -1155,31 +1153,7 @@ BYTEA bytea variable-length byte array (null-byte safe) -

4.18) Why are my subqueries using - IN so slow?

- -

In versions prior to 7.4, subqueries were joined to outer queries - by sequentially scanning the result of the subquery for each row of - the outer query. If the subquery returns only a few rows and the outer - query returns many rows, IN is fastest. To - speed up other queries, replace IN with - EXISTS:

-
    SELECT *
-    FROM tab
-    WHERE col IN (SELECT subcol FROM subtab);
-
- to: -
    SELECT *
-    FROM tab
-    WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
-
- - For this to be fast, subcol should be an indexed column. -

In version 7.4 and later, IN actually uses the same - sophisticated join techniques as normal queries, and is prefered - to using EXISTS. - -

4.19) How do I perform an outer join?

+

4.18) How do I perform an outer join?

PostgreSQL supports outer joins using the SQL standard syntax. Here are two examples:

@@ -1219,7 +1193,7 @@ BYTEA bytea variable-length byte array (null-byte safe) ORDER BY col1 -

4.20) How do I perform queries using +

4.19) How do I perform queries using multiple databases?

There is no way to query a database other than the current one. @@ -1231,7 +1205,7 @@ BYTEA bytea variable-length byte array (null-byte safe) connections to different databases and merge the results on the client side.

-

4.21) How do I return multiple rows or +

4.20) How do I return multiple rows or columns from a function?

In 7.3, you can easily return multiple rows or columns from a @@ -1239,7 +1213,7 @@ BYTEA bytea variable-length byte array (null-byte safe) http://techdocs.postgresql.org/guides/SetReturningFunctions. -

4.22) Why can't I reliably create/drop +

4.21) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?

PL/PgSQL caches function contents, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that @@ -1249,7 +1223,7 @@ BYTEA bytea variable-length byte array (null-byte safe) EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

-

4.23) What encryption options are available? +

4.22) What encryption options are available?