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 1soFVD-009aJo-5w for pgsql-in-general@arkaria.postgresql.org; Wed, 11 Sep 2024 05:02:52 +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 1soFVC-008rgk-Hc for pgsql-in-general@arkaria.postgresql.org; Wed, 11 Sep 2024 05:02:50 +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 1soFVC-008rgO-63; Wed, 11 Sep 2024 05:02:50 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1soFV9-000Zld-3I; Wed, 11 Sep 2024 05:02:48 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a8d56155f51so177497666b.2; Tue, 10 Sep 2024 22:02:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726030966; x=1726635766; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=4KEpjS5jiimvnyG4yj03asPkLBTA4WZqGmj8LJFysy4=; b=YMXWwQfU+OF13oceixtd9nKxN+6I/oM0gfyLyiS1WpLbwDEaC7cJISx0ofWRlzz6mv hmrJgep+pX7gKVNAjqCO7fBpPRfPhS+9/W6WGkpnn4cJuyHtV1ov2xiOFqv8OA6lHWRr IyyYLhSHD3cvxf2xgFpbiOt5vZE4LjK1ho5SIjTmBINz+zOEV3biB4aEOowwLS61bcNq eYsOJZAljndjxwgfVtLvH6163z5xc/a793qnW7FqoV8zs1r8SukWJEkaoa222UTiZYe9 usQJpgXbaMWmxTgr4PcVUMqtRopMKBaE9RTy2duZmFDwDsmIdbwtAU+OCB1t4iqeKncl o0OA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726030966; x=1726635766; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=4KEpjS5jiimvnyG4yj03asPkLBTA4WZqGmj8LJFysy4=; b=YC5bD7sC2g5NKF6S5bTB1WWj6JLTDjkhKIBA35qzied6DsCv3e4mHi5TobkO1uKFSa eHINp44iTsjpwfXqvNDLBPB45oHALT6nCmkj2nUiMcSlD0kWyG6HlM7HMS7agghpccx+ px88djKmaBZA+dwOtoDybK7zXeERO0TWAtxP+XiGH9W4Rx79wCoGhQKfGn9uMFOIh8T2 GN1NFSmdS/S5mll9qDCiP0on8m+rFVRzqnepqqtqyDImBqjr7uDsryA1jjb6elmGysds wZPUXWoBShRaefuNteDpsFtuouDCx8+a8squv/TDBnolC6QmHYScaRmS2ZNXh+WAp+4m JtOA== X-Forwarded-Encrypted: i=1; AJvYcCUbahuFXKyfpACR/FOdQsqA+alQF5rw6jki/K0fkTYEC831CJshNAgt/0mJEKcAW+jCmKITswo7QrgqXzwa1a2AoA==@postgresql.org, AJvYcCVhNCJF0mkFHUbc+Qzpf1bPzvJOR0c3MxY2ytuAC7ywnZYvhkLWvIZVoTbXKapllo4NhFdc4fopv4B+DBXh@postgresql.org X-Gm-Message-State: AOJu0YwZB0eiBAWhE/BSN0HXIngbNsb6jfFcbpzU3JH+ccZSNJ3vvl+B yhDyPpZPxdzFVZg6GoVFy3IZLXLJeMngpxtlNFXf8oNGsd2QrwKaW5sZn5LnsdfZg/POmWZ1yb9 dibli1HYSDWNd5nk4G3YR8dJFOCs= X-Google-Smtp-Source: AGHT+IHvZ/PBjsUKOhI5BxNw8+dStGGMCblBJVQu6t5It8hp1/WFMfXAWxfrZ5k6qRj62UIosz+X3eY+CFVHU7an98g= X-Received: by 2002:a17:907:ea8:b0:a8a:71d5:1094 with SMTP id a640c23a62f3a-a9004a46fbdmr145729066b.46.1726030965564; Tue, 10 Sep 2024 22:02:45 -0700 (PDT) MIME-Version: 1.0 From: Durgamahesh Manne Date: Wed, 11 Sep 2024 10:35:16 +0530 Message-ID: Subject: Performance degrade on insert on conflict do nothing To: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org, Christoph Berg , laurenz.albe@cybertec.at, semab tariq , PostgreSQL mailing lists Content-Type: multipart/alternative; boundary="00000000000092ec9f0621d0e651" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000092ec9f0621d0e651 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 --00000000000092ec9f0621d0e651 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 aDwvZGl2PjxkaXY+PGJyPjwvZGl2PjwvZGl2Pg0K --00000000000092ec9f0621d0e651--