Re: Surjective functional indexes

From: Oleg Bartunov <obartunov(at)gmail(dot)com>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Surjective functional indexes
Date: 2017-09-28 20:25:16
Message-ID: CAF4Au4wXP0ShV0uY+t6stM_fPYHuwXgi5LnxzAHAPHnw55ObBA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Thu, Sep 28, 2017 at 11:24 PM, Oleg Bartunov <obartunov(at)gmail(dot)com> wrote:
> On Thu, May 25, 2017 at 7:30 PM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)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.

sorry, now with picture attached.

Attachment Content-Type Size
Screen Shot 2017-09-28 at 22.58.42.png image/png 258.9 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Konstantin Knizhnik 2017-09-28 20:37:40 Re: Surjective functional indexes
Previous Message Oleg Bartunov 2017-09-28 20:24:11 Re: Surjective functional indexes