Horton

Thread

Commits

  1. Improve performance of index-only scans with many index columns.

  1. Index-only scan is slower than Index scan.

    Konstantin Knizhnik <k.knizhnik@postgrespro.ru> — 2018-11-23T14:51:37Z

    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

  2. Re: Index-only scan is slower than Index scan.

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-03T21:58:17Z

    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