Re: Controlling complexity in queries - Mailing list pgsql-general

From David Johnston
Subject Re: Controlling complexity in queries
Date
Msg-id 93009630-D624-4BC1-A9BA-B28D6E0CBFAC@yahoo.com
Whole thread Raw
In response to Controlling complexity in queries  (Robert James <srobertjames@gmail.com>)
Responses Re: Controlling complexity in queries
List pgsql-general
Inlined.

David J.

On Dec 11, 2011, at 19:46, Robert James <srobertjames@gmail.com> wrote:

> I have a very long query.  Due to the planner and good indexing, it
> runs quite fast.  But it's so long, it's quite hard to follow.
>
> I'm trying to break it up into pieces, but am running up against
> limits of SQL.  Can you help me with any of these problems?
>
> 1.
> SELECT
> <complicated expression A with sub expression B> AS A,
> <complicated expression C with sub expression B> AS C,
> <complicated expression D with sub expression B> AS D
> ...
>
> I'd like to be able to extract the common subexpression B and give it
> a name (called "output_name" in the docs).  But there's no way then to
> reference it from the SELECT clause.  Any workarounds?

Use a WITH clause on the SELECT statement.

>
> 2. complicated join and subquery
> I'd like to extract subparts of this which are conceptually cohesive
> and make them VIEWs.  The problem is that they depend on parameters
> (in the ON and WHERE clauses), and VIEWs don't allow parameters.  I
> could use set returning functions, but, besides the headache involved,
> I've found that these tend to stop the planner from peering inside
> them, and hence ruin performance.
>
> Is there a recommend solution?

Use the VIEWs and let the planner optimize based upon the calling statement's WHERE clause.     ON clause parameters
area different story than the WHERE clause so see if you can avoid them. 

In the end if it is a critical area some degree of trail-and-error is useful; custom materialized views may also work.

>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Robert James
Date:
Subject: Controlling complexity in queries
Next
From: Craig Ringer
Date:
Subject: Re: Howto to clear PostgreSQL cache and how to preload all tuples of a table into the cache?