The crux seems to be that test_b does not have an even distribution for a_id values: it only has records for two of the values in the referenced table. This is how our real dataset is too and isn’t something we can really change.
How does it perform with extended statistics created for these two columns?
I forgot to mention that I had already tried that actually, and it sadly made no difference. I tried both dependencies and ndistinct modes and the estimates remained the same. Having read through the docs I think that’s to be expected since dependencies is designed to help when you have multiple predicates and ndistinct when you’re doing grouping. Neither of which is the case with my query.
For good measure I just tried mcv mode on PG12 with the test script I posted and that also made no difference.
