pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
[SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)
3+ messages / 3 participants
[nested] [flat]

* [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)
@ 1999-09-29 09:06 Stuart Rison <rison@biochemistry.ucl.ac.uk>
  1999-09-29 13:44 ` Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance) Rich Shepard <rshepard@appl-ecosys.com>
  1999-09-29 16:34 ` Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance) Clayton Cottingham <drfrog@smartt.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Stuart Rison @ 1999-09-29 09:06 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: pgsql-sql@hub.org; ridruejo@atm9.com.dtu.dk

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)
  1999-09-29 09:06 [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance) Stuart Rison <rison@biochemistry.ucl.ac.uk>
@ 1999-09-29 13:44 ` Rich Shepard <rshepard@appl-ecosys.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Rich Shepard @ 1999-09-29 13:44 UTC (permalink / raw)
  To: Stuart Rison <rison@biochemistry.ucl.ac.uk>; +Cc: Bruce Momjian <maillist@candle.pha.pa.us>; pgsql-sql@hub.org; ridruejo@atm9.com.dtu.dk

On Wed, 29 Sep 1999, Stuart Rison wrote:

> 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)?

  I like this idea. Every implementation of SQL has its individual
personality, and postgres is no exception. A solutions-oriented list (or
cookbook, if you want) will help everyone. It can be organized by
categories/types of queries (e.g., single table, multiple table; finding
missing values, finding duplicate values) and may be better hosted on the
web site as a table or dynamic (but moderated) list.

  Please add my vote.

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
              Making environmentally-responsible mining happen. (SM)         
                       --------------------------------
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance)
  1999-09-29 09:06 [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance) Stuart Rison <rison@biochemistry.ucl.ac.uk>
@ 1999-09-29 16:34 ` Clayton Cottingham <drfrog@smartt.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Clayton Cottingham @ 1999-09-29 16:34 UTC (permalink / raw)
  To: Stuart Rison <rison@biochemistry.ucl.ac.uk>; +Cc: Bruce Momjian <maillist@candle.pha.pa.us>; pgsql-sql@hub.org; ridruejo@atm9.com.dtu.dk

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!






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-09-29 16:34 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-29 09:06 [SQL] How about a postgreSQL cookbook? (was [SQL] Subselect performance) Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-29 13:44 ` Rich Shepard <rshepard@appl-ecosys.com>
1999-09-29 16:34 ` Clayton Cottingham <drfrog@smartt.com>

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