pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sequential joins
6+ messages / 3 participants
[nested] [flat]

* sequential joins
@ 2002-03-01 20:21  Oleg Lebedev <olebedev@waterford.org>
  0 siblings, 1 reply; 6+ messages in thread

From: Oleg Lebedev @ 2002-03-01 20:21 UTC (permalink / raw)
  To: pgsql-sql

You guys and gals were really helpful!
I hope you can help me with this problem too.
I have an Activity record, that has fields like artist, designer,
programmer containing ids of users from User table, which are assigned
to the current activity.
What I need to do is create a view, that would contain all the Activity
information, except artist, designer, and programmer should be filled
out with corresponding usernames from the User table.
Here is one way to do this:
Schemas:
Activity: name, artist, designer, programmer
User: username, objectid

SELECT name, artistname, designername, programmername
FROM
(SELECT *
FROM activity a
LEFT OUTER JOIN (SELECT username AS artistname, objectid AS userid FROM
User) n1
ON a.artist = n1.userid
LEFT OUTER JOIN (SELECT username AS designername, objectid AS userid
FROM User) n2
ON a.designer = n2.userid
LEFT OUTER JOIN (SELECT username AS programmername, objectid AS userid
FROM User) n3
ON a.programmer = n3.userid) names;

I wonder if there is a better way to do this. Maybe using CASE WHEN THEN
ELSE END clause to avoid multiple scans?
thanks,

Oleg




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

* Re: sequential joins
@ 2002-03-01 20:28  Josh Berkus <josh@agliodbs.com>
  parent: Oleg Lebedev <olebedev@waterford.org>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2002-03-01 20:28 UTC (permalink / raw)
  To: Oleg Lebedev <olebedev@waterford.org>; pgsql-sql

Oleg,

> I wonder if there is a better way to do this. Maybe using CASE WHEN
>  THEN
> ELSE END clause to avoid multiple scans?
> thanks,

No, not really.  PostgreSQL is pretty good about detecting multiple
 references to the same table in subselects and optimizing your query
 appropriately.  On occassion, I've had to do this with the same table
 sub-selected 50 seperate times and Postgres handles it OK.

Sometimes you can use a straight LEFT OUTER JOIN instead of a
 subselect.  This depends entirely on whether you are planning on doing
 any GROUPing or totals on the main query.  If NOT, then:

SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
FROM tablea 
	LEFT OUTER JOIN (SELECT f2, f3
			 FROM tableb WHERE f4 = "1") alias1
		ON tablea.f1 = alias1.f2
	LEFT OUTER JOIN (SELECT f2, f3
			 FROM tableb WHERE f4 = "2") alias2
		ON tablea.f1 = alias2.f2;

Is equivalent to:

SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
FROM tablea 
	LEFT OUTER JOIN tableb AS alias1
		ON (tablea.f1 = alias1.f2 AND alias1.f4 = "1")
	LEFT OUTER JOIN tableb AS alias2
		ON (tablea.f1 = alias2.f2 AND alias2.f4 = "2") ;

And the second should run a bit faster.


(FYI:  MS SQL Server 7.0 does *not* optimize for multiple subselects on
 the same table.  I recently found this out the hard way, and crashed
 an MS SQL Server despite 1gb of memory in the machine.  The same query
 does OK in Postgres on less hardware)

-Josh Berkus



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

* Re: sequential joins
@ 2002-03-01 22:46  Oleg Lebedev <olebedev@waterford.org>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Oleg Lebedev @ 2002-03-01 22:46 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql

Josh,
the sub-select statement I was using were not for filtering data (in which
case you can move filtering condition in ON part of the join), but rather
renaming the column 'username' with the name I need in the current join
statement. In this case, columns with new names ('artist', 'designer',
'programmer') would be appended to the overall query result and can be
selected by top-level select statement.
This looks like a very clumsy way to do what I need.
Any ideas?
thanks,

Oleg

Josh Berkus wrote:

> Oleg,
>
> > I wonder if there is a better way to do this. Maybe using CASE WHEN
> >  THEN
> > ELSE END clause to avoid multiple scans?
> > thanks,
>
> No, not really.  PostgreSQL is pretty good about detecting multiple
>  references to the same table in subselects and optimizing your query
>  appropriately.  On occassion, I've had to do this with the same table
>  sub-selected 50 seperate times and Postgres handles it OK.
>
> Sometimes you can use a straight LEFT OUTER JOIN instead of a
>  subselect.  This depends entirely on whether you are planning on doing
>  any GROUPing or totals on the main query.  If NOT, then:
>
> SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
> FROM tablea
>         LEFT OUTER JOIN (SELECT f2, f3
>                          FROM tableb WHERE f4 = "1") alias1
>                 ON tablea.f1 = alias1.f2
>         LEFT OUTER JOIN (SELECT f2, f3
>                          FROM tableb WHERE f4 = "2") alias2
>                 ON tablea.f1 = alias2.f2;
>
> Is equivalent to:
>
> SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
> FROM tablea
>         LEFT OUTER JOIN tableb AS alias1
>                 ON (tablea.f1 = alias1.f2 AND alias1.f4 = "1")
>         LEFT OUTER JOIN tableb AS alias2
>                 ON (tablea.f1 = alias2.f2 AND alias2.f4 = "2") ;
>
> And the second should run a bit faster.
>
> (FYI:  MS SQL Server 7.0 does *not* optimize for multiple subselects on
>  the same table.  I recently found this out the hard way, and crashed
>  an MS SQL Server despite 1gb of memory in the machine.  The same query
>  does OK in Postgres on less hardware)
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




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

* Re: sequential joins
@ 2002-03-01 23:13  Josh Berkus <josh@agliodbs.com>
  parent: Oleg Lebedev <olebedev@waterford.org>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2002-03-01 23:13 UTC (permalink / raw)
  To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql

Oleg,

> the sub-select statement I was using were not for filtering data (in
>  which
> case you can move filtering condition in ON part of the join), but
>  rather
> renaming the column 'username' with the name I need in the current
>  join
> statement. In this case, columns with new names ('artist',
>  'designer',
> 'programmer') would be appended to the overall query result and can
>  be
> selected by top-level select statement.
> This looks like a very clumsy way to do what I need.
> Any ideas?

No, now I'm confused.  Can you explain better what you are trying to
 achieve as a result?

-Josh







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

* Re: sequential joins
@ 2002-03-01 23:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Oleg Lebedev <olebedev@waterford.org>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2002-03-01 23:43 UTC (permalink / raw)
  To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql

Oleg Lebedev <olebedev@waterford.org> writes:
> SELECT *
> FROM
> activity a
> LEFT OUTER JOIN
>         (SELECT     username AS artistname,
>                             objectid AS userid
>         FROM         user) u1
> ON a.artist = u1.userid;

This seems like the hard way compared to

SELECT a.*, u1.username AS artistname, u1.objectid AS userid
       ... more fields here ...
FROM
activity a
LEFT OUTER JOIN user u1 ON a.artist = u1.userid
... more JOINs here ...

I think that you will in fact end up with the same plan, but that's
only because the PG planner is smart enough to convert the first
into the second.  Might as well save the notational cruft to begin with.

			regards, tom lane



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

* Re: sequential joins
@ 2002-03-01 23:47  Oleg Lebedev <olebedev@waterford.org>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Oleg Lebedev @ 2002-03-01 23:47 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql

Ok, here it is:
SELECT *
FROM
activity a
LEFT OUTER JOIN
        (SELECT     username AS artistname,
                            objectid AS userid
        FROM         user) u1
ON a.artist = u1.userid;

After this join, I would have a view, that contains original activity table
data, appended with column artistname, which would contain artist username,
referenced from this activity record.
If I join the result with
        (SELECT     username AS programmername,
                            objectid AS userid
        FROM         user) u2
ON a.programmer = u2.userid
I would have programmername column appended, containing programmers username,
etc.
I hope this clarifies the issue.
thanks,


Josh Berkus wrote:

> Oleg,
>
> > the sub-select statement I was using were not for filtering data (in
> >  which
> > case you can move filtering condition in ON part of the join), but
> >  rather
> > renaming the column 'username' with the name I need in the current
> >  join
> > statement. In this case, columns with new names ('artist',
> >  'designer',
> > 'programmer') would be appended to the overall query result and can
> >  be
> > selected by top-level select statement.
> > This looks like a very clumsy way to do what I need.
> > Any ideas?
>
> No, now I'm confused.  Can you explain better what you are trying to
>  achieve as a result?
>
> -Josh
>
>   ------------------------------------------------------------------------
>
>   ------------------------------------------------------------------------
>
>   ------------------------------------------------------------------------




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


end of thread, other threads:[~2002-03-01 23:47 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-01 20:21 sequential joins Oleg Lebedev <olebedev@waterford.org>
2002-03-01 20:28 ` Josh Berkus <josh@agliodbs.com>
2002-03-01 22:46   ` Oleg Lebedev <olebedev@waterford.org>
2002-03-01 23:13     ` Josh Berkus <josh@agliodbs.com>
2002-03-01 23:47       ` Oleg Lebedev <olebedev@waterford.org>
2002-03-01 23:43         ` Tom Lane <tgl@sss.pgh.pa.us>

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