pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: How to improve the performance of my SQL query?
7+ messages / 1 participants
[nested] [flat]
* Re: How to improve the performance of my SQL query?
@ 2023-07-20 14:14 gzh <gzhcoder@126.com>
2023-07-21 01:43 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
0 siblings, 1 reply; 7+ messages in thread
From: gzh @ 2023-07-20 14:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
Thank you for your reply.
The information I provided is incorrect, please see my previous reply.
>What I cannot see is if the columns are defined as "character" or whether you bind
>the parameters as "character". Can you show us the table definition of "TBL_SHA"
>and "TBL_INF"?
For information security reasons, I can't provide the table definition, these columns are defined as "character".
At 2023-07-20 19:58:59, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
>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.
>>
>> 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);
>
>Actual rows = 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 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
>
>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 you bind
>the parameters as "character". Can you show us the table definition of "TBL_SHA"
>and "TBL_INF"?
>
>Yours,
>Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to improve the performance of my SQL query?
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
@ 2023-07-21 01:43 ` gzh <gzhcoder@126.com>
2023-07-24 09:54 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-26 07:46 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
0 siblings, 2 replies; 7+ messages in thread
From: gzh @ 2023-07-21 01:43 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
The definitions of the columns used in SQL are as follows.
TBL_SHA
ms_cd character(6) NOT NULL -- PRIMARY KEY
et_cd character(8)
etrys character(8)
TBL_INF
ms_cd character(6) NOT NULL -- PRIMARY KEY
ry_cd character(8) NOT NULL -- PRIMARY KEY
I made some modifications to the data, and I realized that I should not change the length of the data.
The actual data and its corresponding execution plan are shown below.
explain analyze
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = 'MLD009'
and TBL_SHA.ETRYS in
(select TBL_INF.RY_CD
from TBL_INF
WHERE TBL_INF.MS_CD = 'MLD009'
AND TBL_INF.RY_CD = '00000001'
)
----- 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 = 'MLD009'::bpchar) AND (RY_CD = '00000001'::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 = 'MLD009'::bpchar) AND (ETRYS = '00000001'::bpchar))
Rows Removed by Filter: 32000325
Planning Time: 0.162 ms
Execution Time: 124168.838 ms
At 2023-07-20 22:48:19, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
>On Thu, 2023-07-20 at 22:14 +0800, gzh wrote:
>> The information I provided is incorrect, please see my previous reply.
>
>My question remains: I would like to see the table definitions.
>
>Also, did you ANALYZE the tables?
>
>Yours,
>Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to improve the performance of my SQL query?
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-21 01:43 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
@ 2023-07-24 09:54 ` gzh <gzhcoder@126.com>
2023-07-25 03:11 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
1 sibling, 1 reply; 7+ messages in thread
From: gzh @ 2023-07-24 09:54 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
>Did you change any parameters that have an impact on query planning?
>You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
I added some parameters and re-executed the Execution Plan.
Except for the index not taking effect, I still don't know the reason why the index is not working.
Is it because there is too much data that meets the conditions?
EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON)
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = 'MLD009'
and TBL_SHA.ETRYS in
(select TBL_INF.RY_CD
from TBL_INF
WHERE TBL_INF.MS_CD = 'MLD009'
AND TBL_INF.RY_CD = '00000001'
)
----- Execution Plan -----
Limit (cost=2728633.22..2728633.23 rows=1 width=8) (actual time=128691.521..128717.677 rows=1 loops=1)
Output: (count(tbl_sha.et_cd))
Buffers: shared hit=58948 read=2112758
I/O Timings: read=357249.120
-> Aggregate (cost=2728633.22..2728633.23 rows=1 width=8) (actual time=128691.519..128717.674 rows=1 loops=1)
Output: count(tbl_sha.et_cd)
Buffers: shared hit=58948 read=2112758
I/O Timings: read=357249.120
-> Nested Loop (cost=1000.29..2722556.76 rows=2430587 width=9) (actual time=2.364..128350.279 rows=2613500 loops=1)
Output: tbl_sha.et_cd
Buffers: shared hit=58948 read=2112758
I/O Timings: read=357249.120
-> Index Only Scan using tbl_inf_pkc on mtpdb.tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=0.046..0.051 rows=1 loops=1)
Output: tbl_inf.ms_cd, tbl_inf.ry_cd
Index Cond: ((tbl_inf.ms_cd = 'MLD009'::bpchar) AND (tbl_inf.ry_cd = '00000001'::bpchar))
Heap Fetches: 1
Buffers: shared hit=4
-> Gather (cost=1000.00..2698242.58 rows=2430587 width=18) (actual time=2.315..127773.087 rows=2613500 loops=1)
Output: tbl_sha.et_cd, tbl_sha.etrys
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=58944 read=2112758
I/O Timings: read=357249.120
-> Parallel Seq Scan on mtpdb.tbl_sha (cost=0.00..2454183.88 rows=1012745 width=18) (actual time=952.728..127583.089 rows=871167 loops=3)
Output: tbl_sha.et_cd, tbl_sha.etrys
Filter: ((tbl_sha.ms_cd = 'MLD009'::bpchar) AND (tbl_sha.etrys = '00000001'::bpchar))
Rows Removed by Filter: 14062278
Buffers: shared hit=58944 read=2112758
I/O Timings: read=357249.120
Worker 0: actual time=1432.292..127762.181 rows=988036 loops=1
Buffers: shared hit=17875 read=706862
I/O Timings: read=119193.744
Worker 1: actual time=1425.878..127786.777 rows=992381 loops=1
Buffers: shared hit=19813 read=706359
I/O Timings: read=119386.899
Planning:
Buffers: shared hit=42
Planning Time: 1.024 ms
Execution Time: 128717.731 ms
At 2023-07-24 13:43:46, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
>On Fri, 2023-07-21 at 09:43 +0800, gzh wrote:
>> The definitions of the columns used in SQL are as follows.
>>
>> TBL_SHA
>>
>> ms_cd character(6) NOT NULL -- PRIMARY KEY
>> et_cd character(8)
>> etrys character(8)
>>
>> TBL_INF
>>
>> ms_cd character(6) NOT NULL -- PRIMARY KEY
>> ry_cd character(8) NOT NULL -- PRIMARY KEY
>>
>> I made some modifications to the data, and I realized that I should not change the length of the data.
>> The actual data and its corresponding execution plan are shown below.
>>
>> explain analyze
>> select COUNT(ET_CD)
>> from TBL_SHA
>> WHERE TBL_SHA.MS_CD = 'MLD009'
>> and TBL_SHA.ETRYS in
>> (select TBL_INF.RY_CD
>> from TBL_INF
>> WHERE TBL_INF.MS_CD = 'MLD009'
>> AND TBL_INF.RY_CD = '00000001'
>> )
>> ----- 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 = 'MLD009'::bpchar) AND (RY_CD = '00000001'::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 = 'MLD009'::bpchar) AND (ETRYS = '00000001'::bpchar))
>> Rows Removed by Filter: 32000325
>> Planning Time: 0.162 ms
>> Execution Time: 124168.838 ms
>
>Thanks. That should definitely use a b-tree index defined on (ms_cd, etrsys).
>
>Did you change any parameters that have an impact on query planning?
>You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
>
>Yours,
>Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to improve the performance of my SQL query?
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-21 01:43 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-24 09:54 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
@ 2023-07-25 03:11 ` gzh <gzhcoder@126.com>
0 siblings, 0 replies; 7+ messages in thread
From: gzh @ 2023-07-25 03:11 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
>I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ...
>Or do you have a version that is too old for SETTINGS?
Sorry. Please refer to the following execution plan.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = 'MLD009'
and TBL_SHA.ETRYS in
(select TBL_INF.RY_CD
from TBL_INF
WHERE TBL_INF.MS_CD = 'MLD009'
AND TBL_INF.RY_CD = '00000001'
)
----- Execution Plan -----
Buffers: shared hit=13 read=2171693
I/O Timings: read=365863.877
-> Aggregate (cost=2728633.22..2728633.23 rows=1 width=8) (actual time=131595.624..131599.529 rows=1 loops=1)
Buffers: shared hit=13 read=2171693
I/O Timings: read=365863.877
-> Nested Loop (cost=1000.29..2722556.76 rows=2430587 width=9) (actual time=2.341..131256.445 rows=2513500 loops=1)
Buffers: shared hit=13 read=2171693
I/O Timings: read=365863.877
-> Index Only Scan using tbl_inf_pkc on tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=1.471..1.476 rows=1 loops=1)
Index Cond: ((ms_cd = 'MLD009'::bpchar) AND (ry_cd = '00000001'::bpchar))
Heap Fetches: 1
Buffers: shared hit=2 read=2
I/O Timings: read=1.412
-> Gather (cost=1000.00..2698242.58 rows=2430587 width=18) (actual time=0.866..130696.440 rows=2513500 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=11 read=2171691
I/O Timings: read=365862.464
-> Parallel Seq Scan on tbl_sha (cost=0.00..2454183.88 rows=1012745 width=18) (actual time=0.215..130476.981 rows=837833 loops=3)
Filter: ((ms_cd = 'MLD009'::bpchar) AND (etrys = '00000001'::bpchar))
Rows Removed by Filter: 13728945
Buffers: shared hit=11 read=2171691
I/O Timings: read=365862.464
Settings: effective_cache_size = '1886088kB', jit = 'off', search_path = '"$user", mdb'
Planning:
Buffers: shared hit=167 read=7
I/O Timings: read=2.735
Planning Time: 3.733 ms
Execution Time: 131599.594 ms
At 2023-07-24 23:58:50, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
>On Mon, 2023-07-24 at 17:54 +0800, gzh wrote:
>> EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON)
>
>I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ...
>Or do you have a version that is too old for SETTINGS?
>
>One other idea: check if the index is INVALID (this will
>be visible if you run "\d tablenane" in "psql").
>Invalid indexes won't be used.
>
>Yours,
>Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to improve the performance of my SQL query?
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-21 01:43 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
@ 2023-07-26 07:46 ` gzh <gzhcoder@126.com>
2023-07-27 08:09 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
1 sibling, 1 reply; 7+ messages in thread
From: gzh @ 2023-07-26 07:46 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
Thank you very much for taking the time to reply to my question.
>You might want to check your description of the table definitions.
>Going by the above EXPLAIN ANALYZE output, it very much does not look
>like ms_cd is the primary key of TBL_SHA. If it is then it's very
>weird that you have 3200000 rows for MS_CD = 'MLD009'. You have some
>data corruption if that's the case. I suspect you've just not
>accurately described the table definition, however.
The primary key of the SHA table has six fields, and ms_cd is just one of them.
I'm sorry, I didn't make that clear.
>Try executing the query after having done:
>
>SET enable_seqscan TO off;
>
>What plan does it use now?
>
>Is that plan faster or slower than the seq scan plan?
There's improvement, but it's still quite slow.
QUERY PLAN (enable_seqscan=on)
Limit (cost=2693516.87..2693516.88 rows=1 width=8) (actual time=167089.822..167183.058 rows=1 loops=1)
-> Aggregate (cost=2693516.87..2693516.88 rows=1 width=8) (actual time=167089.820..167183.056 rows=1 loops=1)
-> Nested Loop (cost=1000.29..2688558.85 rows=1983209 width=9) (actual time=43544.753..166906.304 rows=2413500 loops=1)
-> Index Only Scan using tbl_inf_pkc on tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=1.034..1.038 rows=1 loops=1)
Index Cond: ((ms_cd = 'MLD009'::bpchar) AND (ry_cd = '00000001'::bpchar))
Heap Fetches: 1
-> Gather (cost=1000.00..2668718.45 rows=1983209 width=18) (actual time=43543.714..166447.333 rows=2413500 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_sha (cost=0.00..2469397.55 rows=826337 width=18) (actual time=43537.056..166225.162 rows=804500 loops=3)
Filter: ((ms_cd = 'MLD009'::bpchar) AND (etrys = '00000001'::bpchar))
Rows Removed by Filter: 15362328
Planning Time: 2.942 ms
Execution Time: 167183.133 ms
SET enable_seqscan TO off;
QUERY PLAN (enable_seqscan=off)
Limit (cost=2880973.06..2880973.07 rows=1 width=8) (actual time=22295.419..22320.102 rows=1 loops=1)
-> Aggregate (cost=2880973.06..2880973.07 rows=1 width=8) (actual time=22295.418..22320.100 rows=1 loops=1)
-> Nested Loop (cost=93112.74..2876169.16 rows=1921561 width=9) (actual time=265.880..22000.432 rows=2413500 loops=1)
-> Index Only Scan using tbl_inf_pkc on tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=0.013..0.020 rows=1 loops=1)
Index Cond: ((ms_cd = 'MLD009'::bpchar) AND (ry_cd = '00000001'::bpchar))
Heap Fetches: 1
-> Gather (cost=93112.45..2856945.24 rows=1921561 width=18) (actual time=265.864..21535.325 rows=2413500 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on tbl_sha (cost=92112.45..2663789.14 rows=800650 width=18) (actual time=260.540..21442.169 rows=804500 loops=3)
Recheck Cond: (ms_cd = 'MLD009'::bpchar)
Rows Removed by Index Recheck: 49
Filter: (etrys = '00000001'::bpchar)
Rows Removed by Filter: 295500
Heap Blocks: exact=13788 lossy=10565
-> Bitmap Index Scan on index_search_04_mscd_cdate (cost=0.00..91632.06 rows=3402599 width=0) (actual time=249.718..249.718 rows=3300000 loops=1)
Index Cond: (ms_cd = 'MLD009'::bpchar)
Planning Time: 0.670 ms
Execution Time: 22320.153 ms
At 2023-07-25 21:04:16, "David Rowley" <dgrowleyml@gmail.com> wrote:
>On Fri, 21 Jul 2023 at 13:44, gzh <gzhcoder@126.com> wrote:
>>
>> The definitions of the columns used in SQL are as follows.
>>
>> TBL_SHA
>> ms_cd character(6) NOT NULL -- PRIMARY KEY
>> et_cd character(8)
>> etrys character(8)
>
>> explain analyze
>> select COUNT(ET_CD)
>> from TBL_SHA
>> WHERE TBL_SHA.MS_CD = 'MLD009'
>> and TBL_SHA.ETRYS in
>> (select TBL_INF.RY_CD
>> from TBL_INF
>> WHERE TBL_INF.MS_CD = 'MLD009'
>> AND TBL_INF.RY_CD = '00000001'
>> )
>> ----- 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 = 'MLD009'::bpchar) AND (RY_CD = '00000001'::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 = 'MLD009'::bpchar) AND (ETRYS = '00000001'::bpchar))
>> Rows Removed by Filter: 32000325
>> Planning Time: 0.162 ms
>> Execution Time: 124168.838 ms
>
>You might want to check your description of the table definitions.
>Going by the above EXPLAIN ANALYZE output, it very much does not look
>like ms_cd is the primary key of TBL_SHA. If it is then it's very
>weird that you have 3200000 rows for MS_CD = 'MLD009'. You have some
>data corruption if that's the case. I suspect you've just not
>accurately described the table definition, however.
>
>David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to improve the performance of my SQL query?
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-21 01:43 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-26 07:46 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
@ 2023-07-27 08:09 ` gzh <gzhcoder@126.com>
2023-07-28 11:50 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
0 siblings, 1 reply; 7+ messages in thread
From: gzh @ 2023-07-27 08:09 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
Thank you for your suggestion.
The database is hosted on AWS RDS, with the instance having vCPU=2 and RAM=4GB.
Could the low performance be due to the low configuration of AWS RDS?
We are considering trying a higher configuration instance.
At 2023-07-27 11:36:20, "David Rowley" <dgrowleyml@gmail.com> wrote:
>On Wed, 26 Jul 2023 at 19:46, gzh <gzhcoder@126.com> wrote:
>> QUERY PLAN (enable_seqscan=on)
>
>> Execution Time: 167183.133 ms
>
>> QUERY PLAN (enable_seqscan=off)
>
>> Execution Time: 22320.153 ms
>
>effective_cache_size and random_page_cost are the settings you should
>be adjusting to coax the planner into using the index.
>
>A rule of thumb for effective_cache_size would be to set it to about
>75% of RAM. There are certainly cases where lower would make more
>sense, certainly, 75% will make more sense than the default 4GB value
>in the majority of cases.
>
>For random_page_cost, the default of 4.0 has been the default since
>HDDs were common. SSDs are common now and, comparatively to sequential
>I/O, their random I/O is faster than that of an HDD, so you may get
>better results by lowering random_page_cost.
>
>David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to improve the performance of my SQL query?
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-21 01:43 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-26 07:46 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-27 08:09 ` Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
@ 2023-07-28 11:50 ` gzh <gzhcoder@126.com>
0 siblings, 0 replies; 7+ messages in thread
From: gzh @ 2023-07-28 11:50 UTC (permalink / raw)
To: Charly <carlbsb@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
Thank you very much for taking the time to reply to my question.
> The problem is none of the explains you sent match with the description above. The last one when you forced the optimizer to go with index scan (SET enable_seqscan TO off;) the chosen index seems to be one composed by ms_cd and cd_ate (following your standard of 2 characters column name). There may have a couple of explanations to this:
> - One is that the index may not be exactly the same as described above;
The primary key of the tbl_sha table consists of several fields, and ms_cd is just one of them. I just explained the definitions of the fields used in the query SQL.
> - Another one is the order in the index. Because you have a composed index the order of the columns in the index matters, and it seems the order is (ms_cd, et_cd, etrys). I wonder if you could recreate this index with the following order: (ms_cd, etrys, et_cd) and run the same query;
The index of TBL_SHA table is defined as follows.
CREATE INDEX index_search_01 ON mdb.TBL_SHA USING btree (MS_CD, ETRYS)
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)
> You can also try to trick the optimizer, for example, what is the result (and explain) of the below query?
>
> WITH tbi (ry_cd) AS (
> SELECT tbl_inf.ry_cd
> FROM tbl_inf tbi
> WHERE tbi.ms_cd = 'MLD009'
> AND tbl_inf.ry_cd = '00000001'
> ) SELECT COUNT(et_cd) FROM tbl_sha tbs
> JOIN tbi ON tbi.ry_cd = tbs .etrys
> WHERE tbs .ms_cd = 'MLD009';
The SQL execution encountered an error, so I made some modifications.
Please refer to the execution plan.
WHERE tbi.ms_cd = 'MLD009'
→
WHERE tbl_inf.ms_cd = 'MLD009'
QUERY PLAN
Limit (cost=2668811.76..2668811.77 rows=1 width=8) (actual time=133555.074..133557.729 rows=1 loops=1)
-> Aggregate (cost=2668811.76..2668811.77 rows=1 width=8) (actual time=133555.072..133557.726 rows=1 loops=1)
-> Nested Loop (cost=1000.29..2664512.83 rows=1719572 width=9) (actual time=29657.638..133341.053 rows=2113500 loops=1)
-> Index Only Scan using tbl_inf_pkc on tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=1.316..1.321 rows=1 loops=1)
Index Cond: ((ms_cd = 'MLD009'::bpchar) AND (ry_cd = '00000001'::bpchar))
Heap Fetches: 1
-> Gather (cost=1000.00..2647308.80 rows=1719572 width=18) (actual time=29656.318..132969.910 rows=2113500 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on tbl_sha tbs (cost=0.00..2474351.60 rows=716488 width=18) (actual time=29654.184..132876.292 rows=704500 loops=3)
Filter: ((ms_cd = 'MLD009'::bpchar) AND (etrys = '00000001'::bpchar))
Rows Removed by Filter: 14678996
Planning Time: 0.164 ms
Execution Time: 133557.767 ms
> Well, adding more resources tends to improve performance, but it's usually not linear and the improvement may not be as large as you want for the extra price you are paying. I would first try to understand the performance problem because using the "add more resources" approach may just delay the problem and it tends to get worse with time as the dataset increases.
I strongly agree with your viewpoint, but I currently don't have a solution in mind for the problem.
At 2023-07-28 04:38:39, "Charly" <carlbsb@gmail.com> wrote:
Hi "gzh",
Based on the info you provided I'm assuming you are trying to use the TBL_SHA primary key to do an index-only scan as in you mentioned above you have:
> TBL_SHA
> ms_cd character(6) NOT NULL -- PRIMARY KEY
> et_cd character(8)
> etrys character(8)
Assuming a composed index here by the 3 columns.
> TBL_INF
> ms_cd character(6) NOT NULL -- PRIMARY KEY
> ry_cd character(8) NOT NULL -- PRIMARY KEY
Here it's more clear that there is a composed index based on those 2 columns.
The problem is none of the explains you sent match with the description above. The last one when you forced the optimizer to go with index scan (SET enable_seqscan TO off;) the chosen index seems to be one composed by ms_cd and cd_ate (following your standard of 2 characters column name). There may have a couple of explanations to this:
- One is that the index may not be exactly the same as described above;
- Another one is the order in the index. Because you have a composed index the order of the columns in the index matters, and it seems the order is (ms_cd, et_cd, etrys). I wonder if you could recreate this index with the following order: (ms_cd, etrys, et_cd) and run the same query;
There may be other problems happening there, but those are the ones I see more evident from the description of the problem.
Giving a closer look to your query I really didn't understand the reasoning to have that subselect as it seems only to validate the two relations have common items or to validate a parent relationship, in this case you can probably use a join to get the same result:
SELECT COUNT(et_cd) FROM tbl_sha tbs
JOIN tbi ON tbi.ry_cd = tbs.etrys AND tbi.ms_cd = tbi.ms_cd
WHERE tbi.ms_cd = 'MLD009'
AND tbl_inf.ry_cd = '00000001';
You can also try to trick the optimizer, for example, what is the result (and explain) of the below query?
WITH tbi (ry_cd) AS (
SELECT tbl_inf.ry_cd
FROM tbl_inf tbi
WHERE tbi.ms_cd = 'MLD009'
AND tbl_inf.ry_cd = '00000001'
) SELECT COUNT(et_cd) FROM tbl_sha tbs
JOIN tbi ON tbi.ry_cd = tbs .etrys
WHERE tbs .ms_cd = 'MLD009';
You can alternatively try the CTE using the both columns in the JOIN clause.
On Thu, 27 Jul 2023 at 05:10, gzh <gzhcoder@126.com> wrote:
Thank you for your suggestion.
The database is hosted on AWS RDS, with the instance having vCPU=2 and RAM=4GB.
Could the low performance be due to the low configuration of AWS RDS?
We are considering trying a higher configuration instance.
Well, adding more resources tends to improve performance, but it's usually not linear and the improvement may not be as large as you want for the extra price you are paying. I would first try to understand the performance problem because using the "add more resources" approach may just delay the problem and it tends to get worse with time as the dataset increases.
I hope it helps.
--
Regards,
Charly Batista
Shanghai, China
carlbsb@gmail.com
https://www.linkedin.com/in/charlybatista/
Linux user #391083
“If you have an apple and I have an apple and we exchange these apples then you and I will still each have one apple. But if you have an idea and I have an idea and we exchange these ideas, then each of us will have two ideas."
George Bernard Shaw (1856-1950)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-07-28 11:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-20 14:14 Re: How to improve the performance of my SQL query? gzh <gzhcoder@126.com>
2023-07-21 01:43 ` gzh <gzhcoder@126.com>
2023-07-24 09:54 ` gzh <gzhcoder@126.com>
2023-07-25 03:11 ` gzh <gzhcoder@126.com>
2023-07-26 07:46 ` gzh <gzhcoder@126.com>
2023-07-27 08:09 ` gzh <gzhcoder@126.com>
2023-07-28 11:50 ` gzh <gzhcoder@126.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox