v5-0001-Add-selectivity-and-nullness-estimates-for-CTID-syst.patch

From 57356662700a7cd080de06fb9676c121f3b8267a Mon Sep 17 00:00:00 2001
From: Edmund Horner <ejrh00@gmail.com>
Date: Fri, 12 Oct 2018 13:36:24 +1300
Subject: [PATCH 1/5] Add selectivity and nullness estimates for CTID system
 variables

Previously, estimates for ItemPointer range quals, such as "ctid <= '(5,7)'",
resorted to the default values of 0.33 for range selectivity, and 0.005 for
nullness, although there was special-case handling for equality quals like
"ctid = (5,7)", which used the appropriate selectivity for distinct items.

This change uses the relation size to estimate the selectivity of a range qual,
and also uses a nullness estimate of 0 for ctid, since it is never NULL.
---
 src/backend/utils/adt/selfuncs.c | 61 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 61 insertions(+)

diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index ffca0fe..f5a1ee0 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -581,6 +581,58 @@ scalarineqsel(PlannerInfo *root, Oid operator, bool isgt, bool iseq,
 
 	if (!HeapTupleIsValid(vardata->statsTuple))
 	{
+		/*
+		 * There are no stats for system columns, but for CTID we can estimate
+		 * based on table size.
+		 */
+		if (vardata->var && IsA(vardata->var, Var) &&
+			((Var *) vardata->var)->varattno == SelfItemPointerAttributeNumber)
+		{
+			ItemPointer itemptr;
+			double		block;
+			double		density;
+
+			/* If the relation's empty, we're going to include all of it. */
+			if (vardata->rel->pages == 0)
+				return 1.0;
+
+			itemptr = (ItemPointer) DatumGetPointer(constval);
+			block = ItemPointerGetBlockNumberNoCheck(itemptr);
+
+			/*
+			 * Determine the average number of tuples per page.  We naively
+			 * assume there will never be any dead tuples or empty space at
+			 * the start or in the middle of the page.  This is likely fine
+			 * for the purposes here.
+			 */
+			density = vardata->rel->tuples / vardata->rel->pages;
+			if (density > 0.0)
+			{
+				OffsetNumber offset = ItemPointerGetOffsetNumberNoCheck(itemptr);
+
+				block += Min(offset / density, 1.0);
+			}
+
+			selec = block / (double) vardata->rel->pages;
+
+			/*
+			 * We'll have one less tuple for "<" and one additional tuple for
+			 * ">=", the latter of which we'll reverse the selectivity for
+			 * below, so we can simply subtract a tuple here.  We can easily
+			 * detect these two cases by iseq being equal to isgt.  They'll
+			 * either both be true or both be false.
+			 */
+			if (iseq == isgt && vardata->rel->tuples >= 1.0)
+				selec -= (1 / vardata->rel->tuples);
+
+			/* Finally, reverse the selectivity for the ">", ">=" case. */
+			if (isgt)
+				selec = 1.0 - selec;
+
+			CLAMP_PROBABILITY(selec);
+			return selec;
+		}
+
 		/* no stats available, so default result */
 		return DEFAULT_INEQ_SEL;
 	}
@@ -1795,6 +1847,15 @@ nulltestsel(PlannerInfo *root, NullTestType nulltesttype, Node *arg,
 				return (Selectivity) 0; /* keep compiler quiet */
 		}
 	}
+	else if (vardata.var && IsA(vardata.var, Var) &&
+			 ((Var *) vardata.var)->varattno == SelfItemPointerAttributeNumber)
+	{
+		/*
+		 * There are no stats for system columns, but we know CTID is never
+		 * NULL.
+		 */
+		selec = (nulltesttype == IS_NULL) ? 0.0 : 1.0;
+	}
 	else
 	{
 		/*
-- 
2.7.4