Re: Simple join optimized badly?

Bucky Jordan wrote:
>
> Is this along the lines of "I'm loading a big table and touching every
> row of data, so I may as well collect some stats along the way" and "I
> know my data contains these statistical properties, but the analyzer
> wasn't able to figure that out (or maybe can't figure it out efficiently
> enough)"?
>
> While it seems like this would require more knowledge from the user
> (e.g. more about their data, how the planner works, and how it uses
> statistics) this would actually be helpful/required for those who really
> care about performance. ...

The user would have to know his data, but he wouldn't need to know how
the planner works. While with hints like "use index X", he *does* need
to know how the planner works.

Being able to give hints about statistical properties of relations and
their relationships seems like a good idea to me. And we can later
figure out ways to calculate them automatically.

BTW, in DB2 you can declare a table as volatile, which means that the
cardinality of the table varies greatly. The planner favors index scans
on volatile tables.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com