Re: Controlling complexity in queries

Inlined.

David J.

On Dec 11, 2011, at 19:46, Robert James <srobertjames(at)gmail(dot)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 are a 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general