Re: Query optimization with X Y JOIN

От: J@Planeti.Biz
Тема: Re: Query optimization with X Y JOIN
Дата: ,
Msg-id: 009a01c6229b$d67c6670$0d310d05@fatchubby
(см: обсуждение, исходный текст)
Ответ на: Physical column size  (Paul Mackay)
Список: pgsql-performance

Скрыть дерево обсуждения

Physical column size  (Paul Mackay, )
 Re: Physical column size  (Mario Weilguni, )
 Query optimization with X Y JOIN  (, )
  Re: Query optimization with X Y JOIN  ("Craig A. James", )
 Re: Query optimization with X Y JOIN  (, )
  Re: Query optimization with X Y JOIN  ("Joshua D. Drake", )
  Re: Query optimization with X Y JOIN  (Richard Huxton, )
 Re: Query optimization with X Y JOIN  (, )

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

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


>  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
>



В списке pgsql-performance по дате сообщения:

От: "Jozsef Szalay"
Дата:
Сообщение: Re: Incorrect Total runtime Reported by Explain Analyze!?
От: Ron
Дата:
Сообщение: Re: [GENERAL] Creation of tsearch2 index is very