Re: [SQL] Good Optimization

secret <secret(at)kearneydev(dot)com> writes:
> Tom Lane wrote:
>> How much *actual* speedup is there? I don't trust the optimizer's
>> numbers as anything more than relative measures ;-)
>>
>> I'm a bit surprised that you are getting a nested-loop plan and not
>> a merge or hash join. With a merge join, at least, there ought not be
>> a large difference from providing the additional qual clause (I think).
>> What Postgres version are you using?

> The actual performance difference is HUGE. Hours vs minutes or Minutes vs
> Seconds...

Well, yeah, it could be huge in a nested-loop scenario; in a mergejoin
I think it would make little difference.

Actually, if the inner path is indexed then this shouldn't make any
difference for a nestloop either; each probe into the inner path *ought*
to be using the value of the current outer tuple's join variable as an
indexqual constraint, which would have the same limiting effect as the
explicit restriction you propose adding. There is code in the optimizer
that claims to be making that happen. Sounds like it is broken :-(

What are the data types of po_id and material_po in your example?
And, again, which Postgres version exactly?

regards, tom lane