Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)

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?
> >
>
> <snip - an entry which could also be put into a cookbook>
>
> 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!