v6-0001-Add-selectivity-estimate-for-CTID-system-variables.patch

From 23d684ad935d913276072c37180ad83f58447cee 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/4] Add selectivity estimate 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, 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.
---
 src/backend/utils/adt/selfuncs.c | 52 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 52 insertions(+)

diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index fb00504..9bb224d 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -583,6 +583,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;
 	}
-- 
2.7.4