This query fetches the likes on a post from people you follow, sorted by the number of followers they have:
EXPLAIN (ANALYZE, buffers) SELECT *
FROM
"Likes" AS "Like"
INNER JOIN "Users" AS "user" ON "Like"."userId" = "user"."id"
WHERE
"Like"."postId" = 8327949
AND "Like"."userId" IN (
1626406,
1079047,
515705,
201486,
...
)
ORDER BY
"user"."followerCount" DESC NULLS LAST
LIMIT 1000;
It is quite slow:
Limit (cost=709.76..709.76 rows=3 width=813) (actual time=73.766..73.771 rows=7 loops=1)
Buffers: shared hit=28 read=166
-> Sort (cost=709.76..709.76 rows=3 width=813) (actual time=73.764..73.766 rows=7 loops=1)
Sort Key: "user"."followerCount"
Sort Method: quicksort Memory: 34kB
Buffers: shared hit=28 read=166
-> Nested Loop (cost=0.99..709.73 rows=3 width=813) (actual time=11.520..73.683 rows=7 loops=1)
Buffers: shared hit=25 read=166
-> Index Scan using likes_postid_fk_index on "Likes" "Like" (cost=0.56..684.35 rows=3 width=40) (actual time=11.499..67.513 rows=7 loops=1)
Index Cond: ("postId" = 8327949)
Filter: ("userId" = ANY ('{1626406,1079047,515705,201486,1825186,2337551,1618223,1744313,1692798,1116714,170908,1529372,809105,337245,1442385,318381,280070,513691,223935,1115566,781861,961253,473281,380685,317279,330858,356948,865515,644161,321362,266739,702785,301291,400095,504921,892772,197472,529580,336546,469157,185441,296105,271139,542717,300530,476953,198200,175499,504644,347030,291177,312798,189770,245629,488431,465146,204447,249853,195334,272305,490817,663110,1312247,339915,244102,479168,169500,286156,215769,206940,197739,204428,210398,176913,188356,207647,404120,243389,243090,398057,256187,311502,180275,267770,227473,158934,286050,266070,249366,277169,195774,199284,252866,245741,447991,189924,271664,228529,188791,219823,257444,237700,188181,358902,368279,233172,323078,367453,206960,1335635,214592,189455,287922,198394,205285,251488,268568,254681,192665,248781,211264,218413,260777,228080,244382,520167,188110,179650,699808,1104265,233165,200331,209695,233674,321823,186993,190657,862183,243259,337044,256918,212106,298535,242418,285738,243407,240652,306349,308079,310548,208338,279951,355813,298999,291297,240830,294256,320524,237990,260782,170375,206414,203776,194467,205774,209708,240820,308362,303978,253173,252944,249359,249741,242279,278825,273099,229238,375004,193146,188192,191721,172159,207889,201605,194988,210825,190777,188828,230510,192816,190382,215680,288545,190621,196215,208725,236996,188695,198651,243486,356285,188862,233978,168138,243295,203019,227652,200609,196257,210625,240651,224133,194217,224387,213470,241697,200181,228456,209092,232659,212120,1530560,213598,857463,220759,190633,196233,295409,197689,189305,178099,178866,191720,189620,192746,191414,188216,203902,494855,114199,165205,192481,188341,188649,188635,188768,189464,180017,169639,188606,159064,205322,171865,187781,163983,187124,187385,338404,181279,162941,162487,182242,169368}'::integer[]))
Rows Removed by Filter: 148
Buffers: shared hit=6 read=153
-> Index Scan using "Users_pkey" on "Users" "user" (cost=0.43..8.45 rows=1 width=773) (actual time=0.690..0.874 rows=1 loops=7)
Index Cond: (id = "Like"."userId")
Buffers: shared hit=19 read=13
Total runtime: 73.887 ms
The index used is:
CREATE INDEX likes_postId_fk_index ON "Likes" ("postId");
I was wondering if there was a way to speed it up.
Aucun commentaire:
Enregistrer un commentaire