Re: Performance issue in foreign-key-aware join estimation

On Wed, 26 Dec 2018 at 09:50, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> Yeah, good questions. I think the simplest thing we could do is building
> them on the first access - that would at least ensure we don't build the
> index without accessing it at least once.

I think we first need to focus on what is back-patchable here. The
problem I see with the equivalence class index idea is that it would
require passing the index down into
match_eclasses_to_foreign_key_col() which is not a static function, so
we can't really go changing its signature on a backbranch.

Another idea would be to create a new version of
match_eclasses_to_foreign_key_col() which uses the index, which would
mean we'd not break any extensions that might happen to use
match_eclasses_to_foreign_key_col().

Ideally, the quick fix in the v1 patch would be good enough for the
backbranches, but a quick bit of benchmarking shows that there's still
a big regression to what the performance is like without the foreign
keys.

(Average of EXPLAIN over 60 seconds)

foreign key qual matching code commented out: 2486.204 ms
Master: 13909.551 ms
v1 patch: 7310.719 ms

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services