Re: [SQL] New Optimizer Behaviour In 7.0b1

Tom Lane wrote:

> In this case I guess the first question to ask is whether its
> selectivity estimates are any good. It seems to be estimating that your
> "d0.d0f1 between '1999-11-01' and '1999-12-01'" clause will select about
> 100 of the 900 rows in dim0; is that anywhere near right? Also, in the
> nested-loop plan we can see that it thinks about 1500 rows from fact1
> will match on "d0.d0key = f.d0key" against any given selected row from
> dim0; is that on the mark? Finally, is the estimate that the total
> number of joined rows (before GROUP BY) is about 33333 any good?
>
> If you have not done VACUUM ANALYZE recently on these two tables,
> it'd be worth trying that to see if it brings the estimates any
> closer to reality.
>
> regards, tom lane

Tom,

Here is the row data for comparison with the selectivity estimates:

select count(*) from dim0 d0 where d0.d0f1 between '1999-11-01' and
'1999-12-01' 31 rows
select count(*) from fact1 where d0key =
<value>
3000 rows
total number of joined rows before group
by
9000 rows
( i.e : there are only 3 distinct d0key values in fact1 for the "month" ,
and each one has 3000 rows )

It looks like the estimate on the big table ( fact1 ) are right order of
magnitude, but the small table ( dim0 ) ones are too high (and presumably )
throwing the rest off

I did a vacuum analyze of these tables again, just in case....( no change
to the plans)

Cheers

Mark