Обсуждение: Index-only scan is slower than Index scan.

Поиск
Список
Период
Сортировка

Index-only scan is slower than Index scan.

От
Konstantin Knizhnik
Дата:
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 scan1.995
indexonly scan (original)2.686
indexonly scan (patch)2.005


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 
Вложения

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

От
Tom Lane
Дата:
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