Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kf895-0001UJ-1q for pgsql-admin@arkaria.postgresql.org; Tue, 17 Nov 2020 21:04:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kf892-0000Qc-CQ for pgsql-admin@arkaria.postgresql.org; Tue, 17 Nov 2020 21:04:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kf892-0000QV-2z for pgsql-admin@lists.postgresql.org; Tue, 17 Nov 2020 21:04:08 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kf88w-0001It-P4 for pgsql-admin@lists.postgresql.org; Tue, 17 Nov 2020 21:04:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1605647039; bh=HZtRCbYah7CqHtzUMrCYS5J3Ty96dQNUAWDyfiDrUKA=; h=X-UI-Sender-Class:Subject:To:References:Cc:From:Date:In-Reply-To; b=ATDgUzT4ris9TEB4GT8g1CwYxkOPFq/ciOzlOGidrjjzoVqbsH2OTlSuO0Vo1QRfI fntgTYi4hdZ0GnqEJ/bZse+C3OVlJhJZBWvsLkLanfQik9ZUKKe2fIGAHwRys+fs/K OQxoq7xuqr796nVLH98WosFDGIPBmbYRcCZBwmRQ= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from [192.168.178.20] ([83.171.161.141]) by mail.gmx.com (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MQe5u-1krpjU3QbG-00NlNg; Tue, 17 Nov 2020 22:03:58 +0100 Subject: Re: PostgreSQL Tuning and running a query on a big data To: pgsql-admin@lists.postgresql.org References: Cc: Sachin Kumar From: Thomas Kellerer Message-ID: Date: Tue, 17 Nov 2020 22:03:57 +0100 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-GB Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:2n8MwlXxU49Bjxm16Yq0QXF1x7Rbq3Ebc5Z35L+w1FZPb9tLBG5 RPyqtlHVhEdQPWMGx8vbK3rLauvbHZRrW1a3kkccbdaYr1X71klI2LWNlBo6ZYck1EgOMM+ /AMDtPCPXuT/h6hkGPz+gnUy0IPi+mI5dLuuxPgimvr6J2RZJ/3AgShFGcEm/hR6Ucj6vR2 Z3rOfG8YACV9ohF7QONFQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:PCCcfVR1ovc=:S700AB4AXJUNsbsy2s3eEu 2Pp2l+JIYF1jIXdeL7LZ7Mg8F+xoCDes48B5572nRv7Jdv4f0YsAGk+eBVTuipTs9n2Dk3EbE SuiazrSeYzU+Ll4/PWMF1uC3S/UTFRjHoz/sQkcBoiG99tU7STgBe+ermgrje1ka9LJrho7Hd IAv/fYnXIH/e113D56vKN3kvizAmv0XY13zYMxFUT7xVPi3w8XTUaWoam268nhLdq7hPwGviB ORYYC579Ie/hID2vf8fwFZ+j52876DWXFWo0PjaNHRs9uIQTCYWjvcsvOgoLAh7lLB4LzlIHS CAo0lN8WPJtcaPiRVZMuvI3VjClo3PBt6jgzJKy1t2DfXv0kfdEgkg09HBXpMTqVxDt+dcmYg kqJiXSQB3Oe/djxlxX4Nr4AetLDp2U1yMllW+KPSxcgvCxVYEEvdmcCaHfh9EojvanHCeT76z 4Dby7HB32FNNi+kPwjfMhjrVzoZ8z75doBkddtpQ+vz6BEPVnfJxADiI6jGzeiqqNiaoD58DC ywsQLSnGeHrbRbzq2mboCzdK9Aa1jOcIo8Tc2y32VDtZXPmhC3Ex21WMm2YpTq+UJYR3z1u8d ANg7NIokx+SsB4TjjFcfs1cIBWHSN27cgTzsJddwPb3gJUKCOIdgqgiQe7+SnIPdidCncE1kj DnunnN2fHawr4Hzkdc9S1OrZCNjZBhacPLubLaHgVptj9B/Gud9sKu1N05NX9p7T/hebtgqaf mmA+IXH4SCoXyhqNMU/ZRgv5WVDl1+XKH3gDKY+18IwQuMbKRDV31IAkWTn3HETb4MelExoZ1 Sn2q6iiJPaWZbzV0/JKD7EfXsRS2BBzF8tlhlKn6WlUdl/DNmsvRAITmsWDPg2j1VmFuZa7R/ YnQGS0glLZ1BVRgMQUKw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Sachin Kumar schrieb am 17.11.2020 um 18:34: > 3.=C2=A0 =C2=A0while running the query on 1 million cards it is taking t= oo much time, say 150 min. is there any way I can reduce it. > *Query I am using* > UPDATE hk_card_master_test m > SET "ACCOUNT_NUMBER" =3D v."v_account_number", "ISSUANCE_NUMBER" =3D v."= v_issuance_number","cron"=3D1 > FROM ( > SELECT h."id",h."CARD_SEQUENCE_NUMBER" ,h."ACCOUNT_NUMBER" ,h."ISSUANCE_= NUMBER",c."ACCOUNT_NUMBER" v_account_number,c."ISSUANCE_NUMBER" v_issuance= _number > FROM > hk_card_master_test h > JOIN > vdaccount_card_bank c > ON SUBSTR(c."ACCOUNT_NUMBER", 1, 10) =3D h."CARD_SEQUENCE_NUMBER" > ORDER BY h."id" ASC LIMIT 1000 > ) AS v > WHERE m."CARD_SEQUENCE_NUMBER" =3D v."CARD_SEQUENCE_NUMBER"; The target table of an UPDATE shouldn't be repeated in the FROM clause in = Postgres. Not sure why you have the LIMIT in the sub-select, but if that is only for testing purposes, then I think the following should do what you want, but = much faster: UPDATE hk_card_master_test m SET "ACCOUNT_NUMBER" =3D v."v_account_number", "ISSUANCE_NUMBER" =3D v."v_issuance_number", "cron"=3D1 FROM vdaccount_card_bank v WHERE SUBSTR(v."ACCOUNT_NUMBER", 1, 10) =3D m."CARD_SEQUENCE_NUMBER" You probably want those indexes: create index on vdaccount_card_bank ( (SUBSTR("ACCOUNT_NUMBER", 1, 10)= ); create index on hk_card_master_test ("CARD_SEQUENCE_NUMBER"); Unrelated to your question, but using quoted/uppercase identifiers is gene= rally discouraged in Postgres: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_tabl= e_or_column_names you probably will have a lot less trouble if you get rid of those. Thomas