Database Reference
In-Depth Information
Figure 9-6. Graphical plan of removing subqueries
Keep in mind that we're not asking you to avoid subqueries entirely. We're only asking
you to use them judiciously. When you do use them, pay extra attention to how you
incorporate them into the main query. Finally, remember that a subquery should work
with the the main query, not independently of it.
Avoid SELECT *
SELECT *
is wasteful. It's akin to printing out a 1,000-page document when you need
only 10 pages. Besides the obvious downside of adding to network traffic, there are two
other drawbacks that you might not think of.
First, PostgreSQL stores large blob and text objects using TOAST (The Oversized-
Attribute Storage Technique). TOAST maintains side tables for PostgreSQL to store this
extra data. So retrieving a large field means that TOAST must assemble the data from
rows across different tables. Imagine the extra processing if your table contains text data
the size of
War and Peace
and you perform an unnecessary
SELECT *
.
Second, when you define views, you often will include more columns than you'll need.
You might even go so far as to use
SELECT *
inside a view. This is understandable and
perfectly fine. PostgreSQL is smart enough to let you request all the columns you want
in your view definition and even include complex calculations or joins without incurring
penalty, as long as no user runs a query referring to the columns.
To drive home our point, let's wrap our census in a view and use the slow subquery
example from
Example 9-12
:
CREATE
OR
REPLACE
VIEW
vw_stats
AS
SELECT
tract_id
,
(
SELECT
COUNT
(
*
)
FROM
census
.
facts
As
F
WHERE
F
.
tract_id
=
T
.
tract_id
)
As
num_facts
,
(
SELECT
COUNT
(
*
)
FROM
census
.
lu_fact_types
As
Y
WHERE
Y
.
fact_type_id
IN
(
SELECT
fact_type_id
FROM
census
.
facts
F
WHERE
F
.
tract_id
=
T
.
tract_id
)
