From: | gzh <gzhcoder(at)126(dot)com> |
---|---|
To: | "David Rowley" <dgrowleyml(at)gmail(dot)com> |
Cc: | "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to improve the performance of my SQL query? |
Date: | 2023-07-27 08:09:27 |
Message-ID: | 5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)gmail(dot)com> wrote:
>On Wed, 26 Jul 2023 at 19:46, gzh <gzhcoder(at)126(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro Horiguchi | 2023-07-27 08:31:56 | Re: Bogus temp file reporting? |
Previous Message | kg.postgresql | 2023-07-27 07:30:41 | Bogus temp file reporting? |