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@dalibo.com> wrote:
> On Tue, 15 Jun 2021 16:12:11 +0530
> Atul Kumar <akumar14871@gmail.com> 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,
>
