Re: BUG #16966: Nested loop joining across tables with varchar -> bpchar cast always scans varchar table first

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> I'm seeing a performance issue when joining across two tables on columns
> that require a cast from varchar to bpchar. No matter how selective the
> condition is on the bpchar table, the outer scan will be on the table with
> the varchar column.

I don't think this is a planner problem. It can't generate the plan
you are hoping for because of index mismatch. The query's join
condition is effectively "a.varcharcol::bpchar = b.bpcharcol", and
the construct "a.varcharcol::bpchar" doesn't match your index on
a.varcharcol, so it can't use an inner indexscan on that side of
the equality.

Possibly you could work around this by providing an expression index on
"a.varcharcol::bpchar". But TBH my recommendation would be to nuke the
bpchar columns from orbit. They're almost never the semantics you want,
especially not if you're sometimes comparing them to non-bpchar
columns.

regards, tom lane