pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery optimization with X Y JOIN
7+ messages / 5 participants
[nested] [flat]
* Query optimization with X Y JOIN
@ 2006-01-26 15:43 J@Planeti.Biz
2006-01-26 16:12 ` Re: Query optimization with X Y JOIN Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 7+ messages in thread
From: J@Planeti.Biz @ 2006-01-26 15:43 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Hey guys, how u been. This is quite a newbie question, but I need to ask it. I'm trying to wrap my mind around the syntax of join and why and when to use it. I understand the concept of making a query go faster by creating indexes, but it seems that when I want data from multiple tables that link together the query goes slow. The slow is typically due to expensive nested loops. The reason is, all my brain understands is:
select
tablea.data
tableb.data
tablec.data
from
tablea
tableb
tablec
where
tablea.pri_key = tableb.foreign_key AND
tableb.pri_key = tablec.foreign_key AND...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization with X Y JOIN
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
@ 2006-01-26 16:12 ` Craig A. James <cjames@modgraph-usa.com>
2006-01-26 16:25 ` Re: Query optimization with X Y JOIN J@Planeti.Biz
0 siblings, 1 reply; 7+ messages in thread
From: Craig A. James @ 2006-01-26 16:12 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
First, this isn't really the right place to ask -- this forum is about performance, not SQL syntax.
Second, this isn't a question anyone can answer in a reasonable length of time. What you're asking for usually is taught in a class on relational database theory, which is typically a semester or two in college.
If you really need a crash course, dig around on the web for terms like "SQL Tutorial".
Good luck,
Craig
J@Planeti.Biz wrote:
> Hey guys, how u been. This is quite a newbie question, but I need to ask
> it. I'm trying to wrap my mind around the syntax of join and why and
> when to use it. I understand the concept of making a query go faster by
> creating indexes, but it seems that when I want data from multiple
> tables that link together the query goes slow. The slow is typically due
> to expensive nested loops. The reason is, all my brain understands is:
>
> select
> tablea.data
> tableb.data
> tablec.data
> from
> tablea
> tableb
> tablec
> where
> tablea.pri_key = tableb.foreign_key AND
> tableb.pri_key = tablec.foreign_key AND...
>
> From what I read, it seems you can use inner/outer right/left join on
> (bla) but when I see syntax examples I see that sometimes tables are
> omitted from the 'from' section of the query and other times, no.
> Sometimes I see that the join commands are nested and others, no and
> sometimes I see joins syntax that only applies to one table. From what I
> understand join can be used to tell the database the fast way to murge
> table data together to get results by specifiying the table that has the
> primary keys and the table that has the foreign keys.
>
> I've read all through the postgres docs on this command and I'm still
> left lost. Can someone please explain to me in simple language how to
> use these commands or provide me with a link. I need it to live right
> now. Thanx.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization with X Y JOIN
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:12 ` Re: Query optimization with X Y JOIN Craig A. James <cjames@modgraph-usa.com>
@ 2006-01-26 16:25 ` J@Planeti.Biz
2006-01-26 16:33 ` Re: [PERFORM] Query optimization with X Y JOIN Jaime Casanova <systemguards@gmail.com>
2006-01-26 16:34 ` Re: Query optimization with X Y JOIN Joshua D. Drake <jd@commandprompt.com>
2006-01-26 16:47 ` Re: Query optimization with X Y JOIN Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 7+ messages in thread
From: J@Planeti.Biz @ 2006-01-26 16:25 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
If I want my database to go faster, due to X then I would think that the
issue is about performance. I wasn't aware of a paticular constraint on X.
I have more that a rudementary understanding of what's going on here, I was
just hoping that someone could shed some light on the basic principal of
this JOIN command and its syntax. Most people I ask, don't give me straight
answers and what I have already read on the web is not very helpful thus
far.
----- Original Message -----
From: "Craig A. James" <cjames@modgraph-usa.com>
To: <pgsql-performance@postgresql.org>
Sent: Thursday, January 26, 2006 11:12 AM
Subject: Re: [PERFORM] Query optimization with X Y JOIN
> First, this isn't really the right place to ask -- this forum is about
> performance, not SQL syntax.
>
> Second, this isn't a question anyone can answer in a reasonable length of
> time. What you're asking for usually is taught in a class on relational
> database theory, which is typically a semester or two in college.
>
> If you really need a crash course, dig around on the web for terms like
> "SQL Tutorial".
>
> Good luck,
> Craig
>
>
> J@Planeti.Biz wrote:
>> Hey guys, how u been. This is quite a newbie question, but I need to ask
>> it. I'm trying to wrap my mind around the syntax of join and why and when
>> to use it. I understand the concept of making a query go faster by
>> creating indexes, but it seems that when I want data from multiple tables
>> that link together the query goes slow. The slow is typically due to
>> expensive nested loops. The reason is, all my brain understands is:
>> select
>> tablea.data
>> tableb.data
>> tablec.data
>> from
>> tablea
>> tableb
>> tablec
>> where
>> tablea.pri_key = tableb.foreign_key AND
>> tableb.pri_key = tablec.foreign_key AND...
>> From what I read, it seems you can use inner/outer right/left join on
>> (bla) but when I see syntax examples I see that sometimes tables are
>> omitted from the 'from' section of the query and other times, no.
>> Sometimes I see that the join commands are nested and others, no and
>> sometimes I see joins syntax that only applies to one table. From what I
>> understand join can be used to tell the database the fast way to murge
>> table data together to get results by specifiying the table that has the
>> primary keys and the table that has the foreign keys.
>> I've read all through the postgres docs on this command and I'm still
>> left lost. Can someone please explain to me in simple language how to use
>> these commands or provide me with a link. I need it to live right now.
>> Thanx.
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Query optimization with X Y JOIN
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:12 ` Re: Query optimization with X Y JOIN Craig A. James <cjames@modgraph-usa.com>
2006-01-26 16:25 ` Re: Query optimization with X Y JOIN J@Planeti.Biz
@ 2006-01-26 16:33 ` Jaime Casanova <systemguards@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Jaime Casanova @ 2006-01-26 16:33 UTC (permalink / raw)
To: J@planeti.biz <J@planeti.biz>; +Cc: pgsql-sql
On 1/26/06, J@planeti.biz <J@planeti.biz> wrote:
> If I want my database to go faster, due to X then I would think that the
> issue is about performance. I wasn't aware of a paticular constraint on X.
>
> I have more that a rudementary understanding of what's going on here, I was
> just hoping that someone could shed some light on the basic principal of
> this JOIN command and its syntax. Most people I ask, don't give me straight
> answers and what I have already read on the web is not very helpful thus
> far.
http://www.postgresql.org/docs/current/static/sql-select.html
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization with X Y JOIN
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:12 ` Re: Query optimization with X Y JOIN Craig A. James <cjames@modgraph-usa.com>
2006-01-26 16:25 ` Re: Query optimization with X Y JOIN J@Planeti.Biz
@ 2006-01-26 16:34 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 7+ messages in thread
From: Joshua D. Drake @ 2006-01-26 16:34 UTC (permalink / raw)
To: J@Planeti.Biz; +Cc: pgsql-performance@postgresql.org
J@Planeti.Biz wrote:
> If I want my database to go faster, due to X then I would think that
> the issue is about performance. I wasn't aware of a paticular
> constraint on X.
>
> I have more that a rudementary understanding of what's going on here,
> I was just hoping that someone could shed some light on the basic
> principal of this JOIN command and its syntax. Most people I ask,
> don't give me straight answers and what I have already read on the web
> is not very helpful thus far.
What you are looking for is here:
http://sqlzoo.net/
It is an excellent website that discusses in depth but at a tutorial
style level how and what SQL is and how to use it. Including JOINS.
FYI, a JOIN is basically a FROM with an integrated WHERE clause. That is
a very simplified description and isn't 100% accurate
but it is close. I strongly suggest the website I mentioned above as it
will resolve your question.
Joshua D. Drake
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization with X Y JOIN
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:12 ` Re: Query optimization with X Y JOIN Craig A. James <cjames@modgraph-usa.com>
2006-01-26 16:25 ` Re: Query optimization with X Y JOIN J@Planeti.Biz
@ 2006-01-26 16:47 ` Richard Huxton <dev@archonet.com>
2006-01-26 17:13 ` Re: Query optimization with X Y JOIN J@Planeti.Biz
2 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2006-01-26 16:47 UTC (permalink / raw)
To: J@Planeti.Biz; +Cc: pgsql-performance@postgresql.org
J@Planeti.Biz wrote:
> If I want my database to go faster, due to X then I would think that the
> issue is about performance. I wasn't aware of a paticular constraint on X.
You haven't asked a performance question yet though.
> I have more that a rudementary understanding of what's going on here, I
> was just hoping that someone could shed some light on the basic
> principal of this JOIN command and its syntax. Most people I ask, don't
> give me straight answers and what I have already read on the web is not
> very helpful thus far.
OK - firstly it's not a JOIN command. It's a SELECT query that happens
to join (in your example) three tables together. The syntax is specified
in the SQL reference section of the manuals, and I don't think it's
different from the standard SQL spec here.
A query that joins two or more tables (be they real base-tables, views
or sub-query result-sets) produces the product of both. Normally you
don't want this so you apply constraints to that join (table_a.col1 =
table_b.col2).
In some cases you want all the rows from one side of a join, whether or
not you get a match on the other side of the join. This is called an
outer join and results in NULLs for all the columns on the "outside" of
the join. A left-join returns all rows from the table on the left of the
join, a right-join from the table on the right of it.
When planning a join, the planner will try to estimate how many matches
it will see on each side, taking into account any extra constraints (you
might want only some of the rows in table_a anyway). It then decides
whether to use any indexes on the relevant column(s).
Now, if you think the planner is making a mistake we'll need to see the
output of EXPLAIN ANALYSE for the query and will want to know that
you've vacuumed and analysed the tables in question.
Does that help at all?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization with X Y JOIN
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:12 ` Re: Query optimization with X Y JOIN Craig A. James <cjames@modgraph-usa.com>
2006-01-26 16:25 ` Re: Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:47 ` Re: Query optimization with X Y JOIN Richard Huxton <dev@archonet.com>
@ 2006-01-26 17:13 ` J@Planeti.Biz
0 siblings, 0 replies; 7+ messages in thread
From: J@Planeti.Biz @ 2006-01-26 17:13 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Yes, that helps a great deal. Thank you so much.
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: <J@planeti.biz>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, January 26, 2006 11:47 AM
Subject: Re: [PERFORM] Query optimization with X Y JOIN
> J@Planeti.Biz wrote:
>> If I want my database to go faster, due to X then I would think that the
>> issue is about performance. I wasn't aware of a paticular constraint on
>> X.
>
> You haven't asked a performance question yet though.
>
>> I have more that a rudementary understanding of what's going on here, I
>> was just hoping that someone could shed some light on the basic principal
>> of this JOIN command and its syntax. Most people I ask, don't give me
>> straight answers and what I have already read on the web is not very
>> helpful thus far.
>
> OK - firstly it's not a JOIN command. It's a SELECT query that happens to
> join (in your example) three tables together. The syntax is specified in
> the SQL reference section of the manuals, and I don't think it's different
> from the standard SQL spec here.
>
> A query that joins two or more tables (be they real base-tables, views or
> sub-query result-sets) produces the product of both. Normally you don't
> want this so you apply constraints to that join (table_a.col1 =
> table_b.col2).
>
> In some cases you want all the rows from one side of a join, whether or
> not you get a match on the other side of the join. This is called an outer
> join and results in NULLs for all the columns on the "outside" of the
> join. A left-join returns all rows from the table on the left of the join,
> a right-join from the table on the right of it.
>
> When planning a join, the planner will try to estimate how many matches it
> will see on each side, taking into account any extra constraints (you
> might want only some of the rows in table_a anyway). It then decides
> whether to use any indexes on the relevant column(s).
>
> Now, if you think the planner is making a mistake we'll need to see the
> output of EXPLAIN ANALYSE for the query and will want to know that you've
> vacuumed and analysed the tables in question.
>
> Does that help at all?
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-01-26 17:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-26 15:43 Query optimization with X Y JOIN J@Planeti.Biz
2006-01-26 16:12 ` Craig A. James <cjames@modgraph-usa.com>
2006-01-26 16:25 ` J@Planeti.Biz
2006-01-26 16:33 ` Jaime Casanova <systemguards@gmail.com>
2006-01-26 16:34 ` Joshua D. Drake <jd@commandprompt.com>
2006-01-26 16:47 ` Richard Huxton <dev@archonet.com>
2006-01-26 17:13 ` J@Planeti.Biz
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