In many applications, queries grow complex by having criteria tacked onto the end (or middle!) of them instead of integrating the criteria into the query as a whole. This is often due to programmers who are used to procedural languages. In procedural languages subroutines and isolation of objects is important. SQL is not a procedural language. It is a language which draws a flow of data from several sources with various criteria and operations. Thinking in flows or cascades of data is quite distinct from the thought process required for a procedural language. Thinking programmatically for queries results in queries that are constructed like the Winchester Mystery House.

When faced with improving existing queries, there is a fair amount of analysis and improvement of queries that can be done even without running the query through EXPLAIN ANALYZE. Making the effort to understand the expected result of each component of a query is vital to being able to improve the queries. This analysis can be done algebraicly or intuitively. My approach is to understand what the results of each component are and to reduce it intuitively. Here are some guidelines that I use.

The first thing to notice is unnecessary subselects. When the target is equivalent in nested subselects, it is likely that they can be collapsed into one query.

Whenever both SUM() and COUNT() are used on the same columns in the same query it bears looking into. The SUM( COUNT(*) ) can usually be the equivalent of a COUNT(*).

Look also for multiple DISTINCT clauses, especially on the same target list and similarly multiple GROUP BY statements on similar subselects. Eliminating subselects with unnecessary GROUP BY or DISTINCT clauses can simplify and speed up your query.

Separating functions into different subqueries can also make for unnecessary subqueries. Remember that aggregates referenced more than once in the same query scope will be shared and that functions, though not aggregates, can be nested.

Although the query planner (post 7.3) will probably reorder the joins effectively where parentheses isolate portions of the joins, for clarity and to prevent the possibility of overriding the planner, it is best to flatten the joins into one query.

In this example, the things that jump out at us are duplicate distinct selections with the same target list and two inner joins without further qualification. The DISTINCTS are collapsible. And (SELECT ... INNER JOIN ...) s INNER JOIN is equivalent to SELECT ... INNER JOIN ... INNER JOIN.

   --
   -- original query 1
   --
   -- EXPLAIN ANALYZE 
   SELECT DISTINCT acol, bcol, dcol, ecol
   FROM (
      SELECT DISTINCT acol, bcol, dcol, ecol
      FROM ONE o
      INNER JOIN two t USING (acol )
   ) AS foo
   INNER JOIN three th USING (acol, dcol);
   
   --
   -- improved query
   -- EXPLAIN ANALYZE 
   SELECT DISTINCT acol, bcol, dcol, ecol
   FROM one o
   INNER JOIN two t USING (acol)
   INNER JOIN three th USING (acol, dcol);

It is important to check to be sure your improvements are correct and actually improve the query. In this next example one level of improvements makes the query more readable but does not speed it up. The second level of improvements turn out to be wrong (!). The separation of the two GROUP BY clauses are really needed. This second example is a tale of caution.

In this example we see both SUM() and COUNT(*) and the separation of the two by a subquery that only executes CASE statements. These should be able to be collapsed.

   --
   -- original query 2
   --
   \echo query 2 original
   -- EXPLAIN ANALYZE
   SELECT acol, dcol, sum(count_one) AS count_one, 
      sum(count_two) AS count_two, count(DISTINCT gcol) AS gcol_count
   FROM (
      SELECT acol, dcol, gcol,
         CASE WHEN gcol = 1 THEN cnt ELSE 0 END AS count_one,
         CASE WHEN gcol = 2 THEN cnt ELSE 0 END AS count_two
      FROM (
         SELECT acol, dcol, count(*) AS cnt, gcol
         FROM one o
         INNER JOIN two t USING (acol)
         INNER JOIN three the USING (acol, dcol)
         GROUP BY acol, dcol, gcol
      ) AS bar
   ) AS foo
   GROUP BY acol, dcol;
   
   --
   -- improved query
   --
   \echo query 2 improved
   EXPLAIN ANALYZE
   SELECT acol, dcol, sum(count_one) AS count_one,
      sum(count_two) AS count_two, count(DISTINCT gcol) AS gcol_count
   FROM (
      SELECT acol, dcol, gcol,
         CASE WHEN gcol = 1 THEN count(*) ELSE 0 END AS count_one,
         CASE WHEN gcol = 2 THEN count(*) ELSE 0 END AS count_two
      FROM one o
      INNER JOIN two t USING (acol)
      INNER JOIN three the USING (acol, dcol)
      GROUP BY acol, dcol, gcol
   ) AS foo
   GROUP BY acol, dcol;

   \echo bad query 2 
   EXPLAIN ANALYZE
   SELECT acol, dcol,
      (CASE WHEN gcol = 1 THEN count(*) ELSE 0 END) AS count_one,
      (CASE WHEN gcol = 2 THEN count(*) ELSE 0 END) AS count_two,
      count( gcol ) as gcol_count
   FROM one o
   INNER JOIN two t USING (acol)
   INNER JOIN three th USING (acol, dcol)
   GROUP BY acol, dcol, gcol;

Contributors: elein at varlena.com

More on Group By
[SQL] tricky GROUP BY / JOIN question	07-Nov-2004

The example below joins 4 tables ITEM, BRAND, MODEL and CONDITION. In human understandable terms: a [secondhand] Item is of a particular Model and Brand. The Items retail at different prices depending on their condition.

This selection is close to what is wanted.

   SELECT brand.brand_name, model.model_name,
      min (item.price), max (item.price)
      min (condition.position), max (condition.position)
   FROM item
   LEFT OUTER JOIN model  ON model_pk =item.model_fk
   LEFT OUTER JOIN brand  ON brand.brand_pk =model.brand_fk
   LEFT OUTER JOIN condition on condition.condition_pk = item.condition_fk
   GROUP BY brand.brand_name,model.model_name

   Brand | Model | Cond | Cond | Price | Price
         |       | min  | max  | min   | max
   -------------------------------------------
   Canon | A1    | Exc  | Mint | 139   | 155   
   Canon | F1N   | Exc++| Mint-| 329   | 379
   Canon | 24mm  | Exc--| Mint+|  99   | 179
   Nikon | 50mm  | Exc--| Mint+| 109   | 119

This query should do the trick according to Tom Lane. It fetches the name of the condition based on the minimum and maximum positions in separate subqueries.

   SELECT brand.brand_name, model.model_name,
      min (item.price), max (item.price)
      (SELECT name FROM condition c1 WHERE position = min(condition.position)),
      (SELECT name FROM condition c2 WHERE position = max(condition.position)),
   FROM ITEM
   LEFT OUTER JOIN model  ON model_pk =item.model_fk
   LEFT OUTER JOIN brand  ON brand.brand_pk =model.brand_fk
   LEFT OUTER JOIN condition ON condition.condition_pk = item.condition_fk
   GROUP BY brand.brand_name,model.model_name

This means that prior to 7.4, the inner aggregates would be incorrectly run against the condition table rather than the outer query which includes the correct JOINs and GROUP BY clauses.

Contributors: T E Schmitz mailreg at numerixtechnology.de Tom Lane tgl at sss.pgh.pa.us