Controlling complexity in queries

From: Robert James <srobertjames(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Controlling complexity in queries
Date: 2011-12-12 00:46:16
Message-ID: CAGYyBgj2tubZBK0kwNcVt9C41Nmc+WYPut=pA+Y9+4h6BWUFNQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-12-12 01:15:52 Re: Controlling complexity in queries
Previous Message David Johnston 2011-12-12 00:44:23 Re: Denormalizing via SQL