From: Clayton Cottingham <drfrog@smartt.com>
To: Stuart Rison <rison@biochemistry.ucl.ac.uk>
Cc: Bruce Momjian <maillist@candle.pha.pa.us>
Cc: pgsql-sql@hub.org
Cc: ridruejo@atm9.com.dtu.dk
Subject: Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)
Date: Wed, 29 Sep 1999 16:34:51 +0000
Message-ID: <37F23FAB.B91F91C6@smartt.com> (raw)
References: <Pine.LNX.4.10.9909290955520.30104-100000@bsmlx17>
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: drfrog@smartt.com, rison@biochemistry.ucl.ac.uk, maillist@candle.pha.pa.us, pgsql-sql@hub.org, ridruejo@atm9.com.dtu.dk
Subject: Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)
In-Reply-To: <37F23FAB.B91F91C6@smartt.com>
References: <Pine.LNX.4.10.9909290955520.30104-100000@bsmlx17> <37F23FAB.B91F91C6@smartt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox