Início / dba / Perguntas / 138276
Accepted
Garrett
Asked: 2016-05-12 14:02:40 +0800 CST

Baixo desempenho de SELECT na nova verificação com dados e índice parcial grande (milhões de linhas)

  • 772

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?

postgresql index

2 respostas

  1. Best Answer

    Adicione também o segundo predicado de sua consulta ao índice parcial:

    WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398'

    Seu carimbo de data/hora é provavelmente um alvo em movimento, mas vou assumir que você tem muitas linhas antigas que são excluídas na maioria de suas consultas e apenas algumas linhas "mais jovens" são de interesse. Um caso de uso típico. Você pode cortar linhas antigas em seu índice parcial.

    CREATE INDEX posts_new_createdat_idx ON "Posts"("createdAt")
WHERE "new"
AND   "createdAt" > '2015-08-01 00:00';  -- replace with useful timestamp

    Isso só faz sentido se você puder reduzir o tamanho do índice para uma fração. Caso contrário, o efeito normalmente não vale a pena.

    Substitua por um carimbo de data/hora oportuno para cortar o máximo de linhas possível enquanto ainda cobre as linhas relevantes para suas consultas.

    Versões modernas do Postgres são inteligentes o suficiente para entender que o índice é aplicável a consultas interrompidas em um carimbo de data/hora posterior. Para versões mais antigas, você precisa adicionar a condição de índice literal de forma redundante para fazer o planejador de consulta usar esse índice parcial.

    Este índice será usado por sua consulta imediatamente como está . (Acabei de remover mais alguns ruídos):

    SELECT COUNT(*) AS "count"
FROM  "Posts"
WHERE "createdAt" > '2015-08-19 14:55:50.398'
AND   "new";

    Relacionado:

    Aparte 1: "new" = TRUEé apenas uma maneira ruidosa de declarar "new"um valor booleano.

    Aparte 2: Não use palavras reservadas ou maiúsculas e minúsculas CaMeL ou outros identificadores ilegais para que você não precise usar aspas duplas o tempo todo. Muito propenso a erros.

    • 3

  2. A única razão para uma nova verificação nesta consulta específica é que o bitmap é muito grande para caber em work_mem e, portanto, deve ser rebaixado para com perdas. Portanto, para evitar a verificação, tente aumentar work_mem, se puder. Você não deveria ter que aumentá-lo muito para manter 1138854 tuplas.

    Versões mais recentes do PostgreSQL tornam isso mais claro, incluindo linhas como esta no "EXPLAIN (ANALYZE)":

    Rows Removed by Index Recheck: 33
Heap Blocks: exact=558 lossy=15682

    No entanto, não espere nada mágico aqui depois de se livrar da verificação. Você pode estar superestimando a quantidade de tempo gasto na nova verificação, em comparação com outras coisas.

    • 1

relate perguntas