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 5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com
Whole thread Raw
In response to Re: How to improve the performance of my SQL query?  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: How to improve the performance of my SQL query?
List pgsql-general
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" <dgrowleyml@gmail.com> wrote: >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

pgsql-general by date:

Previous
From: kg.postgresql@olympiakos.com
Date:
Subject: Bogus temp file reporting?
Next
From: Kyotaro Horiguchi
Date:
Subject: Re: Bogus temp file reporting?