Re:Re: How to improve the performance of my SQL query？ - Mailing list pgsql-general

Thank you very much for taking the time to reply to my question. 

Sorry, I provided incorrect information.

The index also does not work in the following query statement.

> select COUNT(ET_CD)

> from TBL_SHA

> WHERE MS_CD = '009'

> AND ETRYS = '000001'

QUERY PLAN

Limit  (cost=2419643.47..2419643.48 rows=1 width=8) (actual time=128667.439..128668.250 rows=1 loops=1)

  ->  Finalize Aggregate  (cost=2419643.47..2419643.48 rows=1 width=8) (actual time=128667.437..128668.246 rows=1 loops=1)

        ->  Gather  (cost=2419643.25..2419643.46 rows=2 width=8) (actual time=128664.108..128668.233 rows=3 loops=1)

              Workers Planned: 2

              Workers Launched: 2

              ->  Partial Aggregate  (cost=2418643.25..2418643.26 rows=1 width=8) (actual time=128655.256..128655.258 rows=1 loops=3)

                    ->  Parallel Seq Scan on TBL_SHA  (cost=0.00..2415548.85 rows=1237762 width=9) (actual time=75357.455..128531.615 rows=1066667 loops=3)

                          Filter: ((MS_CD = '009'::bpchar) AND (ETRYS = '000001'::bpchar))

                          Rows Removed by Filter: 11833442

Planning Time: 0.118 ms

Execution Time: 128668.290 ms

The TBL_SHA table has another index, as shown below.

CREATE INDEX index_search_02 ON mdb.TBL_SHA USING btree (ET_CD, ETRYS)

CREATE INDEX index_search_03 ON mdb.TBL_SHA USING btree (MS_CD, ET_DAY, BK_CD, FR_CD, RM_CD)

When I take the following query statement, the result is returned quickly.

Why does index_search_01 always not work?

explain analyze

select TO_CHAR(MAX(TBL_SHA.ET_DAY),'YYYYMMDD') AS ET_DAY

from TBL_SHA

WHERE MS_CD = '008'

AND ET_CD = '000003'

QUERY PLAN

Limit  (cost=4.11..4.13 rows=1 width=32) (actual time=0.043..0.044 rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.56..4.11 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)

          ->  Index Scan Backward using index_search_01 on TBL_SHA  (cost=0.56..10836962.40 rows=3054052 width=8) (actual time=0.032..0.032 rows=1 loops=1)

                Index Cond: ((MS_CD = '008'::bpchar) AND (ent_day IS NOT NULL))

                Filter: (ET_CD = '000003'::bpchar)

  ->  Result  (cost=4.11..4.13 rows=1 width=32) (actual time=0.042..0.042 rows=1 loops=1)

Planning Time: 0.228 ms

Execution Time: 0.070 ms

At 2023-07-20 16:07:15, "Erik Wienhold" <ewie@ewie.name> wrote:
>> On 20/07/2023 09:09 CEST gzh <gzhcoder@126.com> 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