Thread: Index-only scan is slower than Index scan.
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:
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
Attachment
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