Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E8BEAB5DBBE for ; Tue, 16 Aug 2011 08:39:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 13347-07 for ; Tue, 16 Aug 2011 11:39:33 +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 5DB4BB5D803 for ; Tue, 16 Aug 2011 08:39:32 -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 1QtHzC-0003mL-0g for pgsql-sql@postgresql.org; Tue, 16 Aug 2011 12:39:10 +0100 From: adam_pgsql Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: which is better: using OR clauses or UNION? Date: Tue, 16 Aug 2011 12:39:09 +0100 Message-Id: To: pgsql-sql Mime-Version: 1.0 (Apple Message framework v1084) X-Mailtags-Version: 2.5 X-Mailtags-Timestamp: 2011-08-16 12:39:09 +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: 1QtHzC-0003mL-0g X-SGUL-MailScanner-MH1: Found to be clean X-SGUL-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-2.9, required 5, autolearn=not spam, ALL_TRUSTED -1.00, BAYES_00 -1.90) 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/56 X-Sequence-Number: 35724 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: table "dba_data_base", index: "in_dba_data_base_identifier" btree (lower(identifier) = varchar_pattern_ops) 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 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; 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 Also which should scale better if I add more strings to match? would = there be any better design patterns for this problem? Thanks for any help Adam select version(); version =20 ---------------------------------------------------------------- PostgreSQL 8.2.12 on i686-pc-linux-gnu, compiled by GCC 2.95.4