|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|
|Views:||Raw Message | Whole Thread | Download mbox|
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:
>> 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.
|Screen Shot 2017-09-28 at 22.58.42.png||image/png||258.9 KB|
|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|