Date: Wed, 23 Apr 2008 11:22:09 +0200
From: Hans Ekbrand <hans.ekbrand@sociology.gu.se>
To: pgsql-performance@postgresql.org
Subject: Re: mysterious difference in speed when combining two queries with OR
Message-ID: <20080423092205.GI11886@amin>
References: <20080423072303.GF11886@amin>
	<20080423075810.GB8401@a-kretschmer.de>
	<20080423085704.GG8401@a-kretschmer.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="ZG5hGh9V5E9QzVHS"
Content-Disposition: inline
In-Reply-To: <20080423085704.GG8401@a-kretschmer.de>
User-Agent: Mutt/1.5.13 (2006-08-11)


--ZG5hGh9V5E9QzVHS
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 23, 2008 at 10:57:04AM +0200, A. Kretschmer wrote:
> am  Wed, dem 23.04.2008, um  9:58:10 +0200 mailte A. Kretschmer folgendes:
> > > Query A (two queries)
> > >=20
> > > select distinct moment.mid from moment,timecard where parent =3D 45 a=
nd (pid=3D17 and timecard.mid =3D moment.mid) order by moment.mid;
> > > select distinct moment.mid from moment,timecard where parent =3D 45 a=
nd (pbar =3D 0) order by moment.mid;
> > >=20
> > > Query B (combining the two with OR)
> > >=20
> > > select distinct moment.mid from moment,timecard where parent =3D 45 a=
nd ((pid=3D17 and timecard.mid =3D moment.mid) or (pbar =3D 0)) order by mo=
ment.mid;
>=20
> Thanks to depesz on #postgresql (irc-channel):
>=20
> Query A, the second query: there are no join between the 2 tables.
> Mistake?

No, I just wanted to show the time differences, I haven't used join
before. Now that you have adviced me to, I have tried your suggestion
to rewrite B as a union and it works good! Just as fast as the A Query!

                                                                          Q=
UERY PLAN                                                                  =
       =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------
 Unique  (cost=3D4100.27..4101.39 rows=3D223 width=3D4) (actual time=3D120.=
963..121.124 rows=3D42 loops=3D1)
   ->  Sort  (cost=3D4100.27..4100.83 rows=3D223 width=3D4) (actual time=3D=
120.959..121.008 rows=3D43 loops=3D1)
         Sort Key: mid
         ->  Append  (cost=3D287.66..4091.57 rows=3D223 width=3D4) (actual =
time=3D11.274..120.795 rows=3D43 loops=3D1)
               ->  Subquery Scan "*SELECT* 1"  (cost=3D287.66..290.70 rows=
=3D203 width=3D4) (actual time=3D11.270..11.604 rows=3D41 loops=3D1)
                     ->  Unique  (cost=3D287.66..288.67 rows=3D203 width=3D=
4) (actual time=3D11.264..11.469 rows=3D41 loops=3D1)
                           ->  Sort  (cost=3D287.66..288.16 rows=3D203 widt=
h=3D4) (actual time=3D11.260..11.331 rows=3D57 loops=3D1)
                                 Sort Key: moment.mid
                                 ->  Hash Join  (cost=3D60.98..279.88 rows=
=3D203 width=3D4) (actual time=3D2.563..11.136 rows=3D57 loops=3D1)
                                       Hash Cond: ("outer".mid =3D "inner".=
mid)
                                       ->  Seq Scan on timecard  (cost=3D0.=
00..211.78 rows=3D1017 width=3D4) (actual time=3D0.032..7.156 rows=3D995 lo=
ops=3D1)
                                             Filter: (pid =3D 17)
                                       ->  Hash  (cost=3D59.88..59.88 rows=
=3D444 width=3D4) (actual time=3D2.329..2.329 rows=3D0 loops=3D1)
                                             ->  Seq Scan on moment  (cost=
=3D0.00..59.88 rows=3D444 width=3D4) (actual time=3D0.035..1.980 rows=3D199=
 loops=3D1)
                                                   Filter: (parent =3D 45)
               ->  Subquery Scan "*SELECT* 2"  (cost=3D192.62..3800.87 rows=
=3D20 width=3D4) (actual time=3D0.583..109.073 rows=3D2 loops=3D1)
                     ->  Unique  (cost=3D192.62..3800.67 rows=3D20 width=3D=
4) (actual time=3D0.578..109.061 rows=3D2 loops=3D1)
                           ->  Nested Loop  (cost=3D192.62..3417.57 rows=3D=
153240 width=3D4) (actual time=3D0.576..89.437 rows=3D15324 loops=3D1)
                                 ->  Index Scan using moment_mid_idx on mom=
ent  (cost=3D0.00..160.15 rows=3D20 width=3D4) (actual time=3D0.544..3.527 =
rows=3D2 loops=3D1)
                                       Filter: ((parent =3D 45) AND (pbar =
=3D 0))
                                 ->  Materialize  (cost=3D192.62..269.24 ro=
ws=3D7662 width=3D0) (actual time=3D0.014..21.930 rows=3D7662 loops=3D2)
                                       ->  Seq Scan on timecard  (cost=3D0.=
00..192.62 rows=3D7662 width=3D0) (actual time=3D0.005..14.560 rows=3D7662 =
loops=3D1)
 Total runtime: 122.076 ms
(23 rows)

--=20
Hans Ekbrand (http://sociologi.cjb.net) <hans@sociologi.cjb.net>
A. Because it breaks the logical sequence of discussion
Q. Why is top posting bad?

--ZG5hGh9V5E9QzVHS
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)

iD8DBQFIDv+8fCyHKnBQYU4RAmSNAKC2jis3CZ7o7T2MqBo/EUyJezvigACgvRoC
Qv3JF0+lEKPN5joOT9GF+4U=
=qmAj
-----END PGP SIGNATURE-----

--ZG5hGh9V5E9QzVHS--