General subselect question

From: Tim Hart <tjhart(at)me(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: General subselect question
Date: 2008-12-31 22:57:37
Message-ID: 3DF03291-82D2-4680-BF67-2DE3BAF5F177@me.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

In the general case, is a subselect that uses union less performant
than a union?

I have a query that looks something like this:

select <some columns>
from table1,
table2,
table3
where <where clause>
union
select <select clause>
from table1,
table2,
table3 ref1,
table3 ref2
where <where clause>

I realized today that I could convert the query to

select <some columns>
from table1,
table2,
( select <some columns>
from table3
where <where clause>
union
select <some columns>
from table3 ref1,
table3 ref2
where <where clause> )
where <where clause>

For my specific case, I'll use EXPLAIN to determine the performance
difference. I'll weigh the performance benefits with the maintenance
benefits ( less duplication of code for me on the second case) and
decide (assuming I'm still motivated on Friday ;) ).

But in the general case, do I have to pay special attention with
unions in subselects?

Tim Hart
615-713-9956 :cell
timothyjhart :Y!
tjhart(at)me(dot)com :AIM

Browse pgsql-general by date

  From Date Subject
Next Message George Pavlov 2009-01-01 01:21:32 Re: pg_restore question (-T and -t)
Previous Message Tony Caduto 2008-12-31 21:49:39 pg_restore question (-T and -t)