--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@[EMAIL PROTECTED]
>
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+8fCyHKnBQYU4RAmS****C2jis3CZ7o7T2MqBo/EUyJezvigACgvRoC
Qv3JF0+lEKPN5joOT9GF+4U=
=qmAj
-----END PGP SIGNATURE-----
--ZG5hGh9V5E9QzVHS--
|
