Tom Lane wrote:
> I would suggest, instead, that you work around the problem until 7.0
> comes out. I think you could do this by removing your two-column
> indexes in favor of single-column indexes, or even just switching the
> order of the indexes (in the above test case, no bug is seen if the
> indexes are declared on (f2,f1)). However switching the order would be
> a bit fragile since it'd depend on which fields you compare to constants
> and which ones you use as join keys in your queries. If that doesn't
> work, a brute-force solution is to run your application with environment
> variable PGOPTIONS="-fn" (forbid nestloop joins), which discourages the
> planner from considering nestloop joins at all. The bug will not arise
> if a merge or hash join plan is used.
First off, let me express appreciation for your investigation and
explanation. My humble thanks.
Re workarounds, I have removed all *unnecessary* multi-column indices.
That still leaves me with 48 multi-column indices for primary keys and
uniqueness. I think I must have those to avoid duplicate key problems,
etc.
Agreed, the index column order switching is fragile and will likely bite me
later. So that leaves the "-fn" option. I will experiment with that.
Are there any known consequences of forbidding nestloop joins? Performance
hits? Functionality hits?
Cheers,
Ed Loehr