Quick Links

• Community
• Contributors
• Mailing Lists
• IRC
• Local User Groups
• Events
• International Sites

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