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

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-02-05 09:43:54
Message-ID: CAKJS1f8JqgRC=fTKpJJxYC4YjunfmsE=Mo_f5BDnfwNA_avsYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sat, 22 Dec 2018 at 09:28, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> On Fri, 21 Dec 2018 at 06:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I was distressed to discover via perf that 69% of the runtime of this
> > test now goes into match_eclasses_to_foreign_key_col(). That seems
> > clearly unacceptable.
>
> Agreed. That's pretty terrible.
>
> I looked at this a bit and see that
> match_eclasses_to_foreign_key_col() is missing any smarts to skip
> equivalence classes that don't have ec_relids bits for both rels. With
> that added the run-time is reduced pretty dramatically.
>
> I've only tested with a debug build as of now, but I get:
>
> Unpatched:
>
> $ pgbench -n -T 60 -f query.sql postgres
> latency average = 18411.604 ms
>
> Patched:
> latency average = 8748.177 ms

So that this does not get lost, I've added an entry for the original
patch for the March commitfest.

While the patch does not bring the performance back to what it was
before this code was added, it makes a massive dent in the additional
overhead.

https://commitfest.postgresql.org/22/1984/

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2019-02-05 09:47:24 Re: Performance issue in foreign-key-aware join estimation
Previous Message Antonin Houska 2019-02-05 09:40:15 Re: [HACKERS] WIP: Aggregation push-down