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: 2018-12-21 20:28:53
Message-ID: CAKJS1f8QWFUnhqJfT4=mSY_U_rhJmkcg=XkuYEvNbY9JS4sNxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Going by my profiler this drops match_eclasses_to_foreign_key_col()
down to just 10% of total planner time for this query. The new
bms_is_member() call is pretty hot inside that function though.

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

Attachment Content-Type Size
speed_up_matching_fkeys_to_eclasses.patch application/octet-stream 658 bytes

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2018-12-21 20:32:17 Re: Compiling on Termux
Previous Message Michael Banck 2018-12-21 20:16:16 Offline enabling/disabling of data checksums