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,
