Garrett's questions

Consulta:

EXPLAIN (ANALYZE, BUFFERS) SELECT
    COUNT (*) AS "count"
FROM
    "Posts" AS "Post"
WHERE
    "Post"."createdAt" > '2015-08-19 14:55:50.398'
AND "Post"."new" = TRUE;

Índice:

CREATE INDEX posts_new_createdat_idx ON "Posts" ("createdAt")
WHERE
    NEW = TRUE

Plano:

Aggregate  (cost=389915.59..389915.60 rows=1 width=0) (actual time=4234.772..4234.773 rows=1 loops=1)
  Buffers: shared hit=254427
  ->  Bitmap Heap Scan on "Posts" "Post"  (cost=14415.81..387990.63 rows=769985 width=0) (actual time=123.805..3859.150 rows=1138854 loops=1)
        Recheck Cond: (("createdAt" > '2015-08-19 14:55:50.398+00'::timestamp with time zone) AND new)
        Rows Removed by Index Recheck: 8238790
        Buffers: shared hit=254427
        ->  Bitmap Index Scan on posts_new_createdat_idx  (cost=0.00..14223.32 rows=769985 width=0) (actual time=122.601..122.601 rows=1138854 loops=1)
              Index Cond: ("createdAt" > '2015-08-19 14:55:50.398+00'::timestamp with time zone)
              Buffers: shared hit=3114
Total runtime: 4234.989 ms

Esquema:

CREATE TABLE "public"."Posts" (
    "id" int4 NOT NULL DEFAULT nextval('"Posts_id_seq"'::regclass),
    "actionId" int4,
    "commentCount" int4 DEFAULT 0,
    "facebook" bool,
    "featurePostOnDate" timestamp(6) WITH TIME ZONE,
    "forcedPrivate" bool,
    "instagram" bool,
    "isReported" bool,
    "likeCount" int4 DEFAULT 0,
    "note" text COLLATE "default",
    "photo" varchar(255) COLLATE "default",
    "private" bool,
    "new" bool,
    "popular" bool,
    "twitter" bool,
    "userId" int4,
    "objectId" varchar(255) COLLATE "default",
    "createdAt" timestamp(6) WITH TIME ZONE,
    "updatedAt" timestamp(6) WITH TIME ZONE,
    "activityLogId" int4,
    "weightLogId" int4,
    "workoutId" int4,
    "workoutLogId" int4,
    "thumbnail" varchar(255) COLLATE "default"
)

Dados:

• new = truepara 99% ou registros
• Quaisquer postagens com mais de 2 semanas podem ser ignoradas (da contagem e do índice)

Detalhes do banco de dados:

Host           = Amazon AWS
Engine         = PostgreSQL 9.3.10
Instance Class = db.r3.8xlarge
Storage Type   = SSD
IOPS           = 3000
StorageAmount  = 500 GB

Como você pode ver, o índice parcial é muito grande. Existe uma maneira de indexar melhor para que a condição de nova verificação não seja tão pesada?

Eu preciso definir a coluna para NULL. Até agora isso funcionou, mas por algum motivo, nesta mesa que é muito maior que as outras, parece que não está funcionando:

ALTER TABLE "public"."WorkoutExercises" DROP COLUMN "_etl";
ALTER TABLE "public"."WorkoutExercises" ADD COLUMN "_etl" bool;

No entanto

SELECT
    *
FROM
    "WorkoutExercises"
WHERE
    "_etl" = TRUE
LIMIT 1000;

Retorna 1.000 resultados. Por que isso acontece e como isso pode ser consertado?

Esta consulta é muito lenta:

EXPLAIN (ANALYZE, buffers) SELECT *
FROM
    "Follows" AS "Follow"
INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id"
WHERE
    "Follow"."followerId" = 169368
ORDER BY
    "Follow"."createdAt" DESC
LIMIT 1000;

Aqui está a explicação:

Limit  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.776..239.289 rows=262 loops=1)
  Buffers: shared hit=750 read=673 dirtied=1
  ->  Nested Loop  (cost=0.86..2120.08 rows=141 width=814) (actual time=0.774..239.148 rows=262 loops=1)
        Buffers: shared hit=750 read=673 dirtied=1
        ->  Index Scan using follows_followinglist_followerid_createdat_idx on "Follows" "Follow"  (cost=0.43..681.88 rows=170 width=41) (actual time=0.377..52.687 rows=262 loops=1)
              Index Cond: ("followerId" = 169368)
              Buffers: shared hit=149 read=115
        ->  Index Scan using "Users_pkey" on "Users" followee  (cost=0.43..8.45 rows=1 width=773) (actual time=0.559..0.709 rows=1 loops=262)
              Index Cond: (id = "Follow"."followeeId")
              Buffers: shared hit=601 read=558 dirtied=1
Total runtime: 239.545 ms

Eu tenho os seguintes índices (atualmente, ele está usando o primeiro):

CREATE INDEX follows_followinglist_followerid_createdat_idx ON "Follows"
  ("followerId", "createdAt" DESC)
CREATE INDEX follows_followinglist_followerid_createdat_idx2 ON "Follows"
  ("createdAt" DESC, "followerId")
CREATE INDEX follows_followerId_fk_index ON "Follows" ("followerId");
CREATE INDEX  "follows_createdat_index" ON "public"."Follows" USING btree("createdAt" DESC);

Antes de adicionar os dois primeiros, o custo era um pouco menor (~ 2.000) usando follows_followerId_fk_index, mas ainda muito lento.

Eu estou querendo saber se há uma maneira de otimizar isso ainda mais.

Aqui está a consulta:

EXPLAIN ANALYZE SELECT
    *
FROM
    "Users" AS "User"
WHERE
    "User"."name" LIKE 'garr%'
AND "User"."id" NOT IN (2449214)
AND "User"."hellbanned" IS NULL
AND "User"."hellbanPostsAfterDate" IS NULL
ORDER BY
    "followerCount" DESC NULLS LAST
LIMIT 3;

Que rende:

Limit  (cost=0.43..3662.92 rows=3 width=1711) (actual time=181.935..2158.898 rows=3 loops=1)
  ->  Index Scan using users_search_followercount_index on "Users" "User"  (cost=0.43..280791.25 rows=230 width=1711) (actual time=181.933..2158.891 rows=3 loops=1)
        Filter: (((name)::text ~~ 'garr%'::text) AND (id <> 2449214))
        Rows Removed by Filter: 29434
Total runtime: 2158.951 ms

O índice usado é o mais ideal que eu poderia construir:

CREATE INDEX users_search_followercount_index ON "Users" ("followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL;

Eu também tentei estes:

-- CREATE UNIQUE INDEX users_name_unique_index ON "Users" ("name");
-- CREATE INDEX users_followerCount_index ON "Users" ("followerCount" DESC NULLS LAST);
-- CREATE UNIQUE INDEX users_search_name_followercount_unique_index ON "Users" ("name", "followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- N/A
-- CREATE UNIQUE INDEX users_search_followercount_name_unique_index ON "Users" ("followerCount" DESC NULLS LAST, "name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422
-- CREATE INDEX users_name_unique_index ON "Users" ("name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422

Mas nenhum acelerou.

Essa consulta obtém uma lista de postagens criadas por pessoas que você segue. Você pode seguir um número ilimitado de pessoas, mas a maioria das pessoas segue < 1.000 pessoas.

Com esse estilo de consulta, a otimização óbvia seria armazenar em cache os "Post"ids, mas infelizmente não tenho tempo para isso no momento.

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;

Rendimentos:

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

Como isso pode ser otimizado por enquanto?

Tenho os seguintes índices relevantes:

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

Talvez isso exija um grande índice composto parcial com createdAte userIdonde private IS NULL?

Usando o Amazon RDS, estamos executando scripts ETL para migrar nossos dados. No entanto, a cada poucas horas, há um grande pico de CPU.

Aqui estão as especificações ETL (por ETL):

50 records inserted / second
pool of 1000 connections

Aqui estão as especificações do servidor:

Amazon R3.8XL
244 GB RAM
32 vCPU
3TB SSD
10 GiB Network Performance
No Multi-AZ (yet)

Aqui estão as configurações principais e modificadas do grupo de parâmetros PG:

checkpoint_completion_target = 0.9
checkpoint_segments          = 16
effective_cache_size         = {DBInstanceClassMemory/10923} (8kb)
maintenance_work_mem         = {DBInstanceClassMemory/16384} (kb)
max_connections              = {DBInstanceClassMemory/12582880}
max_locks_per_transaction    = 64
shared_buffers               = {DBInstanceClassMemory/32768} (8kb)
work_mem                     = {DBInstanceClassMemory/20480000} (kb)

Neste caso, DBInstanceClassMemoryé aproximadamente 244,000,000,000 bytes. O (8kb)significa que o valor é tomado como blocos de 8kb, então shared_buffers = 244000000000/32768*8000 = 60 gb. Todas as alterações feitas foram baseadas neste artigo , e defini effective_cache_size75% porque (como você verá abaixo) a memória não parece estar sendo totalmente utilizada.

Aqui está uma captura de tela das estatísticas do nosso servidor de banco de dados em um período de 6 horas:

O gráfico no canto superior esquerdo mostra os picos de CPU e você pode ver a queda correlacionada em Write IOPS (o gráfico abaixo).

Qual pode ser o motivo desses picos de CPU? Eles congelam quase completamente as consultas pelo ETL (levando mais de 3 minutos para consultas que normalmente levariam menos de um segundo).

Isenção de responsabilidade : sou relativamente novo no PostgreSQL.

Eu estou querendo saber como otimizar uma consulta que faz 2 INNER JOINs. Meu cenário é bastante simples:

Selecione Postagens com uma foto ( Posts.photo IS NOT NULL) e uma Hashtag com o nome 'morto' ( Hashtags.name = 'dead').

As associações são as seguintes:

Posts <- PostHashtags -> Hashtags

Posts.id    = PostHashtags.postId (FK)
Hashtags.id = PostHashtags.hashtagId (FK)

Aqui está a consulta:

SELECT
  "Posts".*,
  "hashtags"."id" AS "hashtags.id",
  "hashtags"."count" AS "hashtags.count",
  "hashtags"."name" AS "hashtags.name",
  "hashtags"."createdAt" AS "hashtags.createdAt",
  "hashtags"."updatedAt" AS "hashtags.updatedAt",
  "hashtags"."objectId" AS "hashtags.objectId",
  "hashtags"."_etl" AS "hashtags._etl",
  "hashtags.PostHashtag"."id" AS "hashtags.PostHashtag.id",
  "hashtags.PostHashtag"."createdAt" AS "hashtags.PostHashtag.createdAt",
  "hashtags.PostHashtag"."updatedAt" AS "hashtags.PostHashtag.updatedAt",
  "hashtags.PostHashtag"."postId" AS "hashtags.PostHashtag.postId",
  "hashtags.PostHashtag"."hashtagId" AS "hashtags.PostHashtag.hashtagId",
  "hashtags.PostHashtag"."objectId" AS "hashtags.PostHashtag.objectId",
  "hashtags.PostHashtag"."_etl" AS "hashtags.PostHashtag._etl"

FROM (
  SELECT
    "Posts"."id",
    "Posts"."note",
    "Posts"."photo",
    "Posts"."createdAt",
    "user"."id" AS "user.id",
    "user"."name" AS "user.name"
  FROM "Posts" AS "Posts"

  INNER JOIN "Users" AS "user" ON "Posts"."userId" = "user"."id"

  WHERE "Posts"."photo" IS NOT NULL
  AND (
    SELECT "PostHashtags"."id" FROM "PostHashtags" AS "PostHashtags"
    INNER JOIN "Hashtags" AS "Hashtag" ON "PostHashtags"."hashtagId" = "Hashtag"."id"
    WHERE "Posts"."id" = "PostHashtags"."postId"
    LIMIT 1
  ) IS NOT NULL

  ORDER BY "Posts"."createdAt" DESC LIMIT 10
) AS "Posts"

INNER JOIN (
  "PostHashtags" AS "hashtags.PostHashtag"
  INNER JOIN "Hashtags" AS "hashtags" ON "hashtags"."id" = "hashtags.PostHashtag"."hashtagId"
)

ON "Posts"."id" = "hashtags.PostHashtag"."postId"
AND "hashtags"."name" = 'dead'

ORDER BY "Posts"."createdAt" DESC;

EXPLICAR os resultados:

Nested Loop  (cost=886222912.89..886223769.55 rows=1 width=277)
  Join Filter: ("hashtags.PostHashtag"."postId" = "Posts".id)
  ->  Limit  (cost=886220835.39..886220835.42 rows=10 width=189)
        ->  Sort  (cost=886220835.39..886220988.88 rows=61394 width=189)
              Sort Key: "Posts"."createdAt"
              ->  Nested Loop  (cost=0.42..886219508.69 rows=61394 width=189)
                    ->  Seq Scan on "Posts"  (cost=0.00..885867917.51 rows=78196 width=177)
                          Filter: ((photo IS NOT NULL) AND ((SubPlan 1) IS NOT NULL))
                          SubPlan 1
                            ->  Limit  (cost=0.42..815.70 rows=1 width=4)
                                  ->  Nested Loop  (cost=0.42..815.70 rows=1 width=4)
                                        ->  Seq Scan on "PostHashtags"  (cost=0.00..811.25 rows=1 width=8)
                                              Filter: ("Posts".id = "postId")
                                        ->  Index Only Scan using "Hashtags_pkey" on "Hashtags" "Hashtag"  (cost=0.42..4.44 rows=1 width=4)
                                              Index Cond: (id = "PostHashtags"."hashtagId")
                    ->  Index Scan using "Users_pkey" on "Users" "user"  (cost=0.42..4.49 rows=1 width=16)
                          Index Cond: (id = "Posts"."userId")
  ->  Materialize  (cost=2077.50..2933.89 rows=1 width=88)
        ->  Hash Join  (cost=2077.50..2933.89 rows=1 width=88)
              Hash Cond: ("hashtags.PostHashtag"."hashtagId" = hashtags.id)
              ->  Seq Scan on "PostHashtags" "hashtags.PostHashtag"  (cost=0.00..721.00 rows=36100 width=40)
              ->  Hash  (cost=2077.49..2077.49 rows=1 width=48)
                    ->  Seq Scan on "Hashtags" hashtags  (cost=0.00..2077.49 rows=1 width=48)
                          Filter: ((name)::text = 'dead'::text)

Esta consulta foi ligeiramente simplificada. Ele também executa OUTER JOINSem outros dados relacionados a Posts, e é por isso que SELECTdeve ser executado em Postsvez de, digamos, PostHashtags.

Qualquer ajuda na tradução EXPLAINpara um índice útil seria muito apreciada.

Minhas ideias:

1. Construa um índice em Posts.photo, mas deve ser um índice parcial WHERE "photo" IS NOT NULL?
2. Crie um UNIQUEíndice em Hashtags.name.

Não tenho certeza se esses são necessariamente os gargalos.