Re: Surjective functional indexes

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

sorry, now with picture attached.