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
