Обсуждение: BUG #16966: Nested loop joining across tables with varchar -> bpchar cast always scans varchar table first

The following bug has been logged on the website:

Bug reference:      16966
Logged by:          Zeb Burke-Conte
Email address:      zebburkeconte@gmail.com
PostgreSQL version: 13.2
Operating system:   Ubuntu 20.04 LTS
Description:

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. It's possible that the issue case is more specific than
that but you can see for yourselves with the example below (which is pretty
minimal). It ends with two queries that should be planned nearly
identically, but aren't; as a result, one is 100x slower.

Note: This is not an issue of inaccurate statistics/selectivity estimates.
The estimates are spot on, but the more selective condition is not being
used as the outer scan.

Example SQL:

drop table if exists public.a;
drop table if exists public.b;

create table public.b (
    id bpchar(16) not null,
    constraint b_pk primary key (id)
);

create table public.a (
    id varchar not null,
    constraint a_pk primary key (id)
);

insert into a
    (id)
    select (ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n +
10000*ten_thousands.n + 100000*hundred_thousands.n)::varchar
    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ten_thousands(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundred_thousands(n)
    order by 1;

insert into b
    (id)
    select (ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n +
10000*ten_thousands.n + 100000*hundred_thousands.n)::varchar
    from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ten_thousands(n),
         (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundred_thousands(n)
    order by 1;

analyze a;
analyze b;

set enable_hashjoin = off;
set enable_mergejoin = off;
set enable_seqscan = off;

explain analyze select * from a join b on a.id = b.id where a.id in
('109244', '721345', '8911');
explain analyze select * from a join b on a.id = b.id where b.id in
('109244', '721345', '8911');

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

Zeb Burke-Conte <zebburkeconte@gmail.com> writes:
> 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?

Don't think so.  It's a bit of an implementation artifact arising from
the fact that varchar has no operators of its own (it's kind of like a
domain over text, in our implementation).  So the parser, faced with
"vc = bp", has to choose whether to use the "text = text" or
"bpchar = bpchar" operator.  It settles on the latter, which I believe
is because it has one more exact match to the actual input types than
"text = text".  (Cf. the resolution rules in [1], whcih are most certainly
not derived from the SQL standard.)  Interestingly, if you'd been
working with "text = bp", then "text = text" would've been picked and
the inefficiency would be on the other side of the join due to needing
to cast the bpchar input.

It may be possible to argue that the SQL spec has a preference here,
but I'm not sure.  (IIRC, their text talks about PAD SPACE attributes
rather than a distinct type, so mapping it onto our API takes some
head-scratching.)  In any case, PG has had these resolution rules for
twenty years plus, so changing them would be a huge compatibility break.

> Changing this could be a "nice-to-have" although I see how it's not a bug
> per se.

Even if we wanted to change the resolution rules, that would just move
the pain somewhere else, as I indicated above.

It's possible that things could be improved by inventing operators
for "varchar = bpchar", "bpchar = varchar", etc and adding them to
all the right index opclasses.  It would be a large amount of work
though and could easily introduce some unexpected side-effects.
Given that we regard bpchar as pretty much of a third-class citizen,
I'm not surprised that nobody has put effort into that.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/typeconv-oper.html