Início / dba / Perguntas / 91468
Accepted
Garrett
Asked: 2015-02-10 00:10:07 +0800 CST

Como otimizar uma query envolvendo um sort e um LIKE?

  • 772

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.

postgresql index

2 respostas

  1. Best Answer

    Suas tentativas de índice ainda são inúteis, pois o predicado

    u."name" LIKE 'garr%'

    é obviamente o mais seletivo, mas não pode usar um índice b-tree básico (padrão), a menos que você esteja operando com a localidade "C" (que é incomum).

    SELECT *
FROM   "Users"
WHERE  name LIKE 'garr%'
AND    id <> 2449214
AND    hellbanned IS NULL
AND    "hellbanPostsAfterDate" IS NULL
ORDER  BY "followerCount" DESC NULLS LAST
LIMIT  3;

    Existem várias opções (também dependendo da definição da tabela ausente e outras informações), este índice deve funcionar:

    CREATE INDEX "Users_whatever_idx"
ON     "Users" (name text_pattern_ops, "followerCount" DESC NULLS LAST) 
WHERE  hellbanned IS NULL
AND   "hellbanPostsAfterDate" IS NULL;

    As condições em hellbannede "hellbanPostsAfterDate"só são úteis se excluírem mais do que algumas linhas. Caso contrário, um índice completo pode ser a melhor escolha, pois pode ser usado para mais consultas diferentes.

    Além disso, há uma chance de a segunda coluna no índice "followerCount"não ajudar, pois normalmente há várias correspondências diferentes para a primeira e o Postgres precisa classificar, afinal. Teste algumas consultas típicas com e sem a segunda coluna no índice.

    Detalhes para índices e correspondência de padrões, incluindo uma explicação para a classe do operador text_pattern_ops:

    À parte: Meu conselho permanente é usar identificadores sem aspas, legais e em minúsculas exclusivamente no Postgres.

    • 3

  2. Talvez você tente usar a consulta aninhada ...

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

relate perguntas