Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 2A86BB5DBEC for ; Tue, 16 Aug 2011 11:09:16 -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 05076-04 for ; Tue, 16 Aug 2011 14:09:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 6A482B5DBE9 for ; Tue, 16 Aug 2011 11:09:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p7GE98Wd026140; Tue, 16 Aug 2011 10:09:08 -0400 (EDT) To: adam_pgsql cc: pgsql-sql Subject: Re: which is better: using OR clauses or UNION? In-reply-to: References: Comments: In-reply-to adam_pgsql message dated "Tue, 16 Aug 2011 12:39:09 +0100" Date: Tue, 16 Aug 2011 10:09:08 -0400 Message-ID: <26139.1313503748@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.888 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.988 X-Spam-Level: X-Archive-Number: 201108/60 X-Sequence-Number: 35728 adam_pgsql writes: > 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? Your test cases don't seem exactly comparable; in particular I think the second one is benefiting from the first one having already read and cached the relevant disk blocks. Notice how you've got, eg, > -> Bitmap Index Scan on in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347 rows=318 loops=1) > Index Cond: (lower(identifier) ~=~ 'sptigr4-2210 (6f24)'::character varying) versus > -> Bitmap Index Scan on in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual time=0.178..0.178 rows=318 loops=1) > Index Cond: (lower(identifier) ~=~ 'sptigr4-2210 (6f24)'::character varying) Those are the exact same subplan, so any honest comparison should be finding them to take the same amount of time. When the actual readings are different by a factor of several hundred, there's something wrong with your measurement process. In the end this comes down to whether duplicates will be eliminated more efficiently by a BitmapOr step or by sort/uniq on the resulting rows. I'd have to bet on the BitmapOr myself, but it's likely that this is down in the noise compared to the actual disk accesses in any not-fully-cached scenario. Also, if you don't expect the sub-statements to yield any duplicates, or don't care about seeing the same row twice in the output, you should consider UNION ALL instead of UNION. regards, tom lane