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,

Jim Nasby                                            [EMAIL PROTECTED]
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to