query issue

[Atul Kumar <akumar14871@xxxxxxxxx>]

Hi,

I have postgres 10 running on RDS instance.

I have query below:

select * from "op_KFDaBAZDSXc4YYts9"."UserFeedItems"
where (("itemType" not in ('WELCOME_POST', 'UPLOAD_CONTACTS',
'BROADCAST_POST')) and ("userId" = '5d230d67bd99c5001b1ae757' and
"is_deleted" in (true, false)))
order by "score" asc, "updatedAt" desc limit 10;


Explain plan is like given below:

QUERY PLAN
Limit  (cost=11058.03..11058.05 rows=10 width=1304) (actual
time=6105.283..6105.293 rows=10 loops=1)
  ->  Sort  (cost=11058.03..11065.36 rows=2935 width=1304) (actual
time=6105.281..6105.283 rows=10 loops=1)
        Sort Key: score, "updatedAt" DESC
        Sort Method: top-N heapsort  Memory: 36kB
        ->  Bitmap Heap Scan on "UserFeedItems"
(cost=131.33..10994.60 rows=2935 width=1304) (actual
time=26.245..6093.680 rows=3882 loops=1)
              Recheck Cond: ("userId" = '5d230d67bd99c5001b1ae757'::text)
              Filter: ((is_deleted = ANY ('{t,f}'::boolean[])) AND
("itemType" <> ALL
('{WELCOME_POST,UPLOAD_CONTACTS,BROADCAST_POST}'::text[])))
              Rows Removed by Filter: 1
              Heap Blocks: exact=3804
              ->  Bitmap Index Scan on "userId"  (cost=0.00..130.60
rows=2956 width=0) (actual time=24.835..24.836 rows=3885 loops=1)
                    Index Cond: ("userId" = '5d230d67bd99c5001b1ae757'::text)
Planning time: 20.928 ms
Execution time: 6108.610 ms



My table structure is somewhat like this

CREATE TABLE "op_KFDaBAZDSXc4YYts9"."UserFeedItems"
(
    _id text COLLATE pg_catalog."default" NOT NULL DEFAULT uuid_generate_v4(),
    "userId" text COLLATE pg_catalog."default" NOT NULL,
    "itemType" text COLLATE pg_catalog."default" NOT NULL,
    payload jsonb NOT NULL,
    score numeric NOT NULL,
    "generalFeedItemId" text COLLATE pg_catalog."default",
    "createdAt" timestamp without time zone NOT NULL DEFAULT
(now())::timestamp without time zone,
    "createdBy" text COLLATE pg_catalog."default",
    "updatedAt" timestamp without time zone NOT NULL DEFAULT
(now())::timestamp without time zone,
    "updatedBy" text COLLATE pg_catalog."default",
    is_deleted boolean DEFAULT false,
    "isRead" boolean NOT NULL DEFAULT false,
    CONSTRAINT "UserFeedItems_pkey" PRIMARY KEY (_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;


CREATE INDEX "UserFeedItems_id"
    ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
    (_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: UserFeedItems_itemType_userId_isdeleted_score_updatedAt

-- DROP INDEX "op_KFDaBAZDSXc4YYts9"."UserFeedItems_itemType_userId_isdeleted_score_updatedAt";

CREATE INDEX "UserFeedItems_itemType_userId_isdeleted_score_updatedAt"
    ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
    ("itemType" COLLATE pg_catalog."default" ASC NULLS LAST, "userId"
COLLATE pg_catalog."default" ASC NULLS LAST, is_deleted ASC NULLS
LAST, score ASC NULLS LAST, "updatedAt" DESC NULLS FIRST)
    TABLESPACE pg_default;
-- Index: score

-- DROP INDEX "op_KFDaBAZDSXc4YYts9".score;

CREATE INDEX score
    ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
    (score ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: updatedat

-- DROP INDEX "op_KFDaBAZDSXc4YYts9".updatedat;

CREATE INDEX updatedat
    ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
    ("updatedAt" DESC NULLS FIRST)
    TABLESPACE pg_default;
-- Index: userId

-- DROP INDEX "op_KFDaBAZDSXc4YYts9"."userId";

CREATE INDEX "userId"
    ON "op_KFDaBAZDSXc4YYts9"."UserFeedItems" USING btree
    ("userId" COLLATE pg_catalog."default" text_pattern_ops ASC NULLS LAST)
    TABLESPACE pg_default;





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 ?








Regards,
Atul