Re: Eliminating unnecessary left joins

Поиск
Список
Период
Сортировка
От Ottó Havasvölgyi
Тема Re: Eliminating unnecessary left joins
Дата
Msg-id 34608c0c0704120218r6857672eyc67ffa0efe447f7a@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Eliminating unnecessary left joins  (Jim Nasby <decibel@decibel.org>)
Ответы Re: Eliminating unnecessary left joins  ("Zeugswetter Andreas ADI SD" <ZeugswetterA@spardat.at>)
Список pgsql-hackers
Jim,
 
Maybe odd, but simpler to optimize this way.
 
Your idea would be also a very good optimization, there was already a discussion about that here: http://archives.postgresql.org/pgsql-performance/2006-01/msg00151.php , but that time Tom refused it because it was too expensive and rare. Maybe now he has a different opinion.
However, left join optimization is lot simpler and cheaper, and can be useful not only for O/R mappers, but for efficient vertical partitioning as Simon mentioned.
 
Best regards,
Otto 

 
2007/4/12, Jim Nasby <decibel@decibel.org>:
I agree with others that the way that query is constructed is a bit
odd, but it does bring another optimization to mind: when doing an
inner-join between a parent and child table when RI is defined
between them, if the query only refers to the child table you can
drop the parent table from the join, because each row in the child
table must have one and only one row in the parent.

Use-case: I'll often use views to make it easier to query several
related tables, but not all queries against the views need to hit
every table. IE: if a table has several status fields that have RI to
parent tables that describe what each status is, you sometimes will
query for the status description, sometimes not.

I suspect that checking to see if tables have the right unique keys
or RI would add a noticeable amount of extra work to query planning,
so we might want a GUC to disable it.

On Apr 7, 2007, at 12:45 PM, Ottó Havasvölgyi wrote:

> Sorry, I have left out the PK requirement.
> What Nicolas wrote is right, I also use an O/R mapper and
> inheritance is solved with vertical partitioning. The tables are
> connected to each other with the PK. And the mapper defines views
> for each class with left joins. The mapper generates queries based
> on these views. A high fraction of the joins would be eliminated
> almost in every query.
>
> My simple example:
>
> Class hierarchy and fields:
> Shape (ID, X, Y)
> |
> +-Circle (ID, Radius)
> |
> +-Rectangle (ID, Width, Height)
>
> The mapper creates 3 tables with the columns next to the class name.
> And it creates 3 views. One of them:
>
> RectangleView:  SELECT r."ID" as "ID", s."X" as "X", s."Y" as "Y",
> r."Width" as "Width", r."Height" as "Height" FROM "Rectangle" r
> LEFT JOIN "Shape" s ON ( r.ID=s.ID)
>
> Now if I query Rectangle object IDs, whose Width is greater than 5,
> it will generate this:
>
> SELECT "ID" FROM RectangleView WHERE "Width">5
>
> In this case I don't need to left join the Shape table, because X
> and Y columns are not used.
>
>
> The other typical situation is when I execute more complex, not-O/
> Rmapper-generated SQL commands based on these views for reporting.
> For example the average width of rectangles whose height is greater
> than 10.
> ----------------------------------------------------
>
> This optimization should be also applied to subqueries.
>
>
>
> Is this optimization relatively easy to introduce?
>
> I would gladly work on this, but unfortunately I don't know the
> codebase at all.
> I would really appreciate if someone competent implemented this
> feature in 8.4.
>
> Thank you in advance,
> Otto
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com       512.569.9461 (cell)



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Vista/IPv6
Следующее
От: "Zeugswetter Andreas ADI SD"
Дата:
Сообщение: Re: Eliminating unnecessary left joins