Talking about optimizer, my long dream

Hi, all.

All this optimizer vs hint thread reminded me about crazy idea that got to
my head some time ago.
I currently has two problems with postgresql optimizer
1) Dictionary tables. Very usual thing is something like "select * from
big_table where distionary_id = (select id from dictionary where
name=value)". This works awful if dictionary_id distribution is not uniform.
The thing that helps is to retrieve subselect value and then simply do
"select * from big_table where dictionary_id=id_value".
2) Complex queries. If there are over 3 levels of subselects, optmizer
counts often become less and less correct as we go up on levels. On ~3rd
level this often lead to wrong choises. The thing that helps is to create
temporary tables from subselects, analyze them and then do main select using
this temporary tables.
While first one can be fixed by introducing some correlation statistics, I
don't think there is any simple way to fix second one.

But what if optimizer could in some cases tell "fetch this and this and then
I'll plan other part of the query based on statistics of what you've
fetched"?

--
Best regards,
Vitalii Tymchyshyn