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?
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 |