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 1qMOh2-002IpA-CK for pgsql-general@arkaria.postgresql.org; Thu, 20 Jul 2023 08:07:25 +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 1qMOgz-002knU-8U for pgsql-general@arkaria.postgresql.org; Thu, 20 Jul 2023 08:07:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMOgy-002knM-RJ for pgsql-general@lists.postgresql.org; Thu, 20 Jul 2023 08:07:20 +0000 Received: from mout-u-107.mailbox.org ([80.241.59.207]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMOgv-000boz-VA for pgsql-general@lists.postgresql.org; Thu, 20 Jul 2023 08:07:20 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [IPv6:2001:67c:2050:b231:465::1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4R64zR3dT6z9sQZ; Thu, 20 Jul 2023 10:07:15 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1689840435; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=lgcVTN1iyLdkSPM1lYwUD0a9Der1Gt9OgLI6dfe6+pU=; b=eQQGi30HdFvJoL147AQ7Gb51bFyP05HgmOgo2SIv16dkL5VubN5h0jdyUDVHyodTMA93W3 VMxVNcXZOGblV+d9OIQz98qEBRCcad7IVqlpaVosN339E60MslRVPTzR5MxG2FBhpxVWqp Pc1LaAGOz6rTYDgnw1QhMfkGeGRoAMAMHj2Z7xjXZLmqQZeWW8fjMWZROu+JoPMPPcIYK/ S1LxNCYmJTJdbnSNLQtkllPCnMyfLmST21zragkro6OIRwNBhIIOD0G0wBX63zm47pxUIQ 8+YR/+j52DILBiFRc5dKf1sAqwlqebbvyWBWRgm6ytzUQYiHuBRseEzW3rs3mg== Date: Thu, 20 Jul 2023 10:07:15 +0200 (CEST) From: Erik Wienhold To: gzh , pgsql-general@lists.postgresql.org Message-ID: <1360960490.395085.1689840435026@office.mailbox.org> In-Reply-To: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> Subject: =?UTF-8?Q?Re:_How_to_improve_the_performance_of_my_SQL_query=EF=BC=9F?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4R64zR3dT6z9sQZ List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 20/07/2023 09:09 CEST gzh wrote: > > I'm running into some performance issues with my SQL query. > The following SQL query is taking a long time to execute. > > explain analyze > select COUNT(ET_CD) > from TBL_SHA > WHERE TBL_SHA.MS_CD = '009' > and TBL_SHA.ETRYS in > (select TBL_INF.RY_CD > from TBL_INF > WHERE TBL_INF.MS_CD = '009' > AND TBL_INF.RY_CD = '000001' > ) > ----- Execution Plan ----- > Limit (cost=2738709.57..2738709.58 rows=1 width=8) (actual time=124168.769..124168.771 rows=1 loops=1) > -> Aggregate (cost=2738709.57..2738709.58 rows=1 width=8) (actual time=124168.767..124168.769 rows=1 loops=1) > -> Nested Loop (cost=0.29..2730702.63 rows=3202774 width=9) (actual time=97264.166..123920.769 rows=3200000 loops=1) > -> Index Only Scan using TBL_INF_pkc on TBL_INF (cost=0.29..8.31 rows=1 width=9) (actual time=0.025..0.030 rows=1 loops=1) > Index Cond: ((MS_CD = '009'::bpchar) AND (RY_CD = '000001'::bpchar)) > Heap Fetches: 1 > -> Seq Scan on TBL_SHA (cost=0.00..2698666.58 rows=3202774 width=18) (actual time=97264.138..123554.792 rows=3200000 loops=1) > Filter: ((MS_CD = '009'::bpchar) AND (ETRYS = '000001'::bpchar)) > Rows Removed by Filter: 32000325 > Planning Time: 0.162 ms > Execution Time: 124168.838 ms > -------------------------------------------------------------------------------- > > The index is defined as follows. > > CREATE INDEX index_search_01 ON mdb.TBL_SHA USING btree (MS_CD, ETRYS); > > > When I take the following sql statement, the index works fine and the query > is fast. > > select COUNT(ET_CD) > from TBL_SHA > WHERE MS_CD = '009' > AND ETRYS = '000001' > > The amount of data in the table is as follows. > TBL_SHA 38700325 > TBL_INF 35546 > > Any suggestions for improving the performance of the query would be greatly > appreciated. You can try EXISTS instead of IN to correlate the subquery and the outer query: SELECT count(et_cd) FROM tbl_sha WHERE tbl_sha.ms_cd = '009' AND tbl_sha.etrys = '000001' AND EXISTS ( SELECT FROM tbl_inf WHERE tbl_inf.ms_cd = tbl_sha.ms_cd AND tbl_inf.ry_cd = tbl_sha.etrys ) -- Erik