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" <dgrowle...@gmail.com> wrote: >On Wed, 26 Jul 2023 at 19:46, gzh <gzhco...@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
