Re: Huge Performance Difference on Similar Query in Pg7.2

Heiko Klein <Heiko(dot)Klein(at)met(dot)no> writes:
> When I now perform the two similar (results equal) queries on those
> rows:
> 1)
> select count(*) from EmissionsView, DataSetsView
> where DataSetsView.setid = EmissionsView.setid
> and EmissionsView.setid = '4614' ;
> -------------
> 2)
> select count(*) from EmissionsView, DataSetsView
> where DataSetsView.setid = EmissionsView.setid
> and DataSetsView.setid = '4614' ;
> ------------

> I have a huge performance difference.

Apparently the condition EmissionsView.setid = '4614' is a lot more
selective on that table than DataSetsView.setid = '4614' is on its
table. So pushing down the former condition translates into lots
fewer rows to be joined than pushing down the latter.

Unfortunately the planner is not very bright about transitivity of
equals and so does not realize that it could derive EmissionsView.setid
= '4614' from the other two conditions. I imagine the reason Oracle
gives similar behavior for both queries is that it does expend the
cycles to make that deduction. (This is a guess though; it'd be
interesting to see their query plans.)

Given the complexity of the plans, it may just be that the planner is
switching into GEQO search mode and is failing to find the best plan.
You might consider setting geqo_threshold larger than the number of
base tables in the query (looks like 12, here) to see if better plans
emerge.

It'd also be worth asking whether you couldn't simplify your views ;-).
Do you really require a 12-way join to do whatever it is you're doing?
I'm pretty amazed that the system is able to find a good plan for either
query ...

regards, tom lane