Inlined. David J. On Dec 11, 2011, at 19:46, Robert James <srobertjames@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
