Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?

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