hi, I have an RDS instance with 2GB of RAM, 1 CPU, instance class - t2.small. If you need any more info please let me know. and as you shared I need to tweak random_page_cost/seq_page_cost/effective_cache_size So please suggest which parameter value I need to increase or decrease as I am known well with these parameters. Regards. On 6/15/21, Jehan-Guillaume de Rorthais <jgdr@xxxxxxxxxx> wrote: > On Tue, 15 Jun 2021 16:12:11 +0530 > Atul Kumar <akumar14871@xxxxxxxxx> wrote: > >> Hi, >> >> I have postgres 10 running on RDS instance. >> >> I have query below: > [...] >> >> So my doubt is initially when I run this query it takes around 42 >> seconds to complete but later after few minutes it completes in 2-3 >> seconds. >> >> I tried to create indexes on table for columns score & "updatedAt" >> DESC seperately but found no proper satisfied solution. >> >> So please help me telling what I am exactly missing here ? > > The worst part of your plan is the Bitmap Heap Scan, where the plan is > actually > fetching the rows from the table. The bitmap index scan and sort are fast. > There's not much to do about them. > > This query need to fetch 3882 rows from your table. So either the fetching > part > of the plan is really, really slow (IO/CPU bound), or the simple filter, on > only > ~4k, is really slow (CPU bound). > > You might want to avoid "SELECT *" and only specify the fields you really > need. > Try first with only "SELECT _id", just to compare. You have an average row > size > of 1.3k that the executor need to fetch and carry all the way to the result > set. > This can cost a lot of useless IO and CPU. > > You might want to tweak random_page_cost/seq_page_cost/effective_cache_size > to > find out if an index scan would do a better job, but I'm unsure because I > lack > of informations about your data and system. > > Regards, >
