Gavin reported UNION faster than OR in some case when doing fts queries two years ago at O'Reilly.
--------------------------------------------------------------------------- [EMAIL PROTECTED] wrote: > Actually, I have used a UNION to replace OR's, the case (simpliefied to) > something like this: > > Sample 1: > WHERE (f1 = 'v1' OR f1 = '') > AND (f2 = 'v2' OR f2 = '') > > Changed to Sample 2: > WHERE (f1 = 'v1') > AND (f2 = 'v2') > UNION > WHERE (f1 = 'v1') > AND (f2 = '') > UNION > WHERE (f1 = '') > AND (f2 = '') > > > Note that Sample 1 is actually a simplified version, the queries are not > exactly equivalent. > > The point is that sample 2 ran MUCH faster because: > a) The table was *very* large > b) The OR clauses of sample 1 prevented the use of an INDEX, > > Reason: It is faster to scan an index 3 times then scan this very large > table once. > > I do not know if there is a proof to say that one can *always* replace OR's > with a union, but sometimes certainly, and in this case it made things much > better... > > Terry Fielder > Manager Software Development and Deployment > Great Gulf Homes / Ashton Woods Homes > [EMAIL PROTECTED] > Fax: (416) 441-9085 > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus > > Sent: Thursday, July 17, 2003 3:00 PM > > To: Scott Cain; [EMAIL PROTECTED] > > Subject: Re: [SQL] OR vs UNION > > > > > > Scott, > > > > > I have a query that uses a series of ORs and I have heard > > that sometimes > > > this type of query can be rewritten to use UNION instead and be more > > > efficient. > > > > I'd be interested to know where you heard that; as far as I > > know, it could > > only apply to conditional left outer joins. > > > > > select distinct > > f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id > > > from feature f, featureloc fl > > > where > > > (f.type_id = 219 OR > > > f.type_id = 368 OR > > > f.type_id = 514 OR > > > f.type_id = 475 OR > > > f.type_id = 426 OR > > > f.type_id = 456 OR > > > f.type_id = 461 OR > > > f.type_id = 553 OR > > > f.type_id = 89) and > > > fl.srcfeature_id = 1 and > > > f.feature_id = fl.feature_id and > > > fl.fmin <= 2491413 and fl.fmax >= 2485521 > > > > Certainly a query of the above form would not benefit from > > being a union. > > > > For readability, you could use an IN() statement rather than > > a bunch of ORs > > ... this would not help performance, but would make your > > query easier to > > type/read. > > > > -- > > -Josh Berkus > > Aglio Database Solutions > > San Francisco > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
