From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: which is better: using OR clauses or UNION?
Date: 16 Aug 2011 14:27:50 GMT
Message-ID: <j2dup6$nl$1@reversiblemaps.ath.cx> (raw)
References: <D7B38BB0-89D0-4351-9936-27F9D9B4AFE0@witneyweb.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: which is better: using OR clauses or UNION?
In-Reply-To: <j2dup6$nl$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox