| Subject | Re: [firebird-support] default plan no good for many joins? |
|---|---|
| Author | Helen Borrie |
| Post date | 2003-07-17T04:39:46Z |
At 03:04 AM 17/07/2003 +0000, you wrote:
this plan, then the reason is that you failed to include the table
identifier on the where criterion. It should be I.OUTS_QTY > 0 in any
case (and should have failed in Firebird 1.5).
in predicting what the optimizer will do. Even so, if your indexing and
housekeeping are good, the optimizer generally knows best. It is
cost-based, not rule-based.
The better approach is to focus on having structures and writing queries
for which the optimizer will do the best plan **for the
conditions**. Indexing a high-selectivity column that you are going to
select on or order by is generally a good way to speed up a query,
especially one like this, designed to denormalize a lot of lookup
relationships.
A general observation: making your Item lookup columns nullable
(presumably the reason for these outer joins) is a recipe for slow
retrieval. If, in fact, they are not nullable, then use inner joins. It's
productive to think about the end use of things like lookup columns. The
small amount of extra application code required to enforce non-nullable
lookups is far outweighed by the gains in query speed and integrity.
heLen
>Dear all,If you have an index on PO_ITEM.OUTS_QTY that the optimizer isn't using in
>
>Is the following query considered poor if executed with the server
>default plan:
>
>SELECT I.ID
> , P.PN
> , B.NAME
> , O.PO_NO
> , S.SUPP_NO
> , I.QTY
> , I.OUTS_QTY
> , U.NAME
> , E.EMP_NO
>
>FROM PO_ITEM I
> LEFT OUTER JOIN PO O ON O.ID = I.PO_ID
> LEFT OUTER JOIN PRODUCT P ON P.ID = I.PRODUCT_ID
> LEFT OUTER JOIN BRAND B ON B.ID = P.BRAND_ID
> LEFT OUTER JOIN UNIT U ON U.ID = P.UNIT_ID
> LEFT OUTER JOIN SUPPLIER S ON S.ID = O.SUPPLIER_ID
> LEFT OUTER JOIN EMPLOYEE E ON E.ID = O.EMPLOYEE_ID
>
>WHERE
> OUTS_QTY > 0
this plan, then the reason is that you failed to include the table
identifier on the where criterion. It should be I.OUTS_QTY > 0 in any
case (and should have failed in Firebird 1.5).
>Server default plan is:No, specifying a custom plan isn't a great idea unless you are very expert
>PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (I NATURAL,O INDEX
>(RDB$PRIMARY26)),P INDEX (RDB$PRIMARY30)),B INDEX (RDB$PRIMARY3)),U
>INDEX (RDB$PRIMARY37)),S INDEX (RDB$PRIMARY36)),E INDEX
>(RDB$PRIMARY17))
>
>Do I need to specify a custom plan every time for such kind of query?
in predicting what the optimizer will do. Even so, if your indexing and
housekeeping are good, the optimizer generally knows best. It is
cost-based, not rule-based.
The better approach is to focus on having structures and writing queries
for which the optimizer will do the best plan **for the
conditions**. Indexing a high-selectivity column that you are going to
select on or order by is generally a good way to speed up a query,
especially one like this, designed to denormalize a lot of lookup
relationships.
A general observation: making your Item lookup columns nullable
(presumably the reason for these outer joins) is a recipe for slow
retrieval. If, in fact, they are not nullable, then use inner joins. It's
productive to think about the end use of things like lookup columns. The
small amount of extra application code required to enforce non-nullable
lookups is far outweighed by the gains in query speed and integrity.
heLen