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

On Sun, 16 Jun 2019 at 19:42, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> I've rebased this on top of the current master. d25ea0127 conflicted
> with the old version.

... and again, per recent conflicting change in equivclass.c

I've also taken a fresh set of performance benchmarks since 1cff1b95a
has recently changed the list.c implementation to use arrays instead
of singly-linked-lists.

I've attached 2 patched:

0001: Is the rebased version of eclass_indexes_v7.patch.
0002: Is new and goes even further to improve performance.

Using schema.sql and query.sql from
https://postgr.es/m/6970.1545327857%40sss.pgh.pa.us I get:

master @ 21039555

postgres=# \i query.sql
Time: 5078.105 ms (00:05.078)
Time: 5279.733 ms (00:05.280)
Time: 5375.766 ms (00:05.376)
Time: 5382.716 ms (00:05.383)

master + 0001:

postgres=# \i query.sql
Time: 2116.394 ms (00:02.116)
Time: 2076.883 ms (00:02.077)
Time: 2142.237 ms (00:02.142)
Time: 2199.468 ms (00:02.199)

(2.47x faster than master)

Per what Tom mentioned in
https://postgr.es/m/16252.1553202606@sss.pgh.pa.us about
generate_join_implied_equalities[_for_ecs]. Since
generate_join_implied_equalities() is still quite an overhead in
profiles, it seems to make sense to special purpose this function
rather than have it call generate_join_implied_equalities_for_ecs()
and pass the root->eq_classes list. Passing the list means we can't
use the new ec_indexes Bitmapset, so can get no benefit of the
improved EC lookup method.

Since generate_join_implied_equalities_for_ecs() is fairly short, I
don't think it's all that bad to keep another slightly altered copy of
it. Especially given the following performance results from doing so:

master + 0001 + 0002:

postgres=# \i query.sql
Time: 1308.742 ms (00:01.309)
Time: 1294.766 ms (00:01.295)
Time: 1293.113 ms (00:01.293)
Time: 1300.643 ms (00:01.301)

(4.06x faster than master)

Unless there's some objection, I'll be looking into pushing both 0001
and 0002 in a single commit in the next few days.

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