Re: which is better: using OR clauses or UNION?

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of adam_pgsql
Sent: Tuesday, August 16, 2011 7:39 AM
To: pgsql-sql
Subject: [SQL] which is better: using OR clauses or UNION?

Hi,

I have a query hitting a table of 25 million rows. The table has a text
field ('identifier') which i need to query for matching rows. The question
is if i have multiple strings to match against this field I can use multiple
OR sub-statements or multiple statements in a UNION. The UNION seems to run
quicker.... is this to be expected? or is there anything else I can do
improve the speed of this query? Some query details:

--------------------------------------------

You can also try the following form:

... WHERE column = ANY( string_to_array ( lower( 'Bug1,Bug2,Bug3' ) , ',' )
);

The main advantage of this is that you can parameterize the input string and
so it will not logically matter how many values you are checking for.

Also, you can always just place the values you want to search for into a
table (temp or otherwise) and perform an Inner Join.

No idea which one is "faster" but the "string_to_array" above requires no
Dynamic SQL which all of the other forms (OR, UNION, IN) need. The table
form also does not require dynamic SQL but you need additional INSERTS for
each search value. I doubt it would be any better than the (OR/UNION/JOIN)
form but you could also create a VALUES virtual table - which also requires
Dynamic SQL.

SELECT *
FROM target_table
NATURAL JOIN ( VALUES ('Bug1'),('Bug2'),('Bug3') ) searchtarget (
join_column_name );

David J.