Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2508A2E0048 for ; Wed, 23 Apr 2008 04:38:31 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03532-05 for ; Wed, 23 Apr 2008 04:38:28 -0300 (ADT) X-Greylist: delayed 00:15:16.013053 by SQLgrey-1.7.5 Received: from ch-smtp01.sth.basefarm.net (ch-smtp01.sth.basefarm.net [80.76.149.212]) by developer.postgresql.org (Postfix) with ESMTP id 7A9B52E0046 for ; Wed, 23 Apr 2008 04:38:28 -0300 (ADT) Received: from c83-254-6-138.bredband.comhem.se ([83.254.6.138]:3235 helo=amin) by ch-smtp01.sth.basefarm.net with esmtps (TLSv1:AES256-SHA:256) (Exim 4.68) (envelope-from ) id 1JoZK0-0002Rp-4N for pgsql-performance@postgresql.org; Wed, 23 Apr 2008 09:23:16 +0200 Received: from hans by amin with local (Exim 4.63) (envelope-from ) id 1JoZJr-00038i-OY for pgsql-performance@postgresql.org; Wed, 23 Apr 2008 09:23:07 +0200 Date: Wed, 23 Apr 2008 09:23:07 +0200 From: Hans Ekbrand To: pgsql-performance@postgresql.org Subject: mysterious difference in speed when combining two queries with OR Message-ID: <20080423072303.GF11886@amin> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="k+G3HLlWI7eRTl+h" Content-Disposition: inline User-Agent: Mutt/1.5.13 (2006-08-11) X-Originating-IP: 83.254.6.138 X-Scan-Result: No virus found in message 1JoZK0-0002Rp-4N. X-Scan-Signature: ch-smtp01.sth.basefarm.net 1JoZK0-0002Rp-4N 1e1a562f5facf9b4b69c4dc749240c3e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/342 X-Sequence-Number: 29835 --k+G3HLlWI7eRTl+h Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable I cannot understand why the following two queries differ so much in executi= on time (almost ten times) Query A (two queries) select distinct moment.mid from moment,timecard where parent =3D 45 and (pi= d=3D17 and timecard.mid =3D moment.mid) order by moment.mid; select distinct moment.mid from moment,timecard where parent =3D 45 and (pb= ar =3D 0) order by moment.mid; Query B (combining the two with OR) select distinct moment.mid from moment,timecard where parent =3D 45 and ((p= id=3D17 and timecard.mid =3D moment.mid) or (pbar =3D 0)) order by moment.m= id; $ time psql -o /dev/null -f query-a.sql fektest real 0m2.016s user 0m1.532s sys 0m0.140s $ time psql -o /dev/null -f query-b.sql fektest real 0m28.534s user 0m1.516s sys 0m0.156s I have tested this in two different computers with different amount of RAM, fast or slow CPU, and the difference is persistent, almost ten times. I should say that this is on postgresql 7.4.16 (debian stable). Can query B be rewritten so that it would execute faster? TIA --=20 Hans Ekbrand (http://sociologi.cjb.net) GPG Fingerprint: 1408 C8D5 1E7D 4C9C C27E 014F 7C2C 872A 7050 614E --k+G3HLlWI7eRTl+h Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIDuPWfCyHKnBQYU4RAiEoAJsEfl1x+GwH4V7MN4rfinOqJDoHLwCg5ALL 04PQ1ci0mAeM1/OS14tfIqQ= =b/ug -----END PGP SIGNATURE----- --k+G3HLlWI7eRTl+h--