Re: Query optimization with X Y JOIN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Yes, that helps a great deal. Thank you so much.

----- Original Message ----- From: "Richard Huxton" <dev@xxxxxxxxxxxx>
To: <J@xxxxxxxxxxx>
Cc: <pgsql-performance@xxxxxxxxxxxxxx>
Sent: Thursday, January 26, 2006 11:47 AM
Subject: Re: [PERFORM] Query optimization with X Y JOIN


J@xxxxxxxxxxx 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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux