Received: from leela.one-2-one.net (leela.one-2-one.net [217.115.142.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0HCuWV77104 for ; Thu, 17 Jan 2002 07:56:32 -0500 (EST) (envelope-from twanger@bluetwanger.de) Received: from [192.168.0.14] (p3E9EC18F.dip.t-dialin.net [62.158.193.143]) by leela.one-2-one.net (8.11.0/8.11.0) with ESMTP id g0HCue801412; Thu, 17 Jan 2002 13:56:40 +0100 Subject: Re: reforming query for 7.0.2 From: Markus Bertheau To: Josh Berkus Cc: postgres sql list In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-l/9XbvaWiGvr8K+0q8k6" X-Mailer: Evolution/1.0.1 Date: 17 Jan 2002 13:56:21 +0100 Message-Id: <1011272192.15907.24.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200201/198 X-Sequence-Number: 6284 --=-l/9XbvaWiGvr8K+0q8k6 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Josh, > Markus, >=20 > > how do I reform this query to work with 7.0.2? >=20 > Better question: Why are you working with 7.0.2? Even the mass-market L= inux > distros (like Red Hat and SuSE) now come with 7.1.x. Yeah, I know... An upgrade is definetely in the queue. But it will not happen before 7.2 (if my imagination of its release date is correct), so we will most probably go to that.=20 >=20 > > select * from personen join (select count(personen_id), personen_id from > > orders group by personen_id) as ordertemp on personen.personen_id =3D > > ordertemp.personen_id >=20 > Acutally, this query needs some reforming on its own. While it will work= , the > following version will achieve the same result, much faster, in 7.1 (and= 7.2, > for that matter): >=20 > SELECT personen.field1, personen.field2, personen.field3, count(order_id)= as > no_orders > FROM personen LEFT OUTER JOIN orders ON personen.personen_id =3D > orders.personen_id > GROUP BY personen.field1, personen.field2, personen.field3 >=20 > ... you see, your subselect above is completely unnecessary. And slower = than a > LEFT OUTER JOIN. Simplicity, simplicity, simplicity! Thank you for that.=20 > Now, we have the problem of no LEFT JOIN support in 7.0, so: >=20 > SELECT personen.field1, personen.field2, personen.field3, count(order_id)= as > no_orders > FROM personen JOIN orders ON personen.personen_id =3D orders.personen_id > GROUP BY personen.field1, personen.field2, personen.field3 > UNION > SELECT personen.field1, personen.field2, personen.field3, 0 as no_orders > FROM personen > WHERE NOT EXISTS (SELECT personen_id FROM orders WHERE personen_id =3D > personen.personen_id); Well, I actually don't need the left outer join, because I don't want persons included that have no orders. I think the original query didn't include them. So I turn the left outer join into a join and have the same query on both versions :-). (We use 7.1.3 on our development server). Thanks again. But let's take a look at explain. The old query shows a much smaller cost estimate compared to the new one. Both databases are vacuumed on a regular (daily) basis. First 7.1.3: cenes_test=3D> select version(); version=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 ------------------------------------------------------------- PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) // old query cenes_test=3D> explain select * from personen join (select count(orders_id), personen_id from orders group by personen_id) as ordertemp on ordertemp.personen_id =3D personen.personen_id; NOTICE: QUERY PLAN: Nested Loop (cost=3D11.76..42.32 rows=3D15 width=3D272) -> Subquery Scan ordertemp (cost=3D11.76..12.50 rows=3D15 width=3D8) -> Aggregate (cost=3D11.76..12.50 rows=3D15 width=3D8) -> Group (cost=3D11.76..12.13 rows=3D147 width=3D8) -> Sort (cost=3D11.76..11.76 rows=3D147 width=3D8) -> Seq Scan on orders (cost=3D0.00..6.47 rows=3D147 width=3D8) -> Index Scan using personen_pkey on personen (cost=3D0.00..2.02 rows=3D1 width=3D264) EXPLAIN // new query cenes_test=3D> explain select personen.nachname, personen.vorname, personen.firma, personen.personen_id, count(orders_id) from personen join orders on personen.personen_id =3D orders.personen_id group by personen.nachname, personen.vorname, personen.firma, personen.personen_id; NOTICE: QUERY PLAN: Aggregate (cost=3D162.53..164.37 rows=3D15 width=3D48) -> Group (cost=3D162.53..164.00 rows=3D147 width=3D48) -> Sort (cost=3D162.53..162.53 rows=3D147 width=3D48) -> Merge Join (cost=3D139.59..157.24 rows=3D147 width=3D48) -> Sort (cost=3D127.83..127.83 rows=3D1265 width=3D40) -> Seq Scan on personen (cost=3D0.00..62.65 rows=3D1265 width=3D40) -> Sort (cost=3D11.76..11.76 rows=3D147 width=3D8) -> Seq Scan on orders (cost=3D0.00..6.47 rows=3D147 width=3D8) EXPLAIN // That's a lot more. About row estimates:=20 cenes_test=3D> select count(personen_id) from personen; count=20 ------- 1272 (1 row) cenes_test=3D> select count(orders_id) from orders; count=20 ------- 189 (1 row) cenes_test=3D> select count(orders_id) from orders group by personen_id order by count desc; count=20 ------- 41 33 15 12 10 8 7 5 4 3 2 2 2 2 2 2 2 1 // 36 x 1 snipped (54 rows) On the production db: cenes=3D> select version(); version=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 --------------------------------------------------------------- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (1 row) // old query doesn't work // new query cenes=3D> explain select personen.nachname, personen.vorname, personen.firma, personen.personen_id, count(orders_id) from personen join orders on personen.personen_id =3D orders.personen_id group by personen.nachname, personen.vorname, personen.firma, personen.personen_id; NOTICE: QUERY PLAN: Aggregate (cost=3D201.91..204.87 rows=3D24 width=3D48) -> Group (cost=3D201.91..204.28 rows=3D237 width=3D48) -> Sort (cost=3D201.91..201.91 rows=3D237 width=3D48) -> Merge Join (cost=3D170.85..192.56 rows=3D237 width=3D48) -> Sort (cost=3D18.72..18.72 rows=3D237 width=3D8) -> Seq Scan on orders (cost=3D0.00..9.37 rows=3D237 width=3D8) -> Sort (cost=3D152.13..152.13 rows=3D1500 width=3D40) -> Seq Scan on personen (cost=3D0.00..73.00 rows=3D1500 width=3D40) EXPLAIN cenes=3D> select count(personen_id) from personen; count=20 ------- 1501 (1 row) cenes=3D> select count(orders_id) from orders; count=20 ------- 238 (1 row) count=20 ------- 34 16 12 12 8 7 6 5 4 2 2 2 2 2 2 2 2 2 2 2 2 2 2 1 // the rest is 1 (129 rows) How's that? Markus Bertheau --=-l/9XbvaWiGvr8K+0q8k6 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQA8Rsn1KNEjAb345r4RAr9lAJ494NGbcS893l1whIh9PQQxxadHNQCgg7o8 ZzaYt8Fpy69QLBTJEnjrnyc= =DW4z -----END PGP SIGNATURE----- --=-l/9XbvaWiGvr8K+0q8k6--