Index-only scan is slower than Index scan.

Started by Konstantin Knizhnikabout 7 years ago2 messages
#1Konstantin Knizhnik
k.knizhnik@postgrespro.ru
1 attachment(s)

Hi hackers,

One of our customers noticed strange thing: time of execution of the
same query is about 25% slower with index only scan, comparing with
indexscan plan
(produced with enable_indexonlyscan = off).
The query is the following:

SELECT
    T1._Period,
    T1._RecorderTRef,
    T1._RecorderRRef,
    T1._LineNo,
    T1._Correspond,
    T1._KindRRef,
    T1._Value_TYPE,
    T1._Value_RTRef,
    T1._Value_RRRef
    FROM _AccRgED165 T1
    WHERE (T1._KindRRef =
'\\217\\246\\000\\011\\017\\252\\000\\001\\021\\350
\\204K\\226\\335\\225'::bytea) AND (T1._Value_TYPE = '\\010'::bytea AND
T1._Value_RTRef = '\\000\\000\\000\\033'::bytea AND T1._Value_RRRef =
'\\217\\246\\000\\011\\017\\252\\000\\001\\021\\350
\\202O\\375/\\317'::bytea) AND ((T1._Period >= '2017-08-01
00:00:00'::timestamp) AND (T1._Period <= '2017-09-01 00:00:00'::timestamp))
    ;

Most of the fetched fields have "bytea" type, so their offsets are not
cached in tuple descriptor.
StoreIndexTuple in nodeIndexonlyscan.c is using index_getattr to
extract  index tuple components.
As far as fields offset can not be cached, we have to scan i-1 preceding
attributes to fetch i-th attribute.
So StoreIndexTuple has quadratic complexity of number of attributes. In
this query there are 9 attributes and it is enough to make
index only scan 25% slower than Index scan, because last one is
extracting all attributes from heap tuple using slot_getsomeattrs()
function.

I have replaced loop extracting  attributes using index_getattr() in
StoreIndexTuple with invocation of index_deform_tuple()
and reimplemented last one in the same way as heap_deform_tuple (extract
all attributes in one path).
My small patch is attached to this mail. After applying it index-only
scan takes almost the same time as index scan:

index scan 1.995
indexonly scan (original) 2.686
indexonly scan (patch) 2.005

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachments:

index_getattr_optimization-v1.patchtext/x-patch; name=index_getattr_optimization-v1.patchDownload
diff --git a/src/backend/access/common/indextuple.c b/src/backend/access/common/indextuple.c
index aa52a96259..b39545cb4b 100644
--- a/src/backend/access/common/indextuple.c
+++ b/src/backend/access/common/indextuple.c
@@ -423,14 +423,71 @@ void
 index_deform_tuple(IndexTuple tup, TupleDesc tupleDescriptor,
 				   Datum *values, bool *isnull)
 {
-	int			i;
+	int			hasnulls = IndexTupleHasNulls(tup);
+	int			natts = tupleDescriptor->natts;	/* number of atts to extract */
+	int			attnum;
+	char	   *tp;				/* ptr to tuple data */
+	int			off;			/* offset in tuple data */
+	bits8	   *bp;				/* ptr to null bitmap in tuple */
+	bool		slow = false;	/* can we use/set attcacheoff? */
 
 	/* Assert to protect callers who allocate fixed-size arrays */
-	Assert(tupleDescriptor->natts <= INDEX_MAX_KEYS);
+	Assert(natts <= INDEX_MAX_KEYS);
+
+	tp = (char *) tup + IndexInfoFindDataOffset(tup->t_info);
+	off = 0;
+	/* XXX "knows" t_bits are just after fixed tuple header! */
+	bp = (bits8 *) ((char *) tup + sizeof(IndexTupleData));
 
-	for (i = 0; i < tupleDescriptor->natts; i++)
+	for (attnum = 0; attnum < natts; attnum++)
 	{
-		values[i] = index_getattr(tup, i + 1, tupleDescriptor, &isnull[i]);
+		Form_pg_attribute thisatt = TupleDescAttr(tupleDescriptor, attnum);
+
+		if (hasnulls && att_isnull(attnum, bp))
+		{
+			values[attnum] = (Datum) 0;
+			isnull[attnum] = true;
+			slow = true;		/* can't use attcacheoff anymore */
+			continue;
+		}
+
+		isnull[attnum] = false;
+
+		if (!slow && thisatt->attcacheoff >= 0)
+			off = thisatt->attcacheoff;
+		else if (thisatt->attlen == -1)
+		{
+			/*
+			 * We can only cache the offset for a varlena attribute if the
+			 * offset is already suitably aligned, so that there would be no
+			 * pad bytes in any case: then the offset will be valid for either
+			 * an aligned or unaligned value.
+			 */
+			if (!slow &&
+				off == att_align_nominal(off, thisatt->attalign))
+				thisatt->attcacheoff = off;
+			else
+			{
+				off = att_align_pointer(off, thisatt->attalign, -1,
+										tp + off);
+				slow = true;
+			}
+		}
+		else
+		{
+			/* not varlena, so safe to use att_align_nominal */
+			off = att_align_nominal(off, thisatt->attalign);
+
+			if (!slow)
+				thisatt->attcacheoff = off;
+		}
+
+		values[attnum] = fetchatt(thisatt, tp + off);
+
+		off = att_addlength_pointer(off, thisatt->attlen, tp + off);
+
+		if (thisatt->attlen <= 0)
+			slow = true;		/* can't use attcacheoff anymore */
 	}
 }
 
diff --git a/src/backend/executor/nodeIndexonlyscan.c b/src/backend/executor/nodeIndexonlyscan.c
index d1201a1807..d6ac5055cf 100644
--- a/src/backend/executor/nodeIndexonlyscan.c
+++ b/src/backend/executor/nodeIndexonlyscan.c
@@ -267,11 +267,6 @@ IndexOnlyNext(IndexOnlyScanState *node)
 static void
 StoreIndexTuple(TupleTableSlot *slot, IndexTuple itup, TupleDesc itupdesc)
 {
-	int			nindexatts = itupdesc->natts;
-	Datum	   *values = slot->tts_values;
-	bool	   *isnull = slot->tts_isnull;
-	int			i;
-
 	/*
 	 * Note: we must use the tupdesc supplied by the AM in index_getattr, not
 	 * the slot's tupdesc, in case the latter has different datatypes (this
@@ -279,11 +274,10 @@ StoreIndexTuple(TupleTableSlot *slot, IndexTuple itup, TupleDesc itupdesc)
 	 * number of columns though, as well as being datatype-compatible which is
 	 * something we can't so easily check.
 	 */
-	Assert(slot->tts_tupleDescriptor->natts == nindexatts);
+	Assert(slot->tts_tupleDescriptor->natts == itupdesc->natts);
 
 	ExecClearTuple(slot);
-	for (i = 0; i < nindexatts; i++)
-		values[i] = index_getattr(itup, i + 1, itupdesc, &isnull[i]);
+	index_deform_tuple(itup, itupdesc, slot->tts_values, slot->tts_isnull);
 	ExecStoreVirtualTuple(slot);
 }
 
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Konstantin Knizhnik (#1)
Re: Index-only scan is slower than Index scan.

Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:

I have replaced loop extracting attributes using index_getattr() in
StoreIndexTuple with invocation of index_deform_tuple()
and reimplemented last one in the same way as heap_deform_tuple (extract
all attributes in one path).

Pushed with minor cosmetic fixes.

regards, tom lane