From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Re: Help with UNION query
Date: Sun, 4 Sep 2005 20:54:00 +0200
User-Agent: KMail/1.8.91
References: <200509041914.33245.andreak@officenet.no>
	<200509042008.21080.andreak@officenet.no>
In-Reply-To: <200509042008.21080.andreak@officenet.no>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart2287381.jQA9K6LgKj";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200509042054.02993.andreak@officenet.no>

--nextPart2287381.jQA9K6LgKj
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Sunday 04 September 2005 20:08, Andreas Joseph Krogh wrote:
> Replying to my self...

Again...

I managed to "reduse" my query to the following:

SELECT gr.groupname FROM onp_group gr, onp_user u
WHERE gr.id IN
	(
SELECT g.id FROM
	onp_group g, onp_user_group ug
WHERE g.groupname =3D ug.groupname AND ug.username =3D u.username
UNION
SELECT gc.child_id FROM
	onp_group_children gc, onp_group g, onp_user_group ug
WHERE gc.group_id =3D g.id AND g.groupname =3D ug.groupname AND ug.username=
 =3D=20
u.username
	)
AND u.username =3D 'andreak';

But "EXPLAIN ANALYZE" tells me it's about 8x slower than this one:

SELECT gr.groupname FROM onp_group gr WHERE gr.id IN(
SELECT g.id
from onp_group g, onp_user_group ug
where g.groupname =3D ug.groupname AND ug.username =3D 'andreak'
UNION
SELECT gc.child_id
=46ROM onp_group_children gc, onp_group g, onp_user_group ug
WHERE gc.group_id =3D g.id AND g.groupname =3D ug.groupname AND ug.username=
 =3D=20
'andreak'
);

So, the question stands: any idea on how to rewrite the lower wuery to only=
=20
specify "ug.username=3D'andreak'" once?

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Hoffsveien 17           | know how to do a thing and to watch         |
PO. Box 425 Sk=F8yen      | somebody else doing it wrong, without       |
0213 Oslo               | comment.                                    |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+

--nextPart2287381.jQA9K6LgKj
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQBDG0LKUopImDh2gfQRAnnGAKCrJm84s8KvrIwkS4F58LfCAe6bswCggZQT
F14Hfg5Kz8XFGMMygyZPi1c=
=4aMW
-----END PGP SIGNATURE-----

--nextPart2287381.jQA9K6LgKj--