MIME-Version: 1.0
References: <CAJCZkoKi5RqJ0Y+_D_D_yhdLXcCiiCG4uBdui1Mgd0wC=EM4oA@mail.gmail.com>
 <CAPnRvGu_Zdf7rq+5EjRdcGZHwd711g=Cg2EpUZk6QwqKWKxzHw@mail.gmail.com>
In-Reply-To: <CAPnRvGu_Zdf7rq+5EjRdcGZHwd711g=Cg2EpUZk6QwqKWKxzHw@mail.gmail.com>
From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Date: Thu, 12 Sep 2024 22:33:10 +0530
Message-ID: <CAJCZkoLK0i2FDyZgvJiJVJJkhs_Ao4tK2RmEGRTgczaPNZKvcg@mail.gmail.com>
Subject: Re: Recommendations on improving the insert on conflict do nothing performance
To: Muhammad Usman Khan <usman.k@bitnine.net>
Cc: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b745170621ef0b9d"
Archived-At: <https://www.postgresql.org/message-id/CAJCZkoLK0i2FDyZgvJiJVJJkhs_Ao4tK2RmEGRTgczaPNZKvcg%40mail.gmail.com>
Precedence: bulk

--000000000000b745170621ef0b9d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Muhammad Usman Khan

I have already set required values of params.Here issue was about
triggers.I have resolved this issue

Regards
Durga Mahesh

On Thu, Sep 12, 2024 at 10:05=E2=80=AFAM Muhammad Usman Khan <usman.k@bitni=
ne.net>
wrote:

> Hi,
> You can use the following approaches for optimization:
>
>    - Instead of inserting one row at a time, perform bulk inserts, which
>    will reduce the overhead of each individual transaction
>    - Partitioning can improve write performance by splitting the data
>    into smaller, more manageable chunks
>    - Tune postgres configuration like
>    work_mem =3D '16MB'
>    shared_buffers =3D '8GB'
>    effective_cache_size =3D '24GB'
>
>
> On Wed, 11 Sept 2024 at 13:50, Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>> Hi
>>     insert into
>> dictionary(lang,tid,sportid,brandid,translatedtext,objecttype,basetid)
>> values ($1,$2,$3,$4,$5,$6,$7) on conflict do nothing
>>  *8vcpus and 32gb ram
>>    Number of calls per sec 1600 at this time 42% of cpu utilized
>>                  Max in ms 33.62 per call
>>                  Avg in ms  0.17 per call
>>                                                    Table
>> "dictionary.dictionary"
>>      Column     |           Type           | Collation | Nullable |
>> Default  | Storage  | Compression | Stats target | Description
>>
>> ----------------+--------------------------+-----------+----------+-----=
-----+----------+-------------+--------------+-------------
>>  lang           | text                     |           | not null |
>>    | extended |             |              |
>>  tid            | text                     |           | not null |
>>    | extended |             |              |
>>  basetid        | text                     |           | not null |
>>    | extended |             |              |
>>  sportid        | text                     |           |          |
>>    | extended |             |              |
>>  brandid        | text                     |           | not null |
>>    | extended |             |              |
>>  translatedtext | text                     |           |          |
>>    | extended |             |              |
>>  objecttype     | text                     |           |          |
>>    | extended |             |              |
>>  createdat      | timestamp with time zone |           | not null | now(=
)
>>    | plain    |             |              |
>>  modified       | timestamp with time zone |           | not null | now(=
)
>>    | plain    |             |              |
>>  modifiedby     | text                     |           | not null |
>> ''::text | extended |             |              |
>>  version        | integer                  |           | not null | 0
>>    | plain    |             |              |
>> Indexes:
>>     "pk_dictionary" PRIMARY KEY, btree (lang, tid)
>>     "idx_dictionary_basetid" btree (basetid)
>>     "idx_dictionary_brandid" btree (brandid)
>>     "idx_dictionary_objecttype" btree (objecttype)
>>     "idx_dictionary_sportid" btree (sportid)
>> Triggers:
>>     i_dictionary_createdat BEFORE INSERT ON dictionary FOR EACH ROW
>> EXECUTE FUNCTION update_createdat_col()
>>     i_dictionary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH
>> ROW EXECUTE FUNCTION update_modified_col()
>> Access method: heap
>> How do we improve this query performance without taking more cpu?
>>
>> Regards,
>> Durga Mahesh
>>
>

--000000000000b745170621ef0b9d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9Imx0ciI+SGnCoE11aGFtbWFkIFVzbWFuIEtoYW48ZGl2Pjxicj48L2Rpdj48ZGl2
PkkgaGF2ZSBhbHJlYWR5IHNldCByZXF1aXJlZCB2YWx1ZXMgb2YgcGFyYW1zLkhlcmUgaXNzdWUg
d2FzIGFib3V0IHRyaWdnZXJzLkkgaGF2ZSByZXNvbHZlZMKgdGhpcyBpc3N1ZcKgPC9kaXY+PGRp
dj48YnI+PC9kaXY+PGRpdj5SZWdhcmRzPC9kaXY+PGRpdj5EdXJnYSBNYWhlc2g8L2Rpdj48L2Rp
dj48YnI+PGRpdiBjbGFzcz0iZ21haWxfcXVvdGUiPjxkaXYgZGlyPSJsdHIiIGNsYXNzPSJnbWFp
bF9hdHRyIj5PbiBUaHUsIFNlcCAxMiwgMjAyNCBhdCAxMDowNeKAr0FNIE11aGFtbWFkIFVzbWFu
IEtoYW4gJmx0OzxhIGhyZWY9Im1haWx0bzp1c21hbi5rQGJpdG5pbmUubmV0Ij51c21hbi5rQGJp
dG5pbmUubmV0PC9hPiZndDsgd3JvdGU6PGJyPjwvZGl2PjxibG9ja3F1b3RlIGNsYXNzPSJnbWFp
bF9xdW90ZSIgc3R5bGU9Im1hcmdpbjowcHggMHB4IDBweCAwLjhleDtib3JkZXItbGVmdDoxcHgg
c29saWQgcmdiKDIwNCwyMDQsMjA0KTtwYWRkaW5nLWxlZnQ6MWV4Ij48ZGl2IGRpcj0ibHRyIj5I
aSw8YnI+WW91IGNhbiB1c2UgdGhlIGZvbGxvd2luZyBhcHByb2FjaGVzIGZvciBvcHRpbWl6YXRp
b246PGRpdj48dWw+PGxpPkluc3RlYWQgb2YgaW5zZXJ0aW5nIG9uZSByb3cgYXQgYSB0aW1lLCBw
ZXJmb3JtIGJ1bGsgaW5zZXJ0cywgd2hpY2ggd2lsbCByZWR1Y2UgdGhlIG92ZXJoZWFkIG9mIGVh
Y2ggaW5kaXZpZHVhbCB0cmFuc2FjdGlvbjwvbGk+PGxpPg0KDQpQYXJ0aXRpb25pbmcgY2FuIGlt
cHJvdmUgd3JpdGUgcGVyZm9ybWFuY2UgYnkgc3BsaXR0aW5nIHRoZSBkYXRhIGludG8gc21hbGxl
ciwgbW9yZSBtYW5hZ2VhYmxlIGNodW5rcw0KDQrCoMKgPGJyPjwvbGk+PGxpPlR1bmUgcG9zdGdy
ZXMgY29uZmlndXJhdGlvbsKgbGlrZTxicj53b3JrX21lbSA9IDxzcGFuPiYjMzk7MTZNQiYjMzk7
PC9zcGFuPsKgPGJyPnNoYXJlZF9idWZmZXJzID0gPHNwYW4+JiMzOTs4R0ImIzM5Ozwvc3Bhbj4N
Cjxicj5lZmZlY3RpdmVfY2FjaGVfc2l6ZSA9IDxzcGFuPiYjMzk7MjRHQiYjMzk7PC9zcGFuPjwv
bGk+PC91bD48L2Rpdj48L2Rpdj48YnI+PGRpdiBjbGFzcz0iZ21haWxfcXVvdGUiPjxkaXYgZGly
PSJsdHIiIGNsYXNzPSJnbWFpbF9hdHRyIj5PbiBXZWQsIDExIFNlcHQgMjAyNCBhdCAxMzo1MCwg
RHVyZ2FtYWhlc2ggTWFubmUgJmx0OzxhIGhyZWY9Im1haWx0bzptYWhlc2hwb3N0Z3JlczlAZ21h
aWwuY29tIiB0YXJnZXQ9Il9ibGFuayI+bWFoZXNocG9zdGdyZXM5QGdtYWlsLmNvbTwvYT4mZ3Q7
IHdyb3RlOjxicj48L2Rpdj48YmxvY2txdW90ZSBjbGFzcz0iZ21haWxfcXVvdGUiIHN0eWxlPSJt
YXJnaW46MHB4IDBweCAwcHggMC44ZXg7Ym9yZGVyLWxlZnQ6MXB4IHNvbGlkIHJnYigyMDQsMjA0
LDIwNCk7cGFkZGluZy1sZWZ0OjFleCI+PGRpdiBkaXI9Imx0ciI+SGnCoDxkaXY+wqAgwqAgaW5z
ZXJ0IGludG8gZGljdGlvbmFyeShsYW5nLHRpZCxzcG9ydGlkLGJyYW5kaWQsdHJhbnNsYXRlZHRl
eHQsb2JqZWN0dHlwZSxiYXNldGlkKSB2YWx1ZXMgKCQxLCQyLCQzLCQ0LCQ1LCQ2LCQ3KSBvbiBj
b25mbGljdCBkbyBub3RoaW5nwqDCoDwvZGl2PjxkaXY+wqAqOHZjcHVzIGFuZCAzMmdiIHJhbcKg
PC9kaXY+PGRpdj7CoCDCoE51bWJlciBvZiBjYWxscyBwZXIgc2VjIDE2MDAgYXQgdGhpcyB0aW1l
IDQyJSBvZiBjcHUgdXRpbGl6ZWTCoDxicj48L2Rpdj48ZGl2PsKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgTWF4IGluIG1zIDMzLjYyIHBlciBjYWxswqA8L2Rpdj48ZGl2PsKgIMKgIMKgIMKgIMKg
IMKgIMKgIMKgIMKgQXZnIGluIG1zwqAgMC4xNyBwZXIgY2FsbMKgPC9kaXY+PGRpdj7CoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoFRhYmxlICZxdW90O2RpY3Rpb25hcnkuZGljdGlvbmFyeSZxdW90Ozxicj7C
oCDCoCDCoENvbHVtbiDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIFR5cGUgwqAgwqAgwqAgwqAgwqAg
fCBDb2xsYXRpb24gfCBOdWxsYWJsZSB8IERlZmF1bHQgwqB8IFN0b3JhZ2UgwqB8IENvbXByZXNz
aW9uIHwgU3RhdHMgdGFyZ2V0IHwgRGVzY3JpcHRpb248YnI+LS0tLS0tLS0tLS0tLS0tLSstLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0tLS0tLS0r
LS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS08YnI+
wqBsYW5nIMKgIMKgIMKgIMKgIMKgIHwgdGV4dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCDCoCDCoCDCoCDCoCDCoHwgZXh0ZW5kZWQg
fCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoHRpZCDCoCDC
oCDCoCDCoCDCoCDCoHwgdGV4dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKg
IMKgIMKgIMKgIHwgbm90IG51bGwgfCDCoCDCoCDCoCDCoCDCoHwgZXh0ZW5kZWQgfCDCoCDCoCDC
oCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoGJhc2V0aWQgwqAgwqAgwqAg
wqB8IHRleHQgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCB8
IG5vdCBudWxsIHwgwqAgwqAgwqAgwqAgwqB8IGV4dGVuZGVkIHwgwqAgwqAgwqAgwqAgwqAgwqAg
fCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBzcG9ydGlkIMKgIMKgIMKgIMKgfCB0ZXh0IMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDC
oCDCoHwgwqAgwqAgwqAgwqAgwqB8IGV4dGVuZGVkIHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDC
oCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBicmFuZGlkIMKgIMKgIMKgIMKgfCB0ZXh0IMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgfCBub3QgbnVsbCB8IMKgIMKg
IMKgIMKgIMKgfCBleHRlbmRlZCB8IMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAg
wqAgwqB8PGJyPsKgdHJhbnNsYXRlZHRleHQgfCB0ZXh0IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoHwgwqAgwqAgwqAgwqAgwqB8
IGV4dGVuZGVkIHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+
wqBvYmplY3R0eXBlIMKgIMKgIHwgdGV4dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8
IMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgfCBleHRlbmRl
ZCB8IMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgwqAgwqB8PGJyPsKgY3JlYXRl
ZGF0IMKgIMKgIMKgfCB0aW1lc3RhbXAgd2l0aCB0aW1lIHpvbmUgfCDCoCDCoCDCoCDCoCDCoCB8
IG5vdCBudWxsIHwgbm93KCkgwqAgwqB8IHBsYWluIMKgIMKgfCDCoCDCoCDCoCDCoCDCoCDCoCB8
IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoG1vZGlmaWVkIMKgIMKgIMKgIHwgdGltZXN0YW1w
IHdpdGggdGltZSB6b25lIHwgwqAgwqAgwqAgwqAgwqAgfCBub3QgbnVsbCB8IG5vdygpIMKgIMKg
fCBwbGFpbiDCoCDCoHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8
YnI+wqBtb2RpZmllZGJ5IMKgIMKgIHwgdGV4dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCAmIzM5OyYjMzk7Ojp0ZXh0IHwgZXh0ZW5k
ZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoHZlcnNp
b24gwqAgwqAgwqAgwqB8IGludGVnZXIgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqB8IMKgIMKg
IMKgIMKgIMKgIHwgbm90IG51bGwgfCAwIMKgIMKgIMKgIMKgfCBwbGFpbiDCoCDCoHwgwqAgwqAg
wqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+SW5kZXhlczo8YnI+wqAgwqAg
JnF1b3Q7cGtfZGljdGlvbmFyeSZxdW90OyBQUklNQVJZIEtFWSwgYnRyZWUgKGxhbmcsIHRpZCk8
YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlfYmFzZXRpZCZxdW90OyBidHJlZSAoYmFzZXRp
ZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlfYnJhbmRpZCZxdW90OyBidHJlZSAoYnJh
bmRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlfb2JqZWN0dHlwZSZxdW90OyBidHJl
ZSAob2JqZWN0dHlwZSk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlfc3BvcnRpZCZxdW90
OyBidHJlZSAoc3BvcnRpZCk8YnI+VHJpZ2dlcnM6PGJyPsKgIMKgIGlfZGljdGlvbmFyeV9jcmVh
dGVkYXQgQkVGT1JFIElOU0VSVCBPTiBkaWN0aW9uYXJ5IEZPUiBFQUNIIFJPVyBFWEVDVVRFIEZV
TkNUSU9OIHVwZGF0ZV9jcmVhdGVkYXRfY29sKCk8YnI+wqAgwqAgaV9kaWN0aW9uYXJ5X21vZGlm
aWVkIEJFRk9SRSBJTlNFUlQgT1IgVVBEQVRFIE9OIGRpY3Rpb25hcnkgRk9SIEVBQ0ggUk9XIEVY
RUNVVEUgRlVOQ1RJT04gdXBkYXRlX21vZGlmaWVkX2NvbCgpPGJyPkFjY2VzcyBtZXRob2Q6IGhl
YXA8YnI+PC9kaXY+PGRpdj5Ib3cgZG8gd2UgaW1wcm92ZSB0aGlzIHF1ZXJ5IHBlcmZvcm1hbmNl
IHdpdGhvdXQgdGFraW5nIG1vcmUgY3B1PzwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+UmVnYXJk
cyw8L2Rpdj48ZGl2PkR1cmdhIE1haGVzaDwvZGl2PjwvZGl2Pg0KPC9ibG9ja3F1b3RlPjwvZGl2
Pg0KPC9ibG9ja3F1b3RlPjwvZGl2Pg0K
--000000000000b745170621ef0b9d--