Join efficiency

[Russ Brown]

Hello all,

Recently a post on this list made me think a bit about the way in which I
write my queries.

I have always written queries with ordinary joins in this manner:

SELECT * FROM a, b WHERE a.x=b.x;

However I recently saw an laternative syntax:

SELECT * FROM a JOIN b ON a.x=b.x;

Is there any difference between these queries in terms of the speed of
planning or the quality of the plan untimately used? I'd imagine that the
second form provides more information that the planner may be able to use
to make a better plan (or make a good plan more easily), but I've never
had any problems with the first form.

It also seems to me that the second form is more self-documenting, which
is something I'm always in favour of.

I'd appreciate anyone's thought/insight.

Thanks.

--

Russell Brown

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[terry]

NOTE: The first way cannot support OUTER joins, the second way can. Hence sometimes one has to use
the second way for at least some of the joins.

PREVIOUSLY: The second way can allow one to tell the planner a "better way" to join the tables.
Likewise it can also enable the programmer to force the planner into a worse way. Oops!
NOW: I believe that the latest version of postgres (7.4.x) the planner will override the 2nd methods
requested join method if it knows of a better way and can do the better way. (Outer joins need to
be done last, by the nature of them, and so cannot be changed much, there may be other cases where
the planner cannot change the requested plan).

I am not an expert, but this is what I recall from following the list.

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
te***@greatgulfhomes.com
Fax: [phone]

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Russ Brown
Sent: Wednesday, September 01, 2004 7:55 AM
To: pg***********@postgresql.org
Subject: [GENERAL] Join efficiency
Hello all,

Recently a post on this list made me think a bit about the
way in which I
write my queries.

I have always written queries with ordinary joins in this manner:

SELECT * FROM a, b WHERE a.x=b.x;

However I recently saw an laternative syntax:

SELECT * FROM a JOIN b ON a.x=b.x;

Is there any difference between these queries in terms of the
speed of
planning or the quality of the plan untimately used? I'd
imagine that the
second form provides more information that the planner may be
able to use
to make a better plan (or make a good plan more easily), but
I've never
had any problems with the first form.

It also seems to me that the second form is more
self-documenting, which
is something I'm always in favour of.

I'd appreciate anyone's thought/insight.

Thanks.

--

Russell Brown

---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index
scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

Russ Brown wrote:

I have always written queries with ordinary joins in this manner:

SELECT * FROM a, b WHERE a.x=b.x;

However I recently saw an laternative syntax:

SELECT * FROM a JOIN b ON a.x=b.x;

Is there any difference between these queries in terms of the speed of
planning or the quality of the plan untimately used? I'd imagine that
the second form provides more information that the planner may be able
to use to make a better plan (or make a good plan more easily), but
I've never had any problems with the first form.
The first form allows PG to plan however it sees fit. The second will
force the join order to be the same as you specify in the query. This
doesn't matter here, but might with a more complicated query.

With v7.4 and higher, I believe this join forcing is configurable
(join_collapse_limit).
It also seems to me that the second form is more self-documenting,
which is something I'm always in favour of.

I tend to prefer the WHERE form, but that might just be me.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Russ Brown]

Hi, thanks for your reply,

On Wed, 1 Sep 2004 08:10:52 -0400, <te***@ashtonwoodshomes.com> wrote:

NOTE: The first way cannot support OUTER joins, the second way can.
Hence sometimes one has to use
the second way for at least some of the joins.

Yes, I've always done OUTER joins the second way. I suppose it's just the
way I was taught SQL: I was initially taught now to do 'ordinary' joins
using the first syntax, and then taught 'LEFT' joins using the second
syntax when I came to need to use them (I very much leaned SQL 'on the
job', though I know of people who *always* use OUTER joins in their
queries). I'd never considered that there was another syntax!
PREVIOUSLY: The second way can allow one to tell the planner a "better
way" to join the tables.
Likewise it can also enable the programmer to force the planner into a
worse way. Oops!
NOW: I believe that the latest version of postgres (7.4.x) the planner
will override the 2nd methods
requested join method if it knows of a better way and can do the better
way. (Outer joins need to
be done last, by the nature of them, and so cannot be changed much,
there may be other cases where
the planner cannot change the requested plan).

That being the case, would it be true to say that with recent versions of
PostgreSQL they both perform identically, meaning the second could be
considered preferable due to its self-documenting nature (and consistency
with the OUTER JOIN syntax)?
I am not an expert, but this is what I recall from following the list.

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
te***@greatgulfhomes.com
Fax: [phone]

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Russ Brown
Sent: Wednesday, September 01, 2004 7:55 AM
To: pg***********@postgresql.org
Subject: [GENERAL] Join efficiency
Hello all,

Recently a post on this list made me think a bit about the
way in which I
write my queries.

I have always written queries with ordinary joins in this manner:

SELECT * FROM a, b WHERE a.x=b.x;

However I recently saw an laternative syntax:

SELECT * FROM a JOIN b ON a.x=b.x;

Is there any difference between these queries in terms of the
speed of
planning or the quality of the plan untimately used? I'd
imagine that the
second form provides more information that the planner may be
able to use
to make a better plan (or make a good plan more easily), but
I've never
had any problems with the first form.

It also seems to me that the second form is more
self-documenting, which
is something I'm always in favour of.

I'd appreciate anyone's thought/insight.

Thanks.

--

Russell Brown

---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index
scan if your
joining column's datatypes do not match

--

Russell Brown

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[John Sidney-Woollett]

Does anyone know if there is a postgres shorthand for Oracle's (+)
notation to denote an outer join?

eg

SELECT * from a, b where a.x = b.x (+)

John Sidney-Woollett

Richard Huxton wrote:

Russ Brown wrote:

I have always written queries with ordinary joins in this manner:

SELECT * FROM a, b WHERE a.x=b.x;

However I recently saw an laternative syntax:

SELECT * FROM a JOIN b ON a.x=b.x;

Is there any difference between these queries in terms of the speed
of planning or the quality of the plan untimately used? I'd imagine
that the second form provides more information that the planner may
be able to use to make a better plan (or make a good plan more
easily), but I've never had any problems with the first form.

The first form allows PG to plan however it sees fit. The second will
force the join order to be the same as you specify in the query. This
doesn't matter here, but might with a more complicated query.

With v7.4 and higher, I believe this join forcing is configurable
(join_collapse_limit).

It also seems to me that the second form is more self-documenting,
which is something I'm always in favour of.

I tend to prefer the WHERE form, but that might just be me.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

John Sidney-Woollett wrote:

Does anyone know if there is a postgres shorthand for Oracle's (+)
notation to denote an outer join?

eg

SELECT * from a, b where a.x = b.x (+)

Just the standard LEFT JOIN ... afaik

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Michael Paesold]

Russ Brown wrote:

SELECT * FROM a, b WHERE a.x=b.x; SELECT * FROM a JOIN b ON a.x=b.x;

That being the case, would it be true to say that with recent versions of
PostgreSQL they both perform identically, meaning the second could be
considered preferable due to its self-documenting nature (and consistency
with the OUTER JOIN syntax)?

Assuming join_collapse_limit is at it's default or set higher...

As far as I can say from reading the documentation, following the hackers
list and trying out myself: yes, both versions should yield the same
optimized query plan and are therefore equal performance wise.

You can just use the one you prefer.

Best Regards,
Michael Paesold
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

"Russ Brown" <po******@dot4dot.plus.com> writes:

Is there any difference between these queries in terms of the speed of
planning or the quality of the plan untimately used?

http://www.postgresql.org/docs/7.4/s...cit-joins.html
http://www.postgresql.org/docs/7.3/s...cit-joins.html
http://www.postgresql.org/docs/7.2/s...cit-joins.html
http://www.postgresql.org/docs/7.1/s...cit-joins.html

depending on which version you are using. (I think 7.1-7.3
are essentially alike, but 7.4 is not.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Russ Brown]

On Wed, 01 Sep 2004 10:31:07 -0400, Tom Lane <tg*@sss.pgh.pa.us> wrote:

"Russ Brown" <po******@dot4dot.plus.com> writes:

Is there any difference between these queries in terms of the speed of
planning or the quality of the plan untimately used?

http://www.postgresql.org/docs/7.4/s...cit-joins.html
http://www.postgresql.org/docs/7.3/s...cit-joins.html
http://www.postgresql.org/docs/7.2/s...cit-joins.html
http://www.postgresql.org/docs/7.1/s...cit-joins.html

depending on which version you are using. (I think 7.1-7.3
are essentially alike, but 7.4 is not.)

regards, tom lane

Thanks for that: very informative.

I should have spotted that in the manual myself, though it has been nice
reading other people's opinions on the subject too.

Regards.

--

Russell Brown

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Laura Vance]

This thread also brings up the question... what ever happened to the *
notation of the SQL2 standard for LEFT and RIGHT outer joins?

To pull all rows from table 'a' and only those from table 'b' that match
the column criteria.
SELECT * FROM a, b WHERE a.x*=b.x;

To pull all rows from table 'b' and only those from table 'a' that match
the column criteria.
SELECT * FROM a, b WHERE a.x=*b.x;

This notation was always easy to remember when you think of the asterisk
as being a wildcard (or 'all') for its side of the expression.

Russ Brown wrote:

Hello all,

Recently a post on this list made me think a bit about the way in
which I write my queries.

I have always written queries with ordinary joins in this manner:

SELECT * FROM a, b WHERE a.x=b.x;

However I recently saw an laternative syntax:

SELECT * FROM a JOIN b ON a.x=b.x;

Is there any difference between these queries in terms of the speed
of planning or the quality of the plan untimately used? I'd imagine
that the second form provides more information that the planner may
be able to use to make a better plan (or make a good plan more
easily), but I've never had any problems with the first form.

It also seems to me that the second form is more self-documenting,
which is something I'm always in favour of.

I'd appreciate anyone's thought/insight.

Thanks.

--
Thanks,
Laura Vance
Systems Engineer
Winfree Academy Charter Schools
6221 Riverside Dr. Suite 110
Irving, Tx 75039
Web: www.winfreeacademy.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jeff Boes]

Russ Brown wrote:

Is there any difference between these queries in terms of the speed of
planning or the quality of the plan untimately used? I'd imagine that
the second form provides more information that the planner may be able
to use to make a better plan (or make a good plan more easily), but
I've never had any problems with the first form.

Use EXPLAIN:

http://www.postgresql.org/docs/7.4/s...l-explain.html

--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jb***@qtm.net