Optymalizacja zapytania Postgres z dużym IN

30

To zapytanie zawiera listę postów utworzonych przez osoby, które obserwujesz. Możesz śledzić nieograniczoną liczbę osób, ale większość osób śledzi <1000 innych.

Przy takim stylu zapytań oczywistą optymalizacją byłoby buforowanie "Post"identyfikatorów, ale niestety nie mam teraz na to czasu.

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,
    ...  -- many more
)
AND "Post"."private" IS NULL
ORDER BY
    "Post"."createdAt" DESC
LIMIT 10;

Wydajność:

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, ... many more ...}'::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

Jak można to na razie zoptymalizować?

Mam następujące odpowiednie indeksy:

-- 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;

Być może wymaga to dużego częściowego indeksu kompozytowego z createdAti userIdgdzie private IS NULL?

— Garrett
źródło

29

Zamiast korzystać z dużej listy IN, dołącz VALUESwyrażenie lub jeśli lista jest wystarczająco duża, użyj tabeli tymczasowej, zindeksuj ją, a następnie dołącz do niej.

Byłoby miło, gdyby PostgreSQL mógł to zrobić wewnętrznie i automatycznie, ale w tym momencie planista nie wie jak.

Podobne tematy:

— Craig Ringer
źródło

28

W INPostgres istnieją dwa różne warianty konstruktu. Jeden działa z wyrażeniem podkwerendowym (zwracając zestaw ), drugi z listą wartości , co jest po prostu skrótem

expression = value1
OR
expression = value2
OR
...

Używasz drugiego formularza, który jest odpowiedni dla krótkiej listy, ale znacznie wolniejszy dla długich list. Zamiast tego podaj listę wartości jako wyrażenie podkwerendowe. Niedawno dowiedziałem się o tym wariancie :

WHERE "Post"."userId" IN (VALUES (201486), (1825186), (998608), ... )

Lubię przekazywać tablicę, odczuwać niepokój i dołączać do niej. Podobna wydajność, ale składnia jest krótsza:

...
FROM   unnest('{201486,1825186,998608, ...}'::int[]) "userId"
JOIN   "Posts" "Post" USING ("userId")

Równoważne, o ile w podanym zestawie / tablicy nie ma duplikatów . W przeciwnym razie druga forma ze JOINzwrotem powtarza duplikaty wierszy, a pierwsza ze INzwraca tylko jedną instancję. Ta subtelna różnica powoduje także różne plany zapytań.

Oczywiście potrzebujesz indeksu na "Posts"."userId".
W przypadku bardzo długich list (tysiące) przejdź do indeksowanej tabeli tymczasowej, takiej jak sugerowana @Craig. Pozwala to na łączne skanowanie indeksu bitmap w obu tabelach, co zwykle jest szybsze, gdy tylko istnieje kilka krotek na stronę danych do pobrania z dysku.

Związane z:

Jak używać DOWOLNEJ zamiast IN w klauzuli WHERE z Railsami?

Poza tym: twoja konwencja nazewnictwa nie jest zbyt pomocna, sprawia, że Twój kod jest pełny i trudny do odczytania. Raczej używaj legalnych, małych i niecytowanych identyfikatorów.

— Erwin Brandstetter
źródło