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:
info->>'name'.
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: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers