Optimize join selectivity estimation by not reading MCV stats for unique join attributes

From: David Geier <geidav(dot)pg(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Optimize join selectivity estimation by not reading MCV stats for unique join attributes
Date: 2022-11-11 12:01:15
Message-ID: b9846ca0-5f1c-9b26-5881-aad3f42b07f0@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

eqjoinsel() can be optimized by not reading MCV stats if at least one of
the two join attributes is unique. As primary keys are implicitly unique
this situation can occur frequently. For unique columns no MCV stats are
stored and eqjoinsel_inner() and eqjoinsel_semi(), called from
eqjoinsel(), only consider MCV stats in the join selectivity computation
if they're present on both columns. Attached is a small patch that
implements the skipping.

With this change we saw some queries improve planning time by more than
2x, especially with larger values for default_statistics_target. That's
because get_attstatsslot() deconstructs the array holding the MCV. The
size of that array depends on default_statistics_target.

Thanks for your consideration!

--
David Geier
(ServiceNow)

Attachment Content-Type Size
0001-Skip-reading-MCV-stats-for-unique-join-attributes.patch text/x-patch 2.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Maxim Orlov 2022-11-11 12:21:58 Re: Add LSN along with offset to error messages reported for WAL file read/write/validate header failures
Previous Message Pavel Borisov 2022-11-11 11:39:05 Re: Lockless queue of waiters in LWLock