Re: Optimize join selectivity estimation by not reading MCV stats for unique join attributes - Mailing list pgsql-hackers

I wrote:
> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
>> Or perhaps what if we have a function that quickly determines if the
>> attribute has MCV, without loading it? I'd bet the expensive part of
>> get_attstatslot() is the deconstruct_array().
>> We could have a function that only does the first small loop over slots,
>> and returns true/false if we have a slot of the requested stakind.

> Yeah, I like this idea.

Actually, looking at get_attstatslot, I realize it was already designed
to do that -- just pass zero for flags.  So we could do it as attached.

We could make some consequent simplifications by only retaining one
"have_mcvs" flag, but I'm inclined to leave the rest of the code as-is.
We would not get much gain from that, and it would make this harder
to undo if there ever is a reason to consider just one set of MCVs.

            regards, tom lane

diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index d597b7e81f..e0aeaa6909 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -2261,6 +2261,7 @@ eqjoinsel(PG_FUNCTION_ARGS)
     Form_pg_statistic stats2 = NULL;
     bool        have_mcvs1 = false;
     bool        have_mcvs2 = false;
+    bool        get_mcv_stats;
     bool        join_is_reversed;
     RelOptInfo *inner_rel;

@@ -2275,11 +2276,25 @@ eqjoinsel(PG_FUNCTION_ARGS)
     memset(&sslot1, 0, sizeof(sslot1));
     memset(&sslot2, 0, sizeof(sslot2));

+    /*
+     * There is no use in fetching one side's MCVs if we lack MCVs for the
+     * other side, so do a quick check to verify that both stats exist.
+     */
+    get_mcv_stats = (HeapTupleIsValid(vardata1.statsTuple) &&
+                     HeapTupleIsValid(vardata2.statsTuple) &&
+                     get_attstatsslot(&sslot1, vardata1.statsTuple,
+                                      STATISTIC_KIND_MCV, InvalidOid,
+                                      0) &&
+                     get_attstatsslot(&sslot2, vardata2.statsTuple,
+                                      STATISTIC_KIND_MCV, InvalidOid,
+                                      0));
+
     if (HeapTupleIsValid(vardata1.statsTuple))
     {
         /* note we allow use of nullfrac regardless of security check */
         stats1 = (Form_pg_statistic) GETSTRUCT(vardata1.statsTuple);
-        if (statistic_proc_security_check(&vardata1, opfuncoid))
+        if (get_mcv_stats &&
+            statistic_proc_security_check(&vardata1, opfuncoid))
             have_mcvs1 = get_attstatsslot(&sslot1, vardata1.statsTuple,
                                           STATISTIC_KIND_MCV, InvalidOid,
                                           ATTSTATSSLOT_VALUES | ATTSTATSSLOT_NUMBERS);
@@ -2289,7 +2304,8 @@ eqjoinsel(PG_FUNCTION_ARGS)
     {
         /* note we allow use of nullfrac regardless of security check */
         stats2 = (Form_pg_statistic) GETSTRUCT(vardata2.statsTuple);
-        if (statistic_proc_security_check(&vardata2, opfuncoid))
+        if (get_mcv_stats &&
+            statistic_proc_security_check(&vardata2, opfuncoid))
             have_mcvs2 = get_attstatsslot(&sslot2, vardata2.statsTuple,
                                           STATISTIC_KIND_MCV, InvalidOid,
                                           ATTSTATSSLOT_VALUES | ATTSTATSSLOT_NUMBERS);