pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference between these two queries ?
5+ messages / 4 participants
[nested] [flat]
* Difference between these two queries ?
@ 2010-06-06 02:30 Nilesh Govindarajan <lists@itech7.com>
0 siblings, 3 replies; 5+ messages in thread
From: Nilesh Govindarajan @ 2010-06-06 02:30 UTC (permalink / raw)
To: pgsql-sql; PostgreSQL General <pgsql-general@postgresql.org>
Hi,
I have a doubt about JOINS.
What is the difference between:
1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and
bu.uid = 5;
2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id =
bu.bid AND bu.uid = 5;
What is the first type of join called ?
and is it possible that they have different execution times ?
--
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com
Cheap and Reliable VPS Hosting: http://j.mp/arHk5e
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Difference between these two queries ?
@ 2010-06-06 09:04 Sergey Konoplev <gray.ru@gmail.com>
parent: Nilesh Govindarajan <lists@itech7.com>
2 siblings, 0 replies; 5+ messages in thread
From: Sergey Konoplev @ 2010-06-06 09:04 UTC (permalink / raw)
To: Nilesh Govindarajan <lists@itech7.com>; +Cc: pgsql-sql; PostgreSQL General <pgsql-general@postgresql.org>
On 6 June 2010 06:30, Nilesh Govindarajan <lists@itech7.com> wrote:
> 1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and
> bu.uid = 5;
>
> 2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id =
> bu.bid AND bu.uid = 5;
Here is an explanation:
http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-FROM
>
> What is the first type of join called ?
CROSS JOIN
>
> and is it possible that they have different execution times ?
AFAIK planner would choose the same execution plan in your situation
so it is not.
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Difference between these two queries ?
@ 2010-06-06 15:15 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
parent: Nilesh Govindarajan <lists@itech7.com>
2 siblings, 1 reply; 5+ messages in thread
From: Little, Douglas @ 2010-06-06 15:15 UTC (permalink / raw)
To: Nilesh Govindarajan <lists@itech7.com>; pgsql-sql; PostgreSQL General <pgsql-general@postgresql.org>
Nilesh,
They should generate equivalent results,
But the difference is the constraint on bu.bid=5.
In the 1st case it's being done after the join.
In the 2nd case it is being done before the join.
The end result should be the same, but the execution time can be hugely different.
Suppose b has 1b rows, and bu has 50m with a 20:1 cardinality.
But bu.bid=5 only select 1 row from bu.
If the constaint is applied after the tables are joined the db needs to materialize the entire 1bx50m row set and then
Select out the bid=5 rows.
Doug
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Nilesh Govindarajan
Sent: Saturday, June 05, 2010 9:31 PM
To: PostgreSQL SQL; PostgreSQL General
Subject: [SQL] Difference between these two queries ?
Hi,
I have a doubt about JOINS.
What is the difference between:
1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and
bu.uid = 5;
2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id =
bu.bid AND bu.uid = 5;
What is the first type of join called ?
and is it possible that they have different execution times ?
--
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com
Cheap and Reliable VPS Hosting: http://j.mp/arHk5e
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Difference between these two queries ?
@ 2010-06-06 15:36 Sam Mason <sam@samason.me.uk>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 0 replies; 5+ messages in thread
From: Sam Mason @ 2010-06-06 15:36 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Sun, Jun 06, 2010 at 10:15:52AM -0500, Little, Douglas wrote:
> They should generate equivalent results,
> But the difference is the constraint on bu.bid=5.
> In the 1st case it's being done after the join.
> In the 2nd case it is being done before the join.
In PG version 8.1 (?) and onwards, the query planner knows that they are
equivalent and will choose from the same types of plans. I.e. it's just
a syntax issue, do whichever you think is "prettier", the semantics are
the same.
--
Sam http://samason.me.uk/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Difference between these two queries ?
@ 2010-06-06 16:34 Nilesh Govindarajan <lists@itech7.com>
parent: Nilesh Govindarajan <lists@itech7.com>
2 siblings, 0 replies; 5+ messages in thread
From: Nilesh Govindarajan @ 2010-06-06 16:34 UTC (permalink / raw)
To: pgsql-sql; PostgreSQL General <pgsql-general@postgresql.org>
Thank you all for your explaination.
--
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com
Cheap and Reliable VPS Hosting: http://j.mp/arHk5e
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-06-06 16:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-06 02:30 Difference between these two queries ? Nilesh Govindarajan <lists@itech7.com>
2010-06-06 09:04 ` Sergey Konoplev <gray.ru@gmail.com>
2010-06-06 15:15 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-06-06 15:36 ` Sam Mason <sam@samason.me.uk>
2010-06-06 16:34 ` Nilesh Govindarajan <lists@itech7.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