Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soJ3f-00A8E0-U6 for pgsql-in-general@arkaria.postgresql.org; Wed, 11 Sep 2024 08:50:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1soJ3e-00CPuN-LO for pgsql-in-general@arkaria.postgresql.org; Wed, 11 Sep 2024 08:50:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soJ3e-00CPuD-A0 for pgsql-in-general@lists.postgresql.org; Wed, 11 Sep 2024 08:50:38 +0000 Received: from mail-ej1-x631.google.com ([2a00:1450:4864:20::631]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1soJ3b-000bQo-9v for pgsql-in-general@postgresql.org; Wed, 11 Sep 2024 08:50:37 +0000 Received: by mail-ej1-x631.google.com with SMTP id a640c23a62f3a-a8d2b24b7a8so126645666b.1 for ; Wed, 11 Sep 2024 01:50:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726044634; x=1726649434; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=PWSFKpwyjJGUPrRaA9eDPRAVXk7ilCQmnH1yjP4Pa5U=; b=QsqWJfp9QIYN4+YFeihbwfjf1hOW6s8s0czE0BWTI63Um4Kl8i7AV2WU/RpNZk4eh6 tmAIdhRO7u2s85i7w/0pKk8SVgtrWxJv+///VQY8J8e8btJmgT0UOlYyiLiCOjBFZFHF zb60B2FSVA316pdsU7h6E9rJSFRTLdppOCuTY2m/zG0+781aRPi5tRKZ7AEyNlY+tQDX rU9tF2X6UyAwYY4qIIxt8ih4tgEYlwStBPCeDrvQ3XwQfOs5nSZK3rUDr4OY+CRa932F EYW476v6z0q4lAlGbGSjh6bYpupakB4Yv4+/b6BlBVHpLVuuyNgtu023wuUH5Re1IPYD pXew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726044634; x=1726649434; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=PWSFKpwyjJGUPrRaA9eDPRAVXk7ilCQmnH1yjP4Pa5U=; b=C0BxLO5yPM+uODe9ib9tkaLSmiw1xQJmEk46pHBsmdbreVRguAeIX8h/Ni6CLklYtr EffQQJL6JixWHEftsHLM1cMtOPN3B6YZ6TR71aXFjbVyl9fBgPLw7nBLhcyKtbectUgl 1l7VZY5pvkMIKRbAMaZvVqoNGkkccoGkqznh//is+ev3W2uhn1GkHxc3pqMJPz+6a77Z zUYxCq6BR0YuzUOPh1VUmU/yD2eCQHMoPfM0JoittDB1sSsMmwiC4dw1Zb8y7aZt8b9M CWT+xGN+HWG0fSSXJKPeVcEtaVEXfGgfnwlyJbZBaEZsDRuUrTC4OwmA3UQLGv2yW6dn XLWA== X-Forwarded-Encrypted: i=1; AJvYcCXFrjMJMgiu1qLwHrbGpr7Z35o2CeB+xK5fA3O5PIWYdqakT5lS3GrrI1C2j2+BBVc3mWtSiXIXSB/AYtuSY5RI@postgresql.org X-Gm-Message-State: AOJu0YyNqyedh2r2e6ahbLjmbQYP9zWu8B+La24NG9KDGIysCEh4Ev4i ake+Q+bZxDp+7OcERNdij0AxY+jUvxCEz4W7TBvTiKREnFHdVHBGmm1Udl65Tln0c3fEXxERehH FLotuaAumlRY740DPlq34vk3yq+m3yIXSZss= X-Google-Smtp-Source: AGHT+IEvazU+bJKTQPyDLeVug+WO64mBNivaCTcmqwLJBeL4e38VDbuk2fD/vl1WaPMSP7LFARG+KElCOpfRjxDuJP0= X-Received: by 2002:a17:907:72d1:b0:a8d:6372:2d38 with SMTP id a640c23a62f3a-a8ffabc1d72mr421674766b.18.1726044633706; Wed, 11 Sep 2024 01:50:33 -0700 (PDT) MIME-Version: 1.0 From: Durgamahesh Manne Date: Wed, 11 Sep 2024 14:23:04 +0530 Message-ID: Subject: Recommendations on improving the insert on conflict do nothing performance To: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000042383a0621d415aa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000042383a0621d415aa Content-Type: text/plain; charset="UTF-8" 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 --00000000000042383a0621d415aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: base64 PGRpdiBkaXI9Imx0ciI+SGnCoDxkaXY+wqAgwqAgaW5zZXJ0IGludG8gZGljdGlvbmFyeShsYW5n LHRpZCxzcG9ydGlkLGJyYW5kaWQsdHJhbnNsYXRlZHRleHQsb2JqZWN0dHlwZSxiYXNldGlkKSB2 YWx1ZXMgKCQxLCQyLCQzLCQ0LCQ1LCQ2LCQ3KSBvbiBjb25mbGljdCBkbyBub3RoaW5nwqDCoDwv ZGl2PjxkaXY+wqAqOHZjcHVzIGFuZCAzMmdiIHJhbcKgPC9kaXY+PGRpdj7CoCDCoE51bWJlciBv ZiBjYWxscyBwZXIgc2VjIDE2MDAgYXQgdGhpcyB0aW1lIDQyJSBvZiBjcHUgdXRpbGl6ZWTCoDxi cj48L2Rpdj48ZGl2PsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgTWF4IGluIG1zIDMzLjYyIHBl ciBjYWxswqA8L2Rpdj48ZGl2PsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgQXZnIGluIG1zwqAg MC4xNyBwZXIgY2FsbMKgPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoFRhYmxlICZxdW90 O2RpY3Rpb25hcnkuZGljdGlvbmFyeSZxdW90Ozxicj7CoCDCoCDCoENvbHVtbiDCoCDCoCB8IMKg IMKgIMKgIMKgIMKgIFR5cGUgwqAgwqAgwqAgwqAgwqAgfCBDb2xsYXRpb24gfCBOdWxsYWJsZSB8 IERlZmF1bHQgwqB8IFN0b3JhZ2UgwqB8IENvbXByZXNzaW9uIHwgU3RhdHMgdGFyZ2V0IHwgRGVz Y3JpcHRpb248YnI+LS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSst LS0tLS0tLS0tLSstLS0tLS0tLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0t Ky0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS08YnI+wqBsYW5nIMKgIMKgIMKgIMKgIMKgIHwg dGV4dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90 IG51bGwgfCDCoCDCoCDCoCDCoCDCoHwgZXh0ZW5kZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKg IMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoHRpZCDCoCDCoCDCoCDCoCDCoCDCoHwgdGV4dCDCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCDC oCDCoCDCoCDCoCDCoHwgZXh0ZW5kZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKg IMKgIMKgIMKgfDxicj7CoGJhc2V0aWQgwqAgwqAgwqAgwqB8IHRleHQgwqAgwqAgwqAgwqAgwqAg wqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCB8IG5vdCBudWxsIHwgwqAgwqAgwqAgwqAg wqB8IGV4dGVuZGVkIHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8 YnI+wqBzcG9ydGlkIMKgIMKgIMKgIMKgfCB0ZXh0IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg IMKgIHwgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoHwgwqAgwqAgwqAgwqAgwqB8IGV4 dGVuZGVkIHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBi cmFuZGlkIMKgIMKgIMKgIMKgfCB0ZXh0IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwg wqAgwqAgwqAgwqAgwqAgfCBub3QgbnVsbCB8IMKgIMKgIMKgIMKgIMKgfCBleHRlbmRlZCB8IMKg IMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAgwqAgwqB8PGJyPsKgdHJhbnNsYXRlZHRl eHQgfCB0ZXh0IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAgwqAgwqAgwqAg fCDCoCDCoCDCoCDCoCDCoHwgwqAgwqAgwqAgwqAgwqB8IGV4dGVuZGVkIHwgwqAgwqAgwqAgwqAg wqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBvYmplY3R0eXBlIMKgIMKgIHwgdGV4 dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgwqAgwqAg wqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgfCBleHRlbmRlZCB8IMKgIMKgIMKgIMKgIMKgIMKgIHwg wqAgwqAgwqAgwqAgwqAgwqAgwqB8PGJyPsKgY3JlYXRlZGF0IMKgIMKgIMKgfCB0aW1lc3RhbXAg d2l0aCB0aW1lIHpvbmUgfCDCoCDCoCDCoCDCoCDCoCB8IG5vdCBudWxsIHwgbm93KCkgwqAgwqB8 IHBsYWluIMKgIMKgfCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxi cj7CoG1vZGlmaWVkIMKgIMKgIMKgIHwgdGltZXN0YW1wIHdpdGggdGltZSB6b25lIHwgwqAgwqAg wqAgwqAgwqAgfCBub3QgbnVsbCB8IG5vdygpIMKgIMKgfCBwbGFpbiDCoCDCoHwgwqAgwqAgwqAg wqAgwqAgwqAgfCDCoCDCoCDCoCDCoCDCoCDCoCDCoHw8YnI+wqBtb2RpZmllZGJ5IMKgIMKgIHwg dGV4dCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCB8IMKgIMKgIMKgIMKgIMKgIHwgbm90 IG51bGwgfCAmIzM5OyYjMzk7Ojp0ZXh0IHwgZXh0ZW5kZWQgfCDCoCDCoCDCoCDCoCDCoCDCoCB8 IMKgIMKgIMKgIMKgIMKgIMKgIMKgfDxicj7CoHZlcnNpb24gwqAgwqAgwqAgwqB8IGludGVnZXIg wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqB8IMKgIMKgIMKgIMKgIMKgIHwgbm90IG51bGwgfCAw IMKgIMKgIMKgIMKgfCBwbGFpbiDCoCDCoHwgwqAgwqAgwqAgwqAgwqAgwqAgfCDCoCDCoCDCoCDC oCDCoCDCoCDCoHw8YnI+SW5kZXhlczo8YnI+wqAgwqAgJnF1b3Q7cGtfZGljdGlvbmFyeSZxdW90 OyBQUklNQVJZIEtFWSwgYnRyZWUgKGxhbmcsIHRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2RpY3Rp b25hcnlfYmFzZXRpZCZxdW90OyBidHJlZSAoYmFzZXRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4X2Rp Y3Rpb25hcnlfYnJhbmRpZCZxdW90OyBidHJlZSAoYnJhbmRpZCk8YnI+wqAgwqAgJnF1b3Q7aWR4 X2RpY3Rpb25hcnlfb2JqZWN0dHlwZSZxdW90OyBidHJlZSAob2JqZWN0dHlwZSk8YnI+wqAgwqAg JnF1b3Q7aWR4X2RpY3Rpb25hcnlfc3BvcnRpZCZxdW90OyBidHJlZSAoc3BvcnRpZCk8YnI+VHJp Z2dlcnM6PGJyPsKgIMKgIGlfZGljdGlvbmFyeV9jcmVhdGVkYXQgQkVGT1JFIElOU0VSVCBPTiBk aWN0aW9uYXJ5IEZPUiBFQUNIIFJPVyBFWEVDVVRFIEZVTkNUSU9OIHVwZGF0ZV9jcmVhdGVkYXRf Y29sKCk8YnI+wqAgwqAgaV9kaWN0aW9uYXJ5X21vZGlmaWVkIEJFRk9SRSBJTlNFUlQgT1IgVVBE QVRFIE9OIGRpY3Rpb25hcnkgRk9SIEVBQ0ggUk9XIEVYRUNVVEUgRlVOQ1RJT04gdXBkYXRlX21v ZGlmaWVkX2NvbCgpPGJyPkFjY2VzcyBtZXRob2Q6IGhlYXA8YnI+PC9kaXY+PGRpdj5Ib3cgZG8g d2UgaW1wcm92ZSB0aGlzIHF1ZXJ5IHBlcmZvcm1hbmNlIHdpdGhvdXQgdGFraW5nIG1vcmUgY3B1 PzwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+UmVnYXJkcyw8L2Rpdj48ZGl2PkR1cmdhIE1haGVz aDwvZGl2PjwvZGl2Pg0K --00000000000042383a0621d415aa--