Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes - Mailing list pgsql-bugs

From Saul, Jean Paolo
Subject Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes
Date
Msg-id CA+73ANdw+2H9dcqFPEhhkwB_H2BOfX0hMv4YFoxG18ANPjE2Ng@mail.gmail.com
Whole thread Raw
In response to Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes  (Bruce Momjian <bruce@momjian.us>)
Responses Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes  (Bruce Momjian <bruce@momjian.us>)
List pgsql-bugs
Tree view
Hi Bruce,

Thank you for that info. We will likely migrate our boolean indexes (and possibly all our low cardinality indexes) to hash after we move to PG11.

How would I know if the PostgreSQL team will be investigating this possible regression issue?
OR can someone help me how to further identify the root cause in the code?

It would be nice if we can patch this out, or at least find the reasoning for the slowness.
Does btree somehow have a global lock on a leaf node when it's being modified?

Sorry for all the questions.


Cheers,

Paolo


On Fri, 1 Feb 2019 at 03:36, Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Jan 31, 2019 at 04:08:39PM +1300, Saul, Jean Paolo wrote:
> Additional tests replacing BTREE indexes with HASH indexes indicate that hash
> indexes do not suffer from low-cardinality performance regression.
> However I cannot use hash indexes in our systems as they are discouraged.

Hash indexes are crash safe since PG 10 so their use is no longer
discouraged, at least from a project perspective.

--
  Bruce Momjian  <bruce@momjian.us>        https://urldefense.proofpoint.com/v2/url?u=http-3A__momjian.us&d=DwIBAg&c=MPZC0Rv-kl4oeK4li6Jc92hnsiCC4tOZO_GSKscRWJE&r=VrrIZRoLr_yZJ3k_V9FTyEmoRy2AmFzy-R0X02P-nWQ&m=Buc9EXWO9Nu-8r264HE6ITfcN7ZkB4kATgNdoUnhNJI&s=1EWjlPEfMXj_eBPXv8wYRZZ4KgnmyOAfA2l7ukFsBu8&e=
  EnterpriseDB                             https://urldefense.proofpoint.com/v2/url?u=http-3A__enterprisedb.com&d=DwIBAg&c=MPZC0Rv-kl4oeK4li6Jc92hnsiCC4tOZO_GSKscRWJE&r=VrrIZRoLr_yZJ3k_V9FTyEmoRy2AmFzy-R0X02P-nWQ&m=Buc9EXWO9Nu-8r264HE6ITfcN7ZkB4kATgNdoUnhNJI&s=xt6ZYwRpqRSjSWnuKCuOFCeJHP7X2KLV-BN3cB4JLzE&e=

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers
Next
From: Peter Geoghegan
Date:
Subject: Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes