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 1qMSJG-002Rim-QB for pgsql-general@arkaria.postgresql.org; Thu, 20 Jul 2023 11:59:06 +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 1qMSJF-00677G-4a for pgsql-general@arkaria.postgresql.org; Thu, 20 Jul 2023 11:59:05 +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 1qMSJE-006778-Lm for pgsql-general@lists.postgresql.org; Thu, 20 Jul 2023 11:59:04 +0000 Received: from mail-wr1-x42b.google.com ([2a00:1450:4864:20::42b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMSJB-000doZ-8x for pgsql-general@lists.postgresql.org; Thu, 20 Jul 2023 11:59:04 +0000 Received: by mail-wr1-x42b.google.com with SMTP id ffacd0b85a97d-31427ddd3fbso586141f8f.0 for ; Thu, 20 Jul 2023 04:59:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1689854341; x=1690459141; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=42XTO6dErl0aNAFXHoLsaS07hcZKkTBGioEFLo6Qi1s=; b=qnjXtZszL5eEmToTdoihxopYE/iftNoScVFZRFbfikOxxHVRWQHBzj8TvqeNsz9mR5 yb27pFcoeoS9AcHN+gTGhhPfkbjBVyqvJNxVy6HOyyVMAUcXmiAhyTli842nDCu5Wl/E JnMZtyUm21UTYO3Z5oj3xcicwof+j8g2yGgzx5n+cUSbKq2DiEcdEbg1joz2Y5VtcFIu 5GjMsoQeE+AQlks1/PUO/pt5/IoI09UyueAOYZ1z6PUl31TBqyebfKgjaMKy7qdMY/+k +LSfJiLYDRgPBkt7o962Dd+aDb0wijMjsld5raJWsqCcCIoAvXbbldbrJx2/oBVpF+BV sE9Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689854341; x=1690459141; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=42XTO6dErl0aNAFXHoLsaS07hcZKkTBGioEFLo6Qi1s=; b=B7nqRH9mpI7gkj++MJjTuaqAIxZHL/b8HogsrT/NXY7A9Ea2Pup/B/WC0kw/e6n6c7 AmBuwzdsusHbvTSzafIwehTazzX67tBoi9lM5oT4oxMrTVy4856kFvyWXO8CfKnSjokU omJ5swcUyaqJbx1P/wMQfzc+4z5Wa4oEeNne6/RC7vVDcXiIHbSNQGStzOOvGZoa4oYQ UvhHd+UL8MMKqumAMnJDwLzvH1S7YwRhxuF2HoB6le0H75sOF686cLhxi9kdXZrdwFZx TLjXOE+KbDJhlx4m2UKfeQLYq0qRtueKVLri2iq+pmwVK/tOdVWj7IuY64iLP6Pzqct1 mwHw== X-Gm-Message-State: ABy/qLYB3WMUISwpbUyhvMYWA38DaI7pjXP16jnP65QRPS4qZ1erpkU5 6hpHrE38hDBhk6pVsD6gWbXj7g== X-Google-Smtp-Source: APBJJlFLmI/zgKORzQ+P5YndSf+BBJrl0z50r3dkY1o1OEf9E120ScECAL8PQ98GVABInUHjuMnZag== X-Received: by 2002:adf:f4c1:0:b0:315:94e7:e100 with SMTP id h1-20020adff4c1000000b0031594e7e100mr1783083wrp.55.1689854340986; Thu, 20 Jul 2023 04:59:00 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:9f9c:795:1a66:3a08:e762]) by smtp.gmail.com with ESMTPSA id a18-20020a5d5712000000b0030c4d8930b1sm1134919wrv.91.2023.07.20.04.59.00 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 20 Jul 2023 04:59:00 -0700 (PDT) Message-ID: <369ee851ef59bc84190f1631219b5a0c8f7907e9.camel@cybertec.at> Subject: Re: How to improve the performance of my SQL =?UTF-8?Q?query=EF=BC=9F?= From: Laurenz Albe To: gzh , pgsql-general@lists.postgresql.org Date: Thu, 20 Jul 2023 13:58:59 +0200 In-Reply-To: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 2023-07-20 at 15:09 +0800, gzh wrote: > I'm running into some performance issues with my SQL query. > The following SQL query is taking a long time to execute. >=20 > explain analyze > select COUNT(ET_CD) > from TBL_SHA > WHERE TBL_SHA.MS_CD =3D '009' > and TBL_SHA.ETRYS in > =C2=A0 =C2=A0(select TBL_INF.RY_CD > =C2=A0 =C2=A0 from TBL_INF > =C2=A0 =C2=A0 WHERE TBL_INF.MS_CD =3D '009' > =C2=A0 =C2=A0AND TBL_INF.RY_CD =3D '000001' > =C2=A0 =C2=A0) > ----- Execution Plan ----- > Limit=C2=A0 (cost=3D2738709.57..2738709.58 rows=3D1 width=3D8) (actual ti= me=3D124168.769..124168.771 rows=3D1 loops=3D1) > =C2=A0 ->=C2=A0 Aggregate=C2=A0 (cost=3D2738709.57..2738709.58 rows=3D1 w= idth=3D8) (actual time=3D124168.767..124168.769 rows=3D1 loops=3D1) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D0.29..2730= 702.63 rows=3D3202774 width=3D9) (actual time=3D97264.166..123920.769 rows= =3D3200000 loops=3D1) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Only Scan= using TBL_INF_pkc on TBL_INF=C2=A0 (cost=3D0.29..8.31 rows=3D1 width=3D9) = (actual time=3D0.025..0.030 rows=3D1 loops=3D1) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Ind= ex Cond: ((MS_CD =3D '009'::bpchar) AND (RY_CD =3D '000001'::bpchar)) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Hea= p Fetches: 1 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on TBL= _SHA=C2=A0 (cost=3D0.00..2698666.58 rows=3D3202774 width=3D18) (actual time= =3D97264.138..123554.792 rows=3D3200000 loops=3D1) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Fil= ter: ((MS_CD =3D '009'::bpchar) AND (ETRYS =3D '000001'::bpchar)) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Row= s Removed by Filter: 32000325 > Planning Time: 0.162 ms > Execution Time: 124168.838 ms > -------------------------------------------------------------------------= ------- >=20 > The index is defined as follows. >=20 > CREATE INDEX index_search_01 ON mdb.TBL_SHA USING btree (MS_CD, ETRYS); Actual rows =3D 3200000, rows removed by filter is ten times as much. It should use an index. > When I take the following sql statement, the index works fine and the que= ry is fast. >=20 > select COUNT(ET_CD) > from TBL_SHA > WHERE MS_CD =3D '009' > AND ETRYS =3D '000001' >=20 > The amount of data in the table is as follows. > TBL_SHA=C2=A0 =C2=A0 38700325 > TBL_INF=C2=A0 =C2=A0 35546 This looks very much like it is a problem with the data types. I see that you are using "character", which you shouldn't do. What I cannot see is if the columns are defined as "character" or whether y= ou bind the parameters as "character". Can you show us the table definition of "TB= L_SHA" and "TBL_INF"? Yours, Laurenz Albe