dynamobi/dynamobi — Issue #154: [LDB-168] provide accurate rowcount information in plans
Author: dynamobi-build
Created: 2011-12-30 02:17:17+00:00
State: open
Labels: LDB, Optimizer

[reporter="jvs", created="Thu, 20 Sep 2007 14:20:50 -0500 (GMT-05:00)"]
Zelaine Fong wrote:

> I did have to go back and fix some of these physical rels (e.g.,

> LhxJoinRel and LcsRowScanRel) to make their rowcounts more reflective of

> what they should be, because not having good counts resulted in bugs.

> But you're right, they still won't be as good as what they were at the

> time the optimizer was selecting join orderings. E.g., information

> about semijoins is no longer available, so star join estimates aren't

> going to be as good as what they were during the optimization phase.

>

> -- Zelaine

>

> John V. Sichi wrote:

>> By the fact that we were careful about cost functions for all of the

>> logical rels used early on, e.g. during join ordering, but not for all

>> of the physical rels.

>>

>> JVS

>>

>> Zelaine Fong wrote:

>>> Obscured in what way?

>>>

>>> -- Zelaine

>>>

>>> John V. Sichi wrote:

>>>> Even with stats, aren't the good rowcount estimates somewhat

>>>> obscured by the time we get to the final physical plan? I've been

>>>> avoiding mentioning them outside of dbdev for that reason.

>>>>

>>>> JVS

>>>>

>>>> Zelaine Fong wrote:

>>>>> The row count number is meaningful if stats are up-to-date. For a

>>>>> straightforward query, it could match closely with the actual row

>>>>> count result of a query. However, for anything complex, I wouldn't

>>>>> expect the numbers to be close.

>>>>>

>>>>> The cumulative cost number should only be used to compare one plan

>>>>> vs another. I'm not sure how else Apps would use that number. The

>>>>> unit of cost doesn't directly match to something physical, like row

>>>>> count.

>>>>>

>>>>> -- Zelaine

>>>>>

>>>>> Haward wrote:

>>>>>> Hi Rushan,

>>>>>>

>>>>>> I could be wrong, but I notice that we can roughly estimate the

>>>>>> cost to execute each line in "explain plan". (see below...)

>>>>>> If it is quite reliable, maybe it is useful for the Apps team.

>>>>>>

>>>>>>

>>>>>> Haward

>>>>>>

>>>>>>

>>>>>> explain plan including all attributes for

>>>>>> select A.c1, B.c1

>>>>>> from A join B

>>>>>> on A.c1 = B.c1

>>>>>> where A.c1=2;

>>>>>>

>>>>>>

>>>>>> FennelToIteratorConverter: rowcount = 2.0, cumulative cost =

>>>>>> 9.414213562373096

>>>>>> FennelReshapeRel(projection=[[1, 0]],

>>>>>> outputRowType=[RecordType(INTEGER C1, INTEGER C10) NOT NULL]):

>>>>>> rowcount = 2.0, cumulative cost = 7.414213562373095

>>>>>> LhxJoinRel(leftKeys=[[0]], rightKeys=[[0]], joinType=[INNER]):

>>>>>> rowcount = 2.0, cumulative cost = 5.414213562373095

>>>>>> LcsRowScanRel(table=[[LOCALDB, HAWARDSCHEMA, B]],

>>>>>> projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$B$C1]]):

>>>>>> rowcount = 2.0, cumulative cost = 2.0

>>>>>> LcsRowScanRel(table=[[LOCALDB, HAWARDSCHEMA, A]],

>>>>>> projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$A$C1]],

>>>>>> residual columns=[[0]]): rowcount = 1.0, cumulative cost =

>>>>>> 1.4142135623730951

>>>>>>FennelValuesRel(tuples=[[{ '[', 2, ']', 2 }]]): rowcount =

>>>>>> 1.0, cumulative cost = 1.0

>>>>>>

>>>>>>

>>>>>

>>>>

>>>

>>

>