To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: which is better: using OR clauses or UNION?
Date: 16 Aug 2011 14:27:50 GMT
Organization: Dis (not Dat) Organisation
Lines: 58
Message-ID: <j2dup6$nl$1@reversiblemaps.ath.cx>
References: <D7B38BB0-89D0-4351-9936-27F9D9B4AFE0@witneyweb.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2011-08-16, adam_pgsql <adam_pgsql@witneyweb.org> wrote:
>
> 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:        

>  WHERE
>      ( lower(identifier) LIKE lower('BUGS0000001884677') OR
>        lower(identifier) LIKE lower('BUGS0000001884678') OR
>        lower(identifier) LIKE lower('BUGS0000001884679') OR
>        lower(identifier) LIKE lower('SpTIGR4-2210 (6F24)') )
>  ORDER BY a.identifier;

don't use like use regex.

...

WHERE identifier ~* E'^(BUGS0000001884677|BUGS0000001884678|BUGS0000001884679|SpTIGR4-2210
\\(6F24\\))$'

or 

where lower(identifier) ~* lower(E'^(BUGS0000001884677|BUGS0000001884678|BUGS0000001884679|SpTIGR4-2210
\\(6F24\\))$')

on the other hand you aren't doing any pattern stuff. - you you could
just use 'in':

WHERE lower(identifier) in
  (lower('BUGS0000001884677'),
   lower('BUGS0000001884678'),
   lower('BUGS0000001884679'),
   lower('SpTIGR4-2210(6F24)') 
  )

or if you need like, use like any:

WHERE lower(identifier) like ANY
  (lower('BUGS0000001884677'),
   lower('BUGS0000001884678'),
   lower('BUGS0000001884679'),
   lower('SpTIGR4-2210(6F24)') 
  )


> Also which should scale better if I add more strings to match? would there be any better design patterns for this problem?

use one of the above: preferably in, else regex, or failing that like any.
"= any" will also work but I don't thing it will ever be better than "in"

-- 
⚂⚃ 100% natural