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

From gzh
Subject Re:How to improve the performance of my SQL query?
Date
Msg-id 76afcab2.1762.189762f7d87.Coremail.gzhcoder@126.com
Whole thread Raw
In response to Re: Re: How to improve the performance of my SQL query?  (jian he <jian.universality@gmail.com>)
List pgsql-general
>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)

pgsql-general by date:

Previous
From: gzh
Date:
Subject: Re: How to improve the performance of my SQL query?
Next
From: Ram Pratap Maurya
Date:
Subject: Server unable to UP after restore