Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes

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(at)momjian(dot)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(at)momjian(dot)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.