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