CODEWITHSUNDEEP
sqlpostgresqlunionquery-performance
John Mullins
John Mullins

Reputation: 1101

Which way for combining of conditions is preferable for unions?

There are some tables with some fields (on PostgreSQL):

Table1 {a, b, c}
Table2 {c, d, a}
Table3 {f, g, a}

I need to union a several queries and get a field. All of these queries have the same condition CONDITION_A and others different conditions: CONDITION_1, CONDITION_2, CONDITION_3

Which way for combining of conditions is preferable for unions?

1)CONDITION_A is embedded into each query:

SELECT a FROM Table1 WHERE <CONDITIONS_1> AND a.someParam=<CONDITION_A>
   UNION
SELECT a FROM Table2 WHERE <CONDITIONS_2> AND a.someParam=<CONDITION_A>
   UNION
SELECT a FROM Table3 WHERE <CONDITIONS_3> AND a.someParam=<CONDITION_A>

2) CONDITION_A is embedded after unions.

(SELECT a FROM Table1 WHERE <CONDITIONS_1> 
   UNION
 SELECT a FROM Table2 WHERE <CONDITIONS_2> 
   UNION
 SELECT a FROM Table3 WHERE <CONDITIONS_2> ) WHERE a.someParam=<CONDITION_A>

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

First, do you really need union? If you can use union all the query will run better because union incurs the overhead of removing duplicates.

Putting the conditions as close to the from is probably a good idea. This gives the optimizer more opportunities to optimize the query. For instance, each query might have indexes that include someParam, which can be used in the subqueries.

Upvotes: 3

Related Questions