From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | gzh <gzhcoder(at)126(dot)com> |
Cc: | Erik Wienhold <ewie(at)ewie(dot)name>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Re: How to improve the performance of my SQL query? |
Date: | 2023-07-20 15:20:16 |
Message-ID: | CACJufxHxrPgj1D-+qAofYr100G5hywghPBmmx4xddY_mH12BFQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jul 20, 2023 at 7:36 PM gzh <gzhcoder(at)126(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-07-20 15:21:55 | Re: My 1st JDBC and PostgreSQL |
Previous Message | Adrian Klaver | 2023-07-20 15:18:32 | Re: My 1st JDBC and PostgreSQL |