From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Subject: | Performance issue in foreign-key-aware join estimation |
Date: | 2018-12-20 17:44:17 |
Message-ID: | 6970.1545327857@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
In connection with David Rowley's proposal to change bitmapset.c to use
64-bit words, I dug out an old test case I had for a complex-to-plan query
(attached). Andres Freund posted this to the lists perhaps ten years ago,
though I can't locate the original posting right now.
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. There aren't an unreasonable number of foreign key
constraints in the underlying schema --- mostly one per table, and there's
only one table with as many as 3. However, poking around in the planner
data structures, it turns out there are:
888 base relations
1005 EquivalenceClasses
167815 fkey_list entries initially
690 fkey_list entries after match_foreign_keys_to_quals trims them
So the reason match_eclasses_to_foreign_key_col is so dominant in the
runtime is it's invoked 167815 times and has to scan a thousand
EquivalenceClasses (unsuccessfully) on most of those calls.
How did the fkey_list get that big? I think the issue is that the
query touches the same tables many many times (888 baserels, but
there are only 20 distinct tables in the schema) and we get an
O(N^2) growth in the apparent number of FKs.
Clearly, we ought to rethink that data structure. I'm not sure
offhand how to make it better, but this is pretty awful.
Perhaps there'd also be some use in having better indexing for
the EquivalenceClass list, but again I'm not sure what that'd
look like.
regards, tom lane
Attachment | Content-Type | Size |
---|---|---|
schema.sql | text/plain | 61.5 KB |
query.sql | text/plain | 2.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2018-12-20 18:13:09 | Re: lock level for DETACH PARTITION looks sketchy |
Previous Message | Robert Haas | 2018-12-20 17:28:31 | Re: lock level for DETACH PARTITION looks sketchy |