This query gets a list of Posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization would be to cache the "Post" ids, but unfortunately I do not have the time for that right now.
EXPLAIN ANALYZE SELECT
"Post"."id",
"Post"."actionId",
"Post"."commentCount",
...
FROM
"Posts" AS "Post"
INNER JOIN "Users" AS "user" ON "Post"."userId" = "user"."id"
LEFT OUTER JOIN "ActivityLogs" AS "activityLog" ON "Post"."activityLogId" = "activityLog"."id"
LEFT OUTER JOIN "WeightLogs" AS "weightLog" ON "Post"."weightLogId" = "weightLog"."id"
LEFT OUTER JOIN "Workouts" AS "workout" ON "Post"."workoutId" = "workout"."id"
LEFT OUTER JOIN "WorkoutLogs" AS "workoutLog" ON "Post"."workoutLogId" = "workoutLog"."id"
LEFT OUTER JOIN "Workouts" AS "workoutLog.workout" ON "workoutLog"."workoutId" = "workoutLog.workout"."id"
WHERE
"Post"."userId" IN (
201486,
1825186,
998608,
340844,
271909,
308218,
341986,
216893,
1917226,
196205,
181385,
836838,
201389,
796055,
308222,
303722,
2159055,
1625496,
192296,
192501,
163697,
2355032,
301612,
325187,
193655,
211138,
168832,
181183,
821535,
164421,
2125231,
340569,
1707494,
191529,
184516,
507978,
188815,
643019,
2290574,
2125243,
351955,
185647,
2106435,
369869,
2135811,
428473,
212574,
432718,
212440,
339445,
260399,
220239,
214074,
163709,
245337,
184841,
2337551,
2362771,
1629398,
621512,
338621,
2355759,
400089,
177507,
2162493,
339088,
165316,
239129,
358194,
249136,
179528,
329338,
364710,
455704,
180124,
1628588,
196193,
894813,
316689,
211135,
2363645,
176919,
1632895,
248529,
1615691,
196837,
174320,
242711,
266865,
238003,
139485,
2202928,
339771,
1626406,
455700,
450055,
165939,
178795,
341145,
2368038,
327801,
341338,
186890,
187038,
174119,
2162478,
2376175,
2125235,
942661,
359665,
328149,
194731,
400039,
2125225,
190577,
172848,
169990,
171880,
206736,
165035,
842297,
2125224,
2342656,
308219,
173081,
213645,
214594,
341421,
309336,
188070,
1886615,
177609,
705537,
173096,
363453,
1634185,
251297,
1820018,
231947,
338402,
181394,
388659,
1510085,
448479,
207035,
2125221,
1629654,
208356,
239575,
363454,
1618223,
328315,
211660,
316092,
1744313,
1870507,
738164,
1692798,
1116714,
170908,
1529372,
809105,
337245,
230864,
1857688,
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,
386116,
197739,
204428,
210398,
176913,
188356,
207647,
404120,
243389,
243090,
398057,
256187,
311502,
180275,
267770,
227473,
158934,
286050,
266070,
249366,
277169,
195774,
199284,
252866,
245741,
159029,
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,
316541,
762902,
273099,
229238,
375004,
193146,
188192,
191721,
172159,
207889,
201605,
194988,
184717,
210825,
190777,
188828,
230510,
192816,
190382,
215680,
288545,
190621,
196215,
208725,
236996,
188695,
198651,
180418,
243486,
356285,
188862,
233978,
168138,
205762,
243295,
203019,
227652,
200609,
196257,
210625,
240651,
224133,
194217,
224387,
213470,
241697,
200181,
228456,
209092,
232659,
212120,
1530560,
213598,
341144,
857463,
220759,
190633,
196233,
295409,
197689,
178844,
189305,
178099,
178866,
191720,
189620,
244130,
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
)
AND "Post"."private" IS NULL
ORDER BY
"Post"."createdAt" DESC
LIMIT 10;
Yields
Limit (cost=3.01..4555.20 rows=10 width=2601) (actual time=7923.011..7973.138 rows=10 loops=1)
-> Nested Loop Left Join (cost=3.01..9019264.02 rows=19813 width=2601) (actual time=7923.010..7973.133 rows=10 loops=1)
-> Nested Loop Left Join (cost=2.58..8935617.96 rows=19813 width=2376) (actual time=7922.995..7973.063 rows=10 loops=1)
-> Nested Loop Left Join (cost=2.15..8821537.89 rows=19813 width=2315) (actual time=7922.984..7961.868 rows=10 loops=1)
-> Nested Loop Left Join (cost=1.71..8700662.11 rows=19813 width=2090) (actual time=7922.981..7961.846 rows=10 loops=1)
-> Nested Loop Left Join (cost=1.29..8610743.68 rows=19813 width=2021) (actual time=7922.977..7961.816 rows=10 loops=1)
-> Nested Loop (cost=0.86..8498351.81 rows=19813 width=1964) (actual time=7922.972..7960.723 rows=10 loops=1)
-> Index Scan using posts_createdat_public_index on "Posts" "Post" (cost=0.43..8366309.39 rows=20327 width=261) (actual time=7922.869..7960.509 rows=10 loops=1)
Filter: ("userId" = ANY ('{201486,1825186,998608,340844,271909,308218,341986,216893,1917226,196205,181385,836838,201389,796055,308222,303722,2159055,1625496,192296,192501,163697,2355032,301612,325187,193655,211138,168832,181183,821535,164421,2125231,340569,1707494,191529,184516,507978,188815,643019,2290574,2125243,351955,185647,2106435,369869,2135811,428473,212574,432718,212440,339445,260399,220239,214074,163709,245337,184841,2337551,2362771,1629398,621512,338621,2355759,400089,177507,2162493,339088,165316,239129,358194,249136,179528,329338,364710,455704,180124,1628588,196193,894813,316689,211135,2363645,176919,1632895,248529,1615691,196837,174320,242711,266865,238003,139485,2202928,339771,1626406,455700,450055,165939,178795,341145,2368038,327801,341338,186890,187038,174119,2162478,2376175,2125235,942661,359665,328149,194731,400039,2125225,190577,172848,169990,171880,206736,165035,842297,2125224,2342656,308219,173081,213645,214594,341421,309336,188070,1886615,177609,705537,173096,363453,1634185,251297,1820018,231947,338402,181394,388659,1510085,448479,207035,2125221,1629654,208356,239575,363454,1618223,328315,211660,316092,1744313,1870507,738164,1692798,1116714,170908,1529372,809105,337245,230864,1857688,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,386116,197739,204428,210398,176913,188356,207647,404120,243389,243090,398057,256187,311502,180275,267770,227473,158934,286050,266070,249366,277169,195774,199284,252866,245741,159029,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,316541,762902,273099,229238,375004,193146,188192,191721,172159,207889,201605,194988,184717,210825,190777,188828,230510,192816,190382,215680,288545,190621,196215,208725,236996,188695,198651,180418,243486,356285,188862,233978,168138,205762,243295,203019,227652,200609,196257,210625,240651,224133,194217,224387,213470,241697,200181,228456,209092,232659,212120,1530560,213598,341144,857463,220759,190633,196233,295409,197689,178844,189305,178099,178866,191720,189620,244130,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: 218360
-> Index Scan using "Users_pkey" on "Users" "user" (cost=0.43..6.49 rows=1 width=1703) (actual time=0.005..0.006 rows=1 loops=10)
Index Cond: (id = "Post"."userId")
-> Index Scan using "ActivityLogs_pkey" on "ActivityLogs" "activityLog" (cost=0.43..5.66 rows=1 width=57) (actual time=0.107..0.107 rows=0 loops=10)
Index Cond: ("Post"."activityLogId" = id)
-> Index Scan using "WeightLogs_pkey" on "WeightLogs" "weightLog" (cost=0.42..4.53 rows=1 width=69) (actual time=0.001..0.001 rows=0 loops=10)
Index Cond: ("Post"."weightLogId" = id)
-> Index Scan using "Workouts_pkey" on "Workouts" workout (cost=0.43..6.09 rows=1 width=225) (actual time=0.001..0.001 rows=0 loops=10)
Index Cond: ("Post"."workoutId" = id)
-> Index Scan using "WorkoutLogs_pkey" on "WorkoutLogs" "workoutLog" (cost=0.43..5.75 rows=1 width=61) (actual time=1.118..1.118 rows=0 loops=10)
Index Cond: ("Post"."workoutLogId" = id)
-> Index Scan using "Workouts_pkey" on "Workouts" "workoutLog.workout" (cost=0.43..4.21 rows=1 width=225) (actual time=0.004..0.004 rows=0 loops=10)
Index Cond: ("workoutLog"."workoutId" = id)
Total runtime: 7974.524 ms
How can this be optimized for the time being?
I have the following relevant indexes:
-- Gets used
CREATE INDEX "posts_createdat_public_index" ON "public"."Posts" USING btree("createdAt" DESC) WHERE "private" IS null;
-- Don't get used
CREATE INDEX "posts_userid_fk_index" ON "public"."Posts" USING btree("userId");
CREATE INDEX "posts_following_index" ON "public"."Posts" USING btree("userId", "createdAt" DESC) WHERE "private" IS null;
Perhaps this requires a large partial composite index with createdAt and userId where private IS NULL?
Aucun commentaire:
Enregistrer un commentaire