> On 29 Jul 2023, at 10:59, Peter J. Holzer 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=804
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 =
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_
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
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" wrote:
>
On Wed, 26 Jul 2023 at 19:46, gzh 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
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 32000
On Fri, 21 Jul 2023 at 13:44, 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)
> explain analyze
> select COUNT(ET_CD)
> from TBL_SHA
> WHERE TBL_SHA.MS_CD = 'MLD009'
On Thu, 20 Jul 2023 at 23:36, gzh 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'
> >
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_pat
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 = '0001';
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
>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
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 ta
On Mon, Jul 24, 2023 at 5:54 PM gzh 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 effe
>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 w
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 characte
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 modi
On Thu, Jul 20, 2023 at 7:36 PM gzh 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 = '00
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
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 informat
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
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 i
> On 20/07/2023 09:09 CEST 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
>
23 matches
Mail list logo
