pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zalman Stern <zalman@netcom.com>
To: osk@hem.passagen.se
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] query with subquery abnormally slow?
Date: Tue, 2 Nov 1999 00:42:41 -0800 (PST)
Message-ID: <199911020842.AAA23090@netcom.com> (raw)
In-Reply-To: <19991101194707.A7034@oskar>

I inadvertently deleted Oskar's message where he described what he is
trying to do at a higher level, but I don't think I'm completely missing
the point with the following:

The table looks like so:

select * from test;
[
ssmldb=> select * from test;
package               |artist             |song
----------------------+-------------------+-----------------
Surf Comp             |Mermen             |Pulpin
Surf Comp             |Bambi Molesters    |Tremor
Surf Comp             |The Squares        |Squaranoid
Dumb Loud Hollow Twang|Bambi Molesters    |Point Break
Dumb Loud Hollow Twang|Bambi Molesters    |Glider
Songs of the Cows     |Mermen             |Songs of the Cows
Surfmania             |The Aqua Velvets   |Surfmania
Surf Comp 2           |Mermen             |Slo Mo H50
Surf Comp 2           |Los Straightjackets|Caveman
]

select t2.* from test t1, test t2
    where t1.package = t2.package and
	  t1.artist = 'Mermen' and t2.artist != 'Mermen';
[
package    |artist             |song
-----------+-------------------+----------
Surf Comp  |Bambi Molesters    |Tremor
Surf Comp  |The Squares        |Squaranoid
Surf Comp 2|Los Straightjackets|Caveman
(3 rows)
]

The query above shows all songs by a different artist that share an album
with a song by the artist in question. It however omits the songs by the
artist in question. To get those back, you could try:

select distinct t1.* from test t1, test t2
    where t1.package = t2.package and
	  ((t1.artist = 'Mermen' and t2.artist != 'Mermen') or
	   (t1.artist != 'Mermen' and t2.artist = 'Mermen'));

or use a union clause:
select t1.* from test t1, test t2
    where t1.package = t2.package and
	  (t2.artist = 'Mermen' and t1.artist != 'Mermen')
    union select t3.* from test t3, test t4
		  where t3.package = t4.package and
			(t3.artist = 'Mermen' and t4.artist != 'Mermen') ;

I don't know how these do for speed as I don't care to create a big table
and indices and all that, but they do not use EXITS and it seems with
suitable indices they should be fairly fast.

-Z-




view thread (8+ messages)

Message-ID: <199911020842.AAA23090@netcom.com>
Permalink:  ../199911020842.AAA23090@netcom.com/
Also on:    postgresql.org/message-id/199911020842.AAA23090@netcom.com

 · 

reply

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: zalman@netcom.com, osk@hem.passagen.se
  Subject: Re: [SQL] query with subquery abnormally slow?
  In-Reply-To: <199911020842.AAA23090@netcom.com>
  References: <19991101194707.A7034@oskar> <199911020842.AAA23090@netcom.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