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

Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com> writes:
> 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.

Yes, if you are using UNIQUE indexes to enforce uniqueness of primary
keys, then you don't have a lot of choice but to leave them in place.
So, if you still see the problem with only those multicolumn indexes
remaining, then PGOPTIONS="-fn" is your best recourse.

> Are there any known consequences of forbidding nestloop joins? Performance
> hits? Functionality hits?

You probably will see a performance hit, since the optimizer wouldn't be
picking the nestloop in the first place if it didn't think it was the
cheapest alternative. (Of course, whether its estimate is *right* is
another story...) Hopefully it won't be a large hit. The worst aspect
of using PGOPTIONS for this is that it'll affect all your queries not
just the ones where this trouble occurs; so on some simpler queries you
might see a noticeable slowdown. You will definitely want to remember
to take out the workaround once you are off 6.5.

A more significant potential problem is that the optimizer will use
nestloop anyway if it can't find a usable merge or hash join method.
I think that that won't be a problem for the datatypes you are using.

regards, tom lane