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

--0000000000001b1cd40621e4a461
Content-Type: text/plain; charset="UTF-8"

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 = '16MB'
   shared_buffers = '8GB'
   effective_cache_size = '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
>

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

PGRpdiBkaXI9Imx0ciI+SGksPGJyPllvdSBjYW4gdXNlIHRoZSBmb2xsb3dpbmcgYXBwcm9hY2hl
cyBmb3Igb3B0aW1pemF0aW9uOjxkaXY+PHVsPjxsaT5JbnN0ZWFkIG9mIGluc2VydGluZyBvbmUg
cm93IGF0IGEgdGltZSwgcGVyZm9ybSBidWxrIGluc2VydHMsIHdoaWNoIHdpbGwgcmVkdWNlIHRo
ZSBvdmVyaGVhZCBvZiBlYWNoIGluZGl2aWR1YWwgdHJhbnNhY3Rpb248L2xpPjxsaT4NCg0KUGFy
dGl0aW9uaW5nIGNhbiBpbXByb3ZlIHdyaXRlIHBlcmZvcm1hbmNlIGJ5IHNwbGl0dGluZyB0aGUg
ZGF0YSBpbnRvIHNtYWxsZXIsIG1vcmUgbWFuYWdlYWJsZSBjaHVua3MNCg0KwqDCoDxicj48L2xp
PjxsaT5UdW5lIHBvc3RncmVzIGNvbmZpZ3VyYXRpb27CoGxpa2U8YnI+d29ya19tZW0gPSA8c3Bh
biBjbGFzcz0iZ21haWwtaGxqcy1zdHJpbmciPiYjMzk7MTZNQiYjMzk7PC9zcGFuPsKgPGJyPnNo
YXJlZF9idWZmZXJzID0gPHNwYW4gY2xhc3M9ImdtYWlsLWhsanMtc3RyaW5nIj4mIzM5OzhHQiYj
Mzk7PC9zcGFuPg0KPGJyPmVmZmVjdGl2ZV9jYWNoZV9zaXplID0gPHNwYW4gY2xhc3M9ImdtYWls
LWhsanMtc3RyaW5nIj4mIzM5OzI0R0ImIzM5Ozwvc3Bhbj48L2xpPjwvdWw+PC9kaXY+PC9kaXY+
PGJyPjxkaXYgY2xhc3M9ImdtYWlsX3F1b3RlIj48ZGl2IGRpcj0ibHRyIiBjbGFzcz0iZ21haWxf
YXR0ciI+T24gV2VkLCAxMSBTZXB0IDIwMjQgYXQgMTM6NTAsIER1cmdhbWFoZXNoIE1hbm5lICZs
dDs8YSBocmVmPSJtYWlsdG86bWFoZXNocG9zdGdyZXM5QGdtYWlsLmNvbSI+bWFoZXNocG9zdGdy
ZXM5QGdtYWlsLmNvbTwvYT4mZ3Q7IHdyb3RlOjxicj48L2Rpdj48YmxvY2txdW90ZSBjbGFzcz0i
Z21haWxfcXVvdGUiIHN0eWxlPSJtYXJnaW46MHB4IDBweCAwcHggMC44ZXg7Ym9yZGVyLWxlZnQ6
MXB4IHNvbGlkIHJnYigyMDQsMjA0LDIwNCk7cGFkZGluZy1sZWZ0OjFleCI+PGRpdiBkaXI9Imx0
ciI+SGnCoDxkaXY+wqAgwqAgaW5zZXJ0IGludG8gZGljdGlvbmFyeShsYW5nLHRpZCxzcG9ydGlk
LGJyYW5kaWQsdHJhbnNsYXRlZHRleHQsb2JqZWN0dHlwZSxiYXNldGlkKSB2YWx1ZXMgKCQxLCQy
LCQzLCQ0LCQ1LCQ2LCQ3KSBvbiBjb25mbGljdCBkbyBub3RoaW5nwqDCoDwvZGl2PjxkaXY+wqAq
OHZjcHVzIGFuZCAzMmdiIHJhbcKgPC9kaXY+PGRpdj7CoCDCoE51bWJlciBvZiBjYWxscyBwZXIg
c2VjIDE2MDAgYXQgdGhpcyB0aW1lIDQyJSBvZiBjcHUgdXRpbGl6ZWTCoDxicj48L2Rpdj48ZGl2
PsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgTWF4IGluIG1zIDMzLjYyIHBlciBjYWxswqA8L2Rp
dj48ZGl2PsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgQXZnIGluIG1zwqAgMC4xNyBwZXIgY2Fs
bMKgPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFRhYmxlICZxdW90O2RpY3Rpb25hcnku
ZGljdGlvbmFyeSZxdW90Ozxicj7CoCDCoCDCoENvbHVtbiDCoCDCoCB8IMKgIMKgIMKgIMKgIMKg
IFR5cGUgwqAgwqAgwqAgwqAgwqAgfCBDb2xsYXRpb24gfCBOdWxsYWJsZSB8IERlZmF1bHQgwqB8
IFN0b3JhZ2UgwqB8IENvbXByZXNzaW9uIHwgU3RhdHMgdGFyZ2V0IHwgRGVzY3JpcHRpb248YnI+
LS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLSst
LS0tLS0tLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0t
LS0tKy0tLS0tLS0tLS0tLS08YnI+wqBsYW5nIMKgIMKgIMKgIMKgIMKgIHwgdGV4dCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCDCoCDC
oCDCoCDCoCDCoHwgZXh0ZW5kZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKg
IMKgIMKgfDxicj7CoHRpZCDCoCDCoCDCoCDCoCDCoCDCoHwgdGV4dCDCoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCDCoCDCoCDCoCDCoCDC
oHwgZXh0ZW5kZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxi
cj7CoGJhc2V0aWQgwqAgwqAgwqAgwqB8IHRleHQgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg
wqAgfCDCoCDCoCDCoCDCoCDCoCB8IG5vdCBudWxsIHwgwqAgwqAgwqAgwqAgwqB8IGV4dGVuZGVk
IHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBzcG9ydGlk
IMKgIMKgIMKgIMKgfCB0ZXh0IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAg
wqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoHwgwqAgwqAgwqAgwqAgwqB8IGV4dGVuZGVkIHwgwqAg
wqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBicmFuZGlkIMKgIMKg
IMKgIMKgfCB0ZXh0IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAg
wqAgfCBub3QgbnVsbCB8IMKgIMKgIMKgIMKgIMKgfCBleHRlbmRlZCB8IMKgIMKgIMKgIMKgIMKg
IMKgIHwgwqAgwqAgwqAgwqAgwqAgwqAgwqB8PGJyPsKgdHJhbnNsYXRlZHRleHQgfCB0ZXh0IMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDC
oCDCoHwgwqAgwqAgwqAgwqAgwqB8IGV4dGVuZGVkIHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDC
oCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBvYmplY3R0eXBlIMKgIMKgIHwgdGV4dCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqB8IMKg
IMKgIMKgIMKgIMKgfCBleHRlbmRlZCB8IMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAg
wqAgwqAgwqB8PGJyPsKgY3JlYXRlZGF0IMKgIMKgIMKgfCB0aW1lc3RhbXAgd2l0aCB0aW1lIHpv
bmUgfCDCoCDCoCDCoCDCoCDCoCB8IG5vdCBudWxsIHwgbm93KCkgwqAgwqB8IHBsYWluIMKgIMKg
fCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoG1vZGlmaWVk
IMKgIMKgIMKgIHwgdGltZXN0YW1wIHdpdGggdGltZSB6b25lIHwgwqAgwqAgwqAgwqAgwqAgfCBu
b3QgbnVsbCB8IG5vdygpIMKgIMKgfCBwbGFpbiDCoCDCoHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDC
oCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBtb2RpZmllZGJ5IMKgIMKgIHwgdGV4dCDCoCDCoCDC
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCAmIzM5
OyYjMzk7Ojp0ZXh0IHwgZXh0ZW5kZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKg
IMKgIMKgIMKgfDxicj7CoHZlcnNpb24gwqAgwqAgwqAgwqB8IGludGVnZXIgwqAgwqAgwqAgwqAg
wqAgwqAgwqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCAwIMKgIMKgIMKgIMKg
fCBwbGFpbiDCoCDCoHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8
YnI+SW5kZXhlczo8YnI+wqAgwqAgJnF1b3Q7cGtfZGljdGlvbmFyeSZxdW90OyBQUklNQVJZIEtF
WSwgYnRyZWUgKGxhbmcsIHRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlfYmFzZXRp
ZCZxdW90OyBidHJlZSAoYmFzZXRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlfYnJh
bmRpZCZxdW90OyBidHJlZSAoYnJhbmRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rpb25hcnlf
b2JqZWN0dHlwZSZxdW90OyBidHJlZSAob2JqZWN0dHlwZSk8YnI+wqAgwqAgJnF1b3Q7aWR4X2Rp
Y3Rpb25hcnlfc3BvcnRpZCZxdW90OyBidHJlZSAoc3BvcnRpZCk8YnI+VHJpZ2dlcnM6PGJyPsKg
IMKgIGlfZGljdGlvbmFyeV9jcmVhdGVkYXQgQkVGT1JFIElOU0VSVCBPTiBkaWN0aW9uYXJ5IEZP
UiBFQUNIIFJPVyBFWEVDVVRFIEZVTkNUSU9OIHVwZGF0ZV9jcmVhdGVkYXRfY29sKCk8YnI+wqAg
wqAgaV9kaWN0aW9uYXJ5X21vZGlmaWVkIEJFRk9SRSBJTlNFUlQgT1IgVVBEQVRFIE9OIGRpY3Rp
b25hcnkgRk9SIEVBQ0ggUk9XIEVYRUNVVEUgRlVOQ1RJT04gdXBkYXRlX21vZGlmaWVkX2NvbCgp
PGJyPkFjY2VzcyBtZXRob2Q6IGhlYXA8YnI+PC9kaXY+PGRpdj5Ib3cgZG8gd2UgaW1wcm92ZSB0
aGlzIHF1ZXJ5IHBlcmZvcm1hbmNlIHdpdGhvdXQgdGFraW5nIG1vcmUgY3B1PzwvZGl2PjxkaXY+
PGJyPjwvZGl2PjxkaXY+UmVnYXJkcyw8L2Rpdj48ZGl2PkR1cmdhIE1haGVzaDwvZGl2PjwvZGl2
Pg0KPC9ibG9ja3F1b3RlPjwvZGl2Pg0K
--0000000000001b1cd40621e4a461--