Index-only scan is slower than Index scan.

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Index-only scan is slower than Index scan.
Date: 2018-11-23 14:51:37
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:

    FROM _AccRgED165 T1
    WHERE (T1._KindRRef =
\\204K\\226\\335\\225'::bytea) AND (T1._Value_TYPE = '\\010'::bytea AND
T1._Value_RTRef = '\\000\\000\\000\\033'::bytea AND T1._Value_RRRef =
\\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()

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:
The Russian Postgres Company

Attachment Content-Type Size
index_getattr_optimization-v1.patch text/x-patch 3.6 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira 2018-11-23 15:17:12 Re: row filtering for logical replication
Previous Message Tom Lane 2018-11-23 14:45:07 Re: Support custom socket directory in pg_upgrade