Re: query issue - Mailing list pgsql-general

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