Re: Query optimization with X Y JOIN

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
>