I was wondering why it seems that the query planner can't "see", based
on the histograms, that two join-columns have a very small
intersection, and adjust its row estimation accordingly. Clearly the
below query returns 1001 rows. It appears as if much or all of the
necessary machinery exists in mergejoinscansel, and indeed if you
inspect
leftstartsel, leftendsel, rightstartsel, rightendsel during execution
they are respectively 0.98, 1.00, 0.00, 0.020, which I believe makes sense.
Am I missing something obvious?
Thanks
Sam
create table table_a as select * from generate_series(1,61000) as pkey;
create table table_b as select * from generate_series(60000,110000) as pkey;
create unique index idx_a on table_a(pkey);
create unique index idx_b on table_b(pkey);
analyse table_a;
analyse table_b;
explain select * from table_a a inner join table_b b on a.pkey = b.pkey;
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Join (cost=1984.88..2550.42 rows=50001 width=8)
Merge Cond: (a.pkey = b.pkey)
-> Index Only Scan using idx_a on table_a a (cost=0.00..1864.32
rows=61000 width=4)
-> Index Only Scan using idx_b on table_b b (cost=0.00..1531.32
rows=50001 width=4)
