Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6909FB5DBE6 for ; Tue, 16 Aug 2011 11:52:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 12947-02 for ; Tue, 16 Aug 2011 14:52:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailhub1.sgul.ac.uk (mailhub1.sghms.ac.uk [194.82.51.7]) by mail.postgresql.org (Postfix) with ESMTP id 08643B5DBE5 for ; Tue, 16 Aug 2011 11:52:34 -0300 (ADT) Received: from [172.16.20.181] (helo=mrc1-181.sgul.ac.uk) by mailhub1.sgul.ac.uk with esmtpsa (TLSv1:AES128-SHA:128) (Exim 4.72) (envelope-from ) id 1QtL0H-0003ZG-E6; Tue, 16 Aug 2011 15:52:29 +0100 Subject: Re: which is better: using OR clauses or UNION? Mime-Version: 1.0 (Apple Message framework v1084) Content-Type: text/plain; charset=utf-8 From: adam_pgsql In-Reply-To: Date: Tue, 16 Aug 2011 15:52:28 +0100 Cc: pgsql-sql Content-Transfer-Encoding: quoted-printable Message-Id: References: To: =?utf-8?Q?Viktor_Bojovi=C4=87?= X-Mailtags-Version: 2.5 X-Mailtags-Timestamp: 2011-08-16 15:52:28 +0100 X-Mailtags-Uuid: 9957FA95-FEA0-4267-B955-E917C15C1F2C X-Mailer: Apple Mail (2.1084) X-SGUL-MailScanner-Information: Please contact the ISP for more information X-SGUL-MailScanner-ID: 1QtL0H-0003ZG-E6 X-SGUL-MailScanner-MH1: Found to be clean X-SGUL-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-2.823, required 5, autolearn=not spam, ALL_TRUSTED -1.00, BAYES_00 -1.90, TW_PG 0.08) X-SGUL-MailScanner-From: adam_pgsql@witneyweb.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201108/62 X-Sequence-Number: 35730 Hi Viktor, thanks for your email, gave that a try, but the=20 lower(identifier) LIKE lower('BUGS0000001884677') OR still comes in quicker than the IN approach thanks adam On 16 Aug 2011, at 12:56, Viktor Bojovi=C4=87 wrote: > hi Adam, > im not sure which is faster/slower but, possibly you can speed it up = by using "in" operator >=20 > ...where lower(identifier) in (lower('BUGS0000001884677') , = lower('BUGS0000001884678'),....); >=20 > if you create function based index: > CREATE INDEX idx_table_lower_text ON table(lower(text_field)); > (taken from: = http://archives.postgresql.org/pgsql-sql/2003-09/msg00395.php) >=20 >=20 >=20 >=20 > On Tue, Aug 16, 2011 at 1:39 PM, adam_pgsql = wrote: >=20 > Hi, >=20 > 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: >=20 >=20 > table "dba_data_base", index: > "in_dba_data_base_identifier" btree (lower(identifier) = varchar_pattern_ops) >=20 >=20 > Query 1 > ------- > datadb=3D#explain analyse SELECT a.bioassay_id, a.identifier, ratio, = log_ratio, p_value > FROM > dba_data_base a > 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; > = QUERY PLAN >=20 > = --------------------------------------------------------------------------= --------------------------------------------------------------------------= ---------------------------------------------------- > -------------------------------- > Sort (cost=3D14930.13..14939.77 rows=3D3857 width=3D62) (actual = time=3D3208.466..3208.652 rows=3D318 loops=3D1) > Sort Key: identifier > -> Bitmap Heap Scan on dba_data_base a (cost=3D134.43..14700.38 = rows=3D3857 width=3D62) (actual time=3D81.106..3207.721 rows=3D318 = loops=3D1) > Recheck Cond: ((lower(identifier) ~~ 'bugs0000001884677'::text) = OR (lower(identifier) ~~ 'bugs0000001884678'::text) OR = (lower(identifier) ~~ 'bugs0000001884679'::text) OR (lower(identifier) ~ > ~ 'sptigr4-2210 (6f24)'::text)) > Filter: ((lower(identifier) ~~ 'bugs0000001884677'::text) OR = (lower(identifier) ~~ 'bugs0000001884678'::text) OR (lower(identifier) = ~~ 'bugs0000001884679'::text) OR (lower(identifier) ~~ 'spt > igr4-2210 (6f24)'::text)) > -> BitmapOr (cost=3D134.43..134.43 rows=3D3857 width=3D0) = (actual time=3D71.397..71.397 rows=3D0 loops=3D1) > -> Bitmap Index Scan on in_dba_data_base_identifier = (cost=3D0.00..32.64 rows=3D964 width=3D0) (actual time=3D0.029..0.029 = rows=3D0 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'bugs0000001884677'::character varying) > -> Bitmap Index Scan on in_dba_data_base_identifier = (cost=3D0.00..32.64 rows=3D964 width=3D0) (actual time=3D0.008..0.008 = rows=3D0 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'bugs0000001884678'::character varying) > -> Bitmap Index Scan on in_dba_data_base_identifier = (cost=3D0.00..32.64 rows=3D964 width=3D0) (actual time=3D0.008..0.008 = rows=3D0 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'bugs0000001884679'::character varying) > -> Bitmap Index Scan on in_dba_data_base_identifier = (cost=3D0.00..32.64 rows=3D964 width=3D0) (actual time=3D71.347..71.347 = rows=3D318 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ 'sptigr4-2210 = (6f24)'::character varying) > Total runtime: 3208.904 ms >=20 >=20 > Query 2 > ------- > datadb=3D#explain analyse SELECT a.bioassay_id, a.identifier, ratio, = log_ratio, p_value > FROM > dba_data_base a > WHERE > lower(identifier) LIKE lower('BUGS0000001884677') > UNION > SELECT a.bioassay_id, a.identifier, ratio, log_ratio, p_value > FROM > dba_data_base a > WHERE > lower(identifier) LIKE lower('BUGS0000001884678') > UNION > SELECT a.bioassay_id, a.identifier, ratio, log_ratio, p_value > FROM > dba_data_base a > WHERE > lower(identifier) LIKE lower('BUGS0000001884679') > UNION > SELECT a.bioassay_id, a.identifier, ratio, log_ratio, p_value > FROM > dba_data_base a > WHERE > lower(identifier) LIKE lower('SpTIGR4-2210 (6F24)') > ORDER BY identifier; >=20 > Sort (cost=3D15702.26..15711.90 rows=3D3856 width=3D62) (actual = time=3D3.688..3.886 rows=3D317 loops=3D1) > Sort Key: identifier > -> Unique (cost=3D15414.74..15472.58 rows=3D3856 width=3D62) = (actual time=3D2.663..3.387 rows=3D317 loops=3D1) > -> Sort (cost=3D15414.74..15424.38 rows=3D3856 width=3D62) = (actual time=3D2.660..2.834 rows=3D318 loops=3D1) > Sort Key: bioassay_id, identifier, ratio, log_ratio, = p_value > -> Append (cost=3D32.88..15185.06 rows=3D3856 width=3D62)= (actual time=3D0.320..2.131 rows=3D318 loops=3D1) > -> Bitmap Heap Scan on dba_data_base a = (cost=3D32.88..3786.62 rows=3D964 width=3D62) (actual time=3D0.041..0.041 = rows=3D0 loops=3D1) > Filter: (lower(identifier) ~~ = 'bugs0000001884677'::text) > -> Bitmap Index Scan on = in_dba_data_base_identifier (cost=3D0.00..32.64 rows=3D964 width=3D0) = (actual time=3D0.036..0.036 rows=3D0 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'bugs0000001884677'::character varying) > -> Bitmap Heap Scan on dba_data_base a = (cost=3D32.88..3786.62 rows=3D964 width=3D62) (actual time=3D0.010..0.010 = rows=3D0 loops=3D1) > Filter: (lower(identifier) ~~ = 'bugs0000001884678'::text) > -> Bitmap Index Scan on = in_dba_data_base_identifier (cost=3D0.00..32.64 rows=3D964 width=3D0) = (actual time=3D0.008..0.008 rows=3D0 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'bugs0000001884678'::character varying) > -> Bitmap Heap Scan on dba_data_base a = (cost=3D32.88..3786.62 rows=3D964 width=3D62) (actual time=3D0.010..0.010 = rows=3D0 loops=3D1) > Filter: (lower(identifier) ~~ = 'bugs0000001884679'::text) > -> Bitmap Index Scan on = in_dba_data_base_identifier (cost=3D0.00..32.64 rows=3D964 width=3D0) = (actual time=3D0.008..0.008 rows=3D0 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'bugs0000001884679'::character varying) > -> Bitmap Heap Scan on dba_data_base a = (cost=3D32.88..3786.62 rows=3D964 width=3D62) (actual time=3D0.255..1.676 = rows=3D318 loops=3D1) > Filter: (lower(identifier) ~~ 'sptigr4-2210 = (6f24)'::text) > -> Bitmap Index Scan on = in_dba_data_base_identifier (cost=3D0.00..32.64 rows=3D964 width=3D0) = (actual time=3D0.178..0.178 rows=3D318 loops=3D1) > Index Cond: (lower(identifier) ~=3D~ = 'sptigr4-2210 (6f24)'::character varying) > Total runtime: 4.174 ms >=20 > Also which should scale better if I add more strings to match? would = there be any better design patterns for this problem? >=20 > Thanks for any help >=20 > Adam >=20 > select version(); > version > ---------------------------------------------------------------- > PostgreSQL 8.2.12 on i686-pc-linux-gnu, compiled by GCC 2.95.4 >=20 >=20 >=20 > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >=20 >=20 >=20 > --=20 > --------------------------------------- > Viktor Bojovi=C4=87 > --------------------------------------- > Wherever I go, Murphy goes with me