From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Subject: | Re: Performance issue in foreign-key-aware join estimation |
Date: | 2019-07-18 07:24:05 |
Message-ID: | CAKJS1f81F7ZFoX5o-p8HR2x_uDPdjKyX5MN_fHim5jddoWiXtw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
0001-Speed-up-finding-EquivalenceClasses-for-a-given-set-.patch | application/octet-stream | 21.3 KB |
0002-Special-purpose-generate_join_implied_equalities-imp.patch | application/octet-stream | 2.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2019-07-18 07:50:57 | Re: partition routing layering in nodeModifyTable.c |
Previous Message | Жарков Роман | 2019-07-18 07:04:34 | Re: Intermittent pg_ctl failures on Windows |