Received: from atm9.com.dtu.dk (atm9.com.dtu.dk [192.38.77.129]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA19400 for ; Tue, 21 Sep 1999 18:46:00 -0400 (EDT) (envelope-from ridruejo@atm9.com.dtu.dk) Received: (from ridruejo@localhost) by atm9.com.dtu.dk (8.8.7/8.8.7) id AAA13731; Wed, 22 Sep 1999 00:54:40 +0200 Message-Id: <199909212254.AAA13731@atm9.com.dtu.dk> Subject: Re: [SQL] Subselect performance In-Reply-To: <199909211918.PAA14232@candle.pha.pa.us> from Bruce Momjian at "Sep 21, 99 03:18:24 pm" To: maillist@candle.pha.pa.us (Bruce Momjian) Date: Tue, 21 Sep 1999 22:54:40 +0000 () Cc: rison@biochemistry.ucl.ac.uk, tgl@sss.pgh.pa.us, pgsql-sql@hub.org From: Daniel Lopez X-Mailer: ELM [version 2.4ME+ PL32 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > It is not your fault. We frequently get reports of this type, and the > behavior of the subquery is very non-intuitive. You would think that a > subquery and a join would have the same performance, but because of the > limitation of subqueries as being nested loop joined, this is not the > case, and subqueries are slower. We tell people to rewrite their query > as EXISTS, but by the time we tell them that, they have already spent > much time trying to figure out why the query is so slow, and I am sure > many people don't even know about the EXISTS workaround. You are right: I spend some time scratching my head, then some time searching the mailing lists and I finally made the query with a EXISTS, which works great for me :) Thanks Can this be a candidate to include in the FAQ? On the same idea, is there any good document out there with all the SQL "recipes" or common practice for things like : "Give me all the rows which have this value in this column more than once, etc" I do it with: select my_index, count(my_index) from my_table group by my_index having count(my_index) > 1; But this is a common query and would be interested in knowing which is the commonly accepted way of doing this Regards Daniel