pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed Re: How to improve the performance of my SQL query?
17+ messages / 8 participants
[nested] [flat]
* Re: How to improve the performance of my SQL query?
@ 2023-07-20 08:07 Erik Wienhold <ewie@ewie.name>
2 siblings, 2 replies; 17+ messages in thread
From: Erik Wienhold @ 2023-07-20 08:07 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; pgsql-general@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-20 09:44 Peter J. Holzer <hjp-pgsql@hjp.at>
2 siblings, 0 replies; 17+ messages in thread
From: Peter J. Holzer @ 2023-07-20 09:44 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-07-20 15:09:22 +0800, gzh wrote:
> Hi everyone,
>
>
> 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 -----
[...]
> -> 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
[...]
> --------------------------------------------------------------------------------
>
>
> 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'
What's the plan for that query?
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-20 11:58 Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 1 reply; 17+ messages in thread
From: Laurenz Albe @ 2023-07-20 11:58 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; pgsql-general@lists.postgresql.org
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] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-20 14:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 17+ messages in thread
From: Laurenz Albe @ 2023-07-20 14:48 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
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] 17+ messages in thread
* Re: Re: How to improve the performance of my SQL query?
@ 2023-07-20 15:20 jian he <jian.universality@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
1 sibling, 1 reply; 17+ messages in thread
From: jian he @ 2023-07-20 15:20 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
On Thu, Jul 20, 2023 at 7:36 PM gzh <gzhcoder@126.com> wrote:
>
>
> 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)
>
> Rows Removed by Filter: 11833442
select (38700325 - 11833442) /38700325.0;
is 0.69 approx.
So I think it says around 69% of rows satisfy the query condition.
but I am not sure in the following 2 cases, whether the actual rows
are noisy or not. I can not find the doc explaining it.
> Partial Aggregate (actual time=128655.256..128655.258 rows=1 loops=3)
> Finalize Aggregate (actual time=128667.437..128668.246 rows=1 loops=1)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re:How to improve the performance of my SQL query?
@ 2023-07-21 02:02 gzh <gzhcoder@126.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: gzh @ 2023-07-21 02:02 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
>select (38700325 - 11833442) /38700325.0;
>is 0.69 approx.
>So I think it says around 69% of rows satisfy the query condition.Thank you for your reply. I have learned a lot from it.
At 2023-07-20 23:20:16, "jian he" <jian.universality@gmail.com> wrote:
>On Thu, Jul 20, 2023 at 7:36 PM gzh <gzhcoder@126.com> wrote:
>>
>>
>> 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)
>>
>
>> Rows Removed by Filter: 11833442
>select (38700325 - 11833442) /38700325.0;
>is 0.69 approx.
>So I think it says around 69% of rows satisfy the query condition.
>
>but I am not sure in the following 2 cases, whether the actual rows
>are noisy or not. I can not find the doc explaining it.
>> Partial Aggregate (actual time=128655.256..128655.258 rows=1 loops=3)
>> Finalize Aggregate (actual time=128667.437..128668.246 rows=1 loops=1)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-24 05:43 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 2 replies; 17+ messages in thread
From: Laurenz Albe @ 2023-07-24 05:43 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
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] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-24 14:42 jian he <jian.universality@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 17+ messages in thread
From: jian he @ 2023-07-24 14:42 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
On Mon, Jul 24, 2023 at 5:54 PM gzh <gzhcoder@126.com> wrote:
>
> >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
>
>
I think the whole query can just:
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '00000001';
> 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'
> )
if subquery after IN clause part, no rows returned then the whole
query would return zero row.
if many duplicates rows returned, then there is no point of evaluate
something like {1 in (1,1,1,1,1,1,)}
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-24 15:58 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 17+ messages in thread
From: Laurenz Albe @ 2023-07-24 15:58 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
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] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-25 03:58 gzh <gzhcoder@126.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: gzh @ 2023-07-25 03:58 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
Thank you for your reply.
>I think the whole query can just:
>select COUNT(ET_CD)
>from TBL_SHA
>WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '00000001';
This is related to the business logic.
>if many duplicates rows returned, then there is no point of evaluate
>something like {1 in (1,1,1,1,1,1,)}
Because the primary key of the 'tbl_inf' table only consists of 'ms_cd' and 'ry_cd' columns, the subquery will not return duplicate rows.
At 2023-07-24 22:42:01, "jian he" <jian.universality@gmail.com> wrote:
>On Mon, Jul 24, 2023 at 5:54 PM gzh <gzhcoder@126.com> wrote:
>>
>> >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
>>
>>
>
>I think the whole query can just:
>select COUNT(ET_CD)
>from TBL_SHA
>WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '00000001';
>
>> 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'
>> )
>
>if subquery after IN clause part, no rows returned then the whole
>query would return zero row.
>if many duplicates rows returned, then there is no point of evaluate
>something like {1 in (1,1,1,1,1,1,)}
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-25 05:56 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 17+ messages in thread
From: Laurenz Albe @ 2023-07-25 05:56 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: pgsql-general@lists.postgresql.org
On Tue, 2023-07-25 at 11:11 +0800, gzh wrote:
> > 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.
>
> [...]
> Settings: effective_cache_size = '1886088kB', jit = 'off', search_path = '"$user", mdb'
> [...]
No unusual settings. So that theory is dead.
> > 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.
Did you check that?
After that, I am out of ideas.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Re: How to improve the performance of my SQL query?
@ 2023-07-25 12:58 David Rowley <dgrowleyml@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 17+ messages in thread
From: David Rowley @ 2023-07-25 12:58 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org
On Thu, 20 Jul 2023 at 23:36, gzh <gzhcoder@126.com> wrote:
>
>
> 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?
The method to access the table is decided by the query planner based
on costs. The costs are driven off the row estimates which are driven
from table statistics. If the table statistics, for example say that
99% of rows have MS_CD = '009', then scanning an index on MS_CD is
unlikely to be a good idea as that would likely require random access
to the heap. It's likely better to perform a table scan and then just
filter out the 1% of rows that don't match.
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?
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-25 13:04 David Rowley <dgrowleyml@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 2 replies; 17+ messages in thread
From: David Rowley @ 2023-07-25 13:04 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
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] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-27 03:36 David Rowley <dgrowleyml@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: David Rowley @ 2023-07-27 03:36 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
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] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-27 20:38 Charly <carlbsb@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Charly @ 2023-07-27 20:38 UTC (permalink / raw)
To: gzh <gzhcoder@126.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org
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 <carlbsb@gmail.com> *
*https://www.linkedin.com/in/charlybatista/
<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] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-29 08:59 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Peter J. Holzer @ 2023-07-29 08:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-07-26 15:46:16 +0800, gzh wrote:
> SET enable_seqscan TO off;
[...]
> -> 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)
So now it's using index_search_04_mscd_cdate which contains only ms_cd
(and - judging from the name, other fields not relevant to this query),
but it still doesn't use index_search_01 which would fit the query
exactly. I can understand that Postgres prefers a sequential scan over
an index scan (the number of matching rows is about 10% of the total
table size which is a lot), but why would it prefer a less specific
index to a more specific one?
Can you get Postgres to use that index at all?
Find a combination of ms_cd and etrys which doesn't cover millions of
rows and try that.
Also try lowering random_page_cost.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: How to improve the performance of my SQL query?
@ 2023-07-29 09:37 Alban Hertroys <haramrae@gmail.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 17+ messages in thread
From: Alban Hertroys @ 2023-07-29 09:37 UTC (permalink / raw)
To: Peter J. Holzer <hjp-pgsql@hjp.at>; +Cc: pgsql-general@lists.postgresql.org
> On 29 Jul 2023, at 10:59, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2023-07-26 15:46:16 +0800, gzh wrote:
>> SET enable_seqscan TO off;
> [...]
>> -> 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)
>
> So now it's using index_search_04_mscd_cdate which contains only ms_cd
> (and - judging from the name, other fields not relevant to this query),
> but it still doesn't use index_search_01 which would fit the query
> exactly. I can understand that Postgres prefers a sequential scan over
> an index scan (the number of matching rows is about 10% of the total
> table size which is a lot), but why would it prefer a less specific
> index to a more specific one?
>
> Can you get Postgres to use that index at all?
>
> Find a combination of ms_cd and etrys which doesn't cover millions of
> rows and try that.
>
> Also try lowering random_page_cost.
Wasn’t this an RDS server with just 4GB of memory?
How large are those multi-column indices? Perhaps they don’t (all) fit into available cache memory and the server decided to use the one that it had cached?
I’m frankly not at all certain how the server would behave around such resource shortage situations, but I suppose loading an uncached index into cache could get a higher cost than using a less optimal (costlier) index that’s already cached.
Regarding lowering random_page_cost; If your index files are on SSD storage, lowering that sufficiently (to a realistic value) could then sufficiently lower the cost of loading that uncached index into memory, evicting the index it was using in above plan to make room (unless other active sessions are using it).
Alban Hertroys
--
There is always an exception to always.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2023-07-29 09:37 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-20 08:07 ` Erik Wienhold <ewie@ewie.name>
2023-07-20 15:20 ` jian he <jian.universality@gmail.com>
2023-07-21 02:02 ` gzh <gzhcoder@126.com>
2023-07-25 12:58 ` David Rowley <dgrowleyml@gmail.com>
2023-07-20 09:44 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-07-20 11:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-20 14:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-24 05:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-24 14:42 ` jian he <jian.universality@gmail.com>
2023-07-25 03:58 ` gzh <gzhcoder@126.com>
2023-07-24 15:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-25 05:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-07-25 13:04 ` David Rowley <dgrowleyml@gmail.com>
2023-07-27 03:36 ` David Rowley <dgrowleyml@gmail.com>
2023-07-27 20:38 ` Charly <carlbsb@gmail.com>
2023-07-29 08:59 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-07-29 09:37 ` Alban Hertroys <haramrae@gmail.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