Re: query issue

Hi,

Please find below the details you asked for:

Relation size
1986 MB

table count - 1407721

We have removed few indexes.

Query -

QUERY PLAN
Limit (cost=0.43..5529.03 rows=10 width=37) (actual
time=0.974..12911.087 rows=10 loops=1)
Output: items._id
Buffers: shared hit=4838 read=3701
-> Subquery Scan on items (cost=0.43..1622646.30 rows=2935
width=37) (actual time=0.972..12911.078 rows=10 loops=1)
Output: items._id
Buffers: shared hit=4838 read=3701
-> Index Scan using sort on
"op_KFDaBAZDSXc4YYts9"."UserFeedItems" (cost=0.43..1622616.95
rows=2935 width=50) (actual time=0.970..12911.070 rows=10 loops=1)
Output: "UserFeedItems"._id, "UserFeedItems".score,
"UserFeedItems"."updatedAt"
Filter: (("UserFeedItems".is_deleted = ANY
('{t,f}'::boolean[])) AND ("UserFeedItems"."userId" =
'5d230d67bd99c5001b1ae757'::text) AND ("UserFeedItems"."itemType" <>
ALL ('{WELCOME_POST,UPLOAD_CONTACTS,BROADCAST_POST}'::text[])))
Rows Removed by Filter: 15478
Buffers: shared hit=4838 read=3701
Planning time: 100.949 ms
Execution time: 12930.302 ms

QUERY PLAN
Limit (cost=0.43..5529.03 rows=10 width=37) (actual
time=0.974..12911.087 rows=10 loops=1)
Output: items._id
Buffers: shared hit=4838 read=3701
-> Subquery Scan on items (cost=0.43..1622646.30 rows=2935
width=37) (actual time=0.972..12911.078 rows=10 loops=1)
Output: items._id
Buffers: shared hit=4838 read=3701
-> Index Scan using sort on
"op_KFDaBAZDSXc4YYts9"."UserFeedItems" (cost=0.43..1622616.95
rows=2935 width=50) (actual time=0.970..12911.070 rows=10 loops=1)
Output: "UserFeedItems"._id, "UserFeedItems".score,
"UserFeedItems"."updatedAt"
Filter: (("UserFeedItems".is_deleted = ANY
('{t,f}'::boolean[])) AND ("UserFeedItems"."userId" =
'5d230d67bd99c5001b1ae757'::text) AND ("UserFeedItems"."itemType" <>
ALL ('{WELCOME_POST,UPLOAD_CONTACTS,BROADCAST_POST}'::text[])))
Rows Removed by Filter: 15478
Buffers: shared hit=4838 read=3701
Planning time: 100.949 ms
Execution time: 12930.302 ms

Please share your suggestions.

Regards,
Atul

On 6/15/21, Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com> wrote:
> On Tue, 15 Jun 2021 19:16:41 +0530
> Atul Kumar <akumar14871(at)gmail(dot)com> wrote:
>
>> 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.
>
> First, did you test with "SELECT _id" instead of "SELECT *" ?
>
> About rand_page_costs/effective_cache_size, the fine manual already give
> some
> explanations and tips:
> https://www.postgresql.org/docs/current/runtime-config-query.html
>
> With such a low setup, I'm not sure what you can expect though. What is the
> concurrency? How many lines in total? The table size?
>