Thank you so much, Tom--this is a great point. I've tested your index expression suggestion, and you have correctly diagnosed the problem. Even more simply, casting the bpchar to varchar in the JOIN ... ON condition works.
I still find this quite counterintuitive, since nothing about my query is forcing Postgres to cast the varchar column to bpchar instead of the other way around. Is there some arcane standard that requires it? More likely, I'm guessing, casting is determined upfront and indices are not considered. Changing this could be a "nice-to-have" although I see how it's not a bug per se.
Of course, in my actual use case, I've switched away from bpchar.
Best,
Zeb Burke-Conte
PG Bug reporting form <noreply@postgresql.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