On Thu, May 25, 2017 at 7:30 PM, Konstantin Knizhnik <k.knizh...@postgrespro.ru> wrote: > 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.
I confirm that the patch helps for workload A of YCSB, but actually just extends #clients, where postgres outperforms mongodb (see attached picture). If we increase #clients > 100 postgres quickly degrades not only for workload A, but even for workload B (5% updates), while mongodb and mysql behave much-much better, but this is another problem, we will discuss in different thread. > > 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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers