Início / dba / Perguntas / 93158
Accepted
Sam
Asked: 2015-02-18 14:31:52 +0800 CST

Como acelerar selecione distinto?

  • 772

Eu tenho uma seleção simples distinta em alguns dados de séries temporais:

SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';

E leva 112 segundos. Aqui está o plano de consulta:

http://explain.depesz.com/s/NTyA

Meu aplicativo tem que realizar muitas operações distintas e contagens como esta. Existe uma maneira mais rápida de obter esse tipo de dados?

postgresql performance

3 respostas

  1. Best Answer

    Você provavelmente não quer ouvir isso, mas a melhor opção para acelerar SELECT DISTINCTé evitar DISTINCT para começar. Em muitos casos (não em todos!) isso pode ser evitado com um melhor design de banco de dados ou melhores consultas.

    Às vezes, GROUP BYé mais rápido, porque leva um caminho de código diferente.

    No seu caso particular , não parece que você pode se livrar DISTINCT(bem, veja abaixo). Mas você pode suportar a consulta com um índice especial se tiver muitas consultas desse tipo:

    CREATE INDEX foo ON events (project_id, "time", user_id);

    No Postgres 11 ou posterior, você pode usar um índice de "cobertura" real como:

    CREATE INDEX foo ON events (project_id, "time") INCLUDE (user_id);

    A adição user_idsó é útil se você obtiver varreduras somente de índice . Ver:

    Removeria o caro Bitmap Heap Scan do seu plano de consulta, que consome 90% do tempo de consulta.

    Your EXPLAINmostra 2.491 usuários distintos de meio milhão de linhas qualificadas. Isso não se tornará super rápido, não importa o que você faça, mas pode ser substancialmente mais rápido. Com cerca de 200 linhas por usuário, emular uma varredura de salto de índice no índice acima pode valer a pena. A condição de intervalo timecomplica as coisas, e 200 linhas por usuário ainda é um número moderado. Então não tenho certeza. Ver:

    De qualquer forma, se os intervalos de tempo em suas consultas forem sempre os mesmos, uma MATERIALIZED VIEWdobra user_idpor per (project_id, <fixed time interval>)seria um longo caminho. Não há chance lá com intervalos de tempo variados, no entanto. Talvez você pudesse pelo menos dobrar usuários por hora ou alguma outra unidade de tempo mínimo, e isso compraria desempenho suficiente para garantir a sobrecarga considerável. Pode ser combinado com qualquer estilo de consulta.

    Nitpick:
    Muito provavelmente, os predicados "time"devem ser:

    AND "time" >= '2015-01-11 8:00:00'
AND "time" <  '2015-02-10 8:00:00';

    Aparte:
    Não use timecomo identificador. É uma palavra reservada no SQL padrão e um tipo básico no Postgres.

    • 35

  2. Aqui está meu teste no caso de Sam e a resposta de Erwin

    drop table t1
create table t1 (id int, user_id int, project_id int, date_time timestamp without time zone) ;

insert into t1 -- 10 million row - size="498 MB"
select row_number() over(), round(row_number() over()/1000), round(row_number() over()/100000) , date
from generate_series('2015-01-01'::date, '2016-12-01'::date,'6 seconds'::interval
) date 
limit 10000000

-- before indexing - 10000000 row - output=100 row - time=2900ms
SELECT DISTINCT user_id
FROM t1
WHERE project_id = 1
AND date_time > '2015-01-01 8:00:00'
AND date_time < '2016-12-01 8:00:00' ;

CREATE INDEX foo ON t1 (project_id, date_time, user_id); -- time process=51.2 secs -- size="387 MB"         

-- after indexing - 10000000 row - output=100 row - time= 75ms (reduce ~ 38 times)
SELECT DISTINCT user_id
FROM t1
WHERE project_id = 1
AND date_time > '2015-01-01 00:00:00'
AND date_time < '2016-12-01 00:00:00' ;

    Erwin disse: "Você provavelmente não quer ouvir isso, mas a melhor opção para acelerar SELECT DISTINCT é evitar DISTINCT para começar. Em muitos casos (não todos!) isso pode ser evitado com um design de banco de dados melhor ou consultas melhores ". Acho que ele está certo, devemos evitar usar "distinto, agrupar por, ordenar por" (se houver).

    Conheci uma situação como o caso do Sam e acho que o Sam pode usar partição na tabela de eventos por mês. Isso reduzirá o tamanho dos seus dados quando você consultar, mas você precisa de uma função (pl/pgsql) para executar em vez da consulta acima. A função encontrará partições apropriadas (dependendo das condições) para executar a consulta.

    • 4

  3. Você pode tentar criar um índice espacial como um índice "rtree" em todas as suas colunas (time, project_id, user_id). Acho que isso poderia acelerar a consulta em teoria, mas não tenho certeza.

    Para outros que buscam acelerar SELECT DISTINCTsem WHERE: Alguns mecanismos de banco de dados implementam um algoritmo especial ("index skip scan", "loose indexscan", "jump scan") apenas para selecionar valores distintos das colunas principais de um índice b-tree. O PostgreSQL ainda não o possui, mas o tem no roteiro a partir de 2020. Veja Loose indexscan no Postgres Wiki . Isso não ajuda neste caso em particular, porque você tem um filtro de intervalo em outra coluna, que também precisa usar as colunas iniciais de um índice de árvore b. Você tem que escolher apenas um.

    • 1

relate perguntas