pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: gzh <gzhcoder@126.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: How to improve the performance of my SQL query?
Date: Thu, 27 Jul 2023 16:09:27 +0800 (CST)
Message-ID: <5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com> (raw)
In-Reply-To: <CAApHDvq08k_iJb8sCCAVPdV8A9JL0hEWFvs=3ryYsx3dApVOQQ@mail.gmail.com>
References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com>
	<369ee851ef59bc84190f1631219b5a0c8f7907e9.camel@cybertec.at>
	<1937d056.71e9.18973a752d0.Coremail.gzhcoder@126.com>
	<ea3893a955f4ae396efb05260bfab056f027fcf9.camel@cybertec.at>
	<6f3fd2f9.12b3.189761e1ccc.Coremail.gzhcoder@126.com>
	<CAApHDvpEm_V-eGmr44BJrT9=62cE7M+2wSYUSLquL4onp3bdVA@mail.gmail.com>
	<35cecf64.4a72.1899129b4b7.Coremail.gzhcoder@126.com>
	<CAApHDvq08k_iJb8sCCAVPdV8A9JL0hEWFvs=3ryYsx3dApVOQQ@mail.gmail.com>

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


view thread (7+ messages)  latest in thread

Message-ID: <5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com>
Permalink:  ../5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com/
Also on:    postgresql.org/message-id/5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: gzhcoder@126.com, dgrowleyml@gmail.com, laurenz.albe@cybertec.at
  Subject: Re: How to improve the performance of my SQL query?
  In-Reply-To: <5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com>
  References: <6212ea1b.425b.1897221c3cc.Coremail.gzhcoder@126.com> <369ee851ef59bc84190f1631219b5a0c8f7907e9.camel@cybertec.at> <1937d056.71e9.18973a752d0.Coremail.gzhcoder@126.com> <ea3893a955f4ae396efb05260bfab056f027fcf9.camel@cybertec.at> <6f3fd2f9.12b3.189761e1ccc.Coremail.gzhcoder@126.com> <CAApHDvpEm_V-eGmr44BJrT9=62cE7M+2wSYUSLquL4onp3bdVA@mail.gmail.com> <35cecf64.4a72.1899129b4b7.Coremail.gzhcoder@126.com> <CAApHDvq08k_iJb8sCCAVPdV8A9JL0hEWFvs=3ryYsx3dApVOQQ@mail.gmail.com> <5901a5a9.536c.18996654820.Coremail.gzhcoder@126.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox