Surjective functional indexes

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Surjective functional indexes
Date: 2017-05-25 16:30:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Right now Postgres determines whether update operation touch index or
not based only on set of the affected columns.
But in case of functional indexes such policy quite frequently leads to
unnecessary index updates.
For example, functional index are widely use for indexing JSON data:

JSON data may contain multiple attributes and only few of them may be
affected by update.
Moreover, index is used to build for immutable attributes (like "id",
"isbn", "name",...).

Functions like (info->>'name') are named "surjective" ni mathematics.
I have strong feeling that most of functional indexes are based on
surjective functions.
For such indexes current Postgresql index update policy is very
inefficient. It cause disabling of hot updates
and so leads to significant degrade of performance.

Without this patch Postgres is slower than Mongo on YCSB benchmark with
(50% update,50 % select) workload.
And after applying this patch Postgres beats Mongo at all workloads.

My proposal is to check value of function for functional indexes instead
of just comparing set of effected attributes.
Obviously, for some complex functions it may have negative effect on
update speed.
This is why I have added "surjective" option to index. By default it is
switched on for all functional indexes (based on my assumption
that most functions used in functional indexes are surjective). But it
is possible to explicitly disable it and make decision weather index
needs to be updated or not only based on set of effected attributes.

Konstantin Knizhnik
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
surjective-index.patch text/x-patch 13.9 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-05-25 16:37:40 Re: Surjective functional indexes
Previous Message Tom Lane 2017-05-25 16:28:34 Re: CREATE STATISTICS statistic_type documentation