From: J@Planeti.Biz
To: pgsql-performance@postgresql.org
Subject: Re: Query optimization with X Y JOIN
Date: Thu, 26 Jan 2006 12:13:33 -0500
Message-ID: <009a01c6229b$d67c6670$0d310d05@fatchubby> (raw)
References: <786c2f6d0601260206k2201a548p48af7c7fc085d3b2@mail.gmail.com>
<005d01c6228f$3772dca0$0d310d05@fatchubby>
<43D8F4FD.4040703@modgraph-usa.com>
<008001c62295$156b8fc0$0d310d05@fatchubby>
<43D8FD39.8000408@archonet.com>
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
>
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: J@Planeti.Biz, pgsql-performance@postgresql.org
Subject: Re: Query optimization with X Y JOIN
In-Reply-To: <009a01c6229b$d67c6670$0d310d05@fatchubby>
* 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