Received: from dfw-ix5.ix.netcom.com (dfw-ix5.ix.netcom.com [206.214.98.5]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA04555 for ; Wed, 29 Sep 1999 12:29:56 -0400 (EDT) (envelope-from drfrog@smartt.com) Received: (from smap@localhost) by dfw-ix5.ix.netcom.com (8.8.4/8.8.4) id LAA00428; Wed, 29 Sep 1999 11:25:44 -0500 (CDT) Received: from 00-10-4b-66-ed-f1.bconnected.net(209.53.22.49) by dfw-ix5.ix.netcom.com via smap (V1.3) id rma000022; Wed Sep 29 11:23:56 1999 Sender: DrFrog@ix.netcom.com Message-ID: <37F23FAB.B91F91C6@smartt.com> Date: Wed, 29 Sep 1999 16:34:51 +0000 From: Clayton Cottingham X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.3.2 i586) X-Accept-Language: en MIME-Version: 1.0 To: Stuart Rison CC: Bruce Momjian , pgsql-sql@hub.org, ridruejo@atm9.com.dtu.dk Subject: Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance) References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Stuart Rison wrote: > Dear All, > > I think Daniel's idea is brilliant. Why don't we set up some sort of > 'postgreSQL cookbook' where everybody puts in their "tricks of the trade" > (for the moment I'm thinking mostly SQL related)? > > A first pass of the FAQ should generate quite a few of these; like the > "SELECT my_index, count(my_index) from my_table group by my_index having > count(my_index) > 1" example below or Bruce's new FAQ item re: getting a > more efficient IN subquery equivalent. > > Perhaps we could even have a (moderated?) newsgroup where we could cc: > cookbook like entries that appear in the other pgsql newsgroups. > > Just ideas, for the moment I saddly have no time to make a start on this > (paper deadline looming ominously close) but I'm holding this thought! > > Any comments? > > Cheers, > > Stuart. > > On Tue, 28 Sep 1999, Bruce Momjian wrote: > > > > > > > > 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; > > > > > New FAQ: > > > > 4.23) Why are my subqueries using IN so slow? > > > > > > Stuart C. G. Rison > Department of Biochemistry and Molecular Biology > 6th floor, Darwin Building, University College London (UCL) > Gower Street, London, WC1E 6BT, United Kingdom > Tel. 0207 504 2303, Fax. 0207 380 7033 > e-mail: rison@biochem.ucl.ac.uk > > ************ this is a great idea! ive got the irc.stampede.org #dbms we could discuss this on or right here!