From: gzh <gzhcoder@126.com>
To: jian he <jian.universality@gmail.com>
Cc: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-general@lists.postgresql.org
Subject: Re:How to improve the performance of my SQL query?
Date: Fri, 21 Jul 2023 10:02:51 +0800 (CST)
Message-ID: <76afcab2.1762.189762f7d87.Coremail.gzhcoder@126.com> (raw)
In-Reply-To: <CACJufxHxrPgj1D-+qAofYr100G5hywghPBmmx4xddY_mH12BFQ@mail.gmail.com>
References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com>
<1360960490.395085.1689840435026@office.mailbox.org>
<1628bf9c.6d72.1897315f05f.Coremail.gzhcoder@126.com>
<CACJufxHxrPgj1D-+qAofYr100G5hywghPBmmx4xddY_mH12BFQ@mail.gmail.com>
>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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gzhcoder@126.com, jian.universality@gmail.com, ewie@ewie.name
Subject: Re: How to improve the performance of my SQL query?
In-Reply-To: <76afcab2.1762.189762f7d87.Coremail.gzhcoder@126.com>
References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> <1360960490.395085.1689840435026@office.mailbox.org> <1628bf9c.6d72.1897315f05f.Coremail.gzhcoder@126.com> <CACJufxHxrPgj1D-+qAofYr100G5hywghPBmmx4xddY_mH12BFQ@mail.gmail.com> <76afcab2.1762.189762f7d87.Coremail.gzhcoder@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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