Início / dba / Perguntas / 125094
Accepted
James Hay
Asked: 2016-01-04 17:40:08 +0800 CST

Melhorando o desempenho da classificação na cláusula GROUP BY

  • 772

Tenho duas tabelas no Postgres 9.4.1 eventse event_refscom os seguintes schemas:

eventstabela

CREATE TABLE events (
  id serial NOT NULL PRIMARY KEY,
  event_type text NOT NULL,
  event_path jsonb,
  event_data jsonb,
  created_at timestamp with time zone NOT NULL
);

-- Index on type and created time

CREATE INDEX events_event_type_created_at_idx
  ON events (event_type, created_at);

event_refstabela

CREATE TABLE event_refs (
  event_id integer NOT NULL,
  reference_key text NOT NULL,
  reference_value text NOT NULL,
  CONSTRAINT event_refs_pkey PRIMARY KEY (event_id, reference_key, reference_value),
  CONSTRAINT event_refs_event_id_fkey FOREIGN KEY (event_id)
      REFERENCES events (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Ambas as tabelas contêm 2 milhões de linhas. Esta é a consulta que estou executando

SELECT
  EXTRACT(EPOCH FROM (MAX(events.created_at) - MIN(events.created_at))) as funnel_time
FROM
  events
INNER JOIN
  event_refs
ON
  event_refs.event_id = events.id AND
  event_refs.reference_key = 'project'
WHERE
    events.event_type = 'event1' OR
    events.event_type = 'event2' AND
    events.created_at >= '2015-07-01 00:00:00+08:00' AND
    events.created_at < '2015-12-01 00:00:00+08:00'
GROUP BY event_refs.reference_value
HAVING COUNT(*) > 1

Estou ciente da precedência do operador na cláusula where. Ele deve apenas filtrar eventos com o tipo 'event2' por data.

Esta é a EXPLAIN ANALYZEsaída

GroupAggregate  (cost=116503.86..120940.20 rows=147878 width=14) (actual time=3970.530..4163.041 rows=53532 loops=1)
   Group Key: event_refs.reference_value
   Filter: (count(*) > 1)
   Rows Removed by Filter: 41315
   ->  Sort  (cost=116503.86..116873.56 rows=147878 width=14) (actual time=3970.509..4105.316 rows=153766 loops=1)
         Sort Key: event_refs.reference_value
         Sort Method: external merge  Disk: 3904kB
         ->  Hash Join  (cost=24302.26..101275.04 rows=147878 width=14) (actual time=101.667..1394.281 rows=153766 loops=1)
               Hash Cond: (event_refs.event_id = events.id)
               ->  Seq Scan on event_refs  (cost=0.00..37739.00 rows=2000000 width=10) (actual time=0.007..368.661 rows=2000000 loops=1)
                     Filter: (reference_key = 'project'::text)
               ->  Hash  (cost=21730.79..21730.79 rows=147878 width=12) (actual time=101.524..101.524 rows=153766 loops=1)
                     Buckets: 16384  Batches: 2  Memory Usage: 3315kB
                     ->  Bitmap Heap Scan on events  (cost=3761.23..21730.79 rows=147878 width=12) (actual time=23.139..75.814 rows=153766 loops=1)
                           Recheck Cond: ((event_type = 'event1'::text) OR ((event_type = 'event2'::text) AND (created_at >= '2015-07-01 04:00:00+12'::timestamp with time zone) AND (created_at < '2015-12-01 05:00:00+13'::timestamp with time zone)))
                           Heap Blocks: exact=14911
                           ->  BitmapOr  (cost=3761.23..3761.23 rows=150328 width=0) (actual time=21.210..21.210 rows=0 loops=1)
                                 ->  Bitmap Index Scan on events_event_type_created_at_idx  (cost=0.00..2349.42 rows=102533 width=0) (actual time=12.234..12.234 rows=99864 loops=1)
                                       Index Cond: (event_type = 'event1'::text)
                                 ->  Bitmap Index Scan on events_event_type_created_at_idx  (cost=0.00..1337.87 rows=47795 width=0) (actual time=8.975..8.975 rows=53902 loops=1)
                                       Index Cond: ((event_type = 'event2'::text) AND (created_at >= '2015-07-01 04:00:00+12'::timestamp with time zone) AND (created_at < '2015-12-01 05:00:00+13'::timestamp with time zone))
 Planning time: 0.493 ms
 Execution time: 4178.517 ms

Estou ciente de que o filtro na event_refsvarredura da tabela não está filtrando nada, isso é resultado dos meus dados de teste, haverá tipos diferentes adicionados posteriormente.

Tudo abaixo e incluindo o HashJoinparece razoável fornecer meus dados de teste, mas estou me perguntando se é possível aumentar a Sortvelocidade da GROUP BYcláusula?

Eu tentei adicionar um índice de b-tree à reference_valuecoluna, mas não parece usá-lo. Se não estou enganado (e posso muito bem estar, por favor me diga), está classificando 153.766 linhas. Um índice não seria benéfico para esse processo de classificação?

postgresql postgresql-9.4

1 respostas

  1. Best Answer

    work_mem

    É isso que torna sua classificação cara:

    Método de classificação: mesclagem externa Disco: 3904kB

    A classificação cai no disco, o que prejudica o desempenho. Você precisa de mais RAM. Em particular, você precisa aumentar a configuração para work_mem. O manual:

    work_mem( integer)

    Especifica a quantidade de memória a ser usada por operações de classificação interna e tabelas de hash antes de gravar em arquivos de disco temporários.

    Nesse caso específico, aumentar a configuração em escassos 4 MB deve resolver o problema. Geralmente, como você vai precisar de muito mais em sua implantação completa para 60 milhões de linhas e como pode sair pela culatra se a configuração geral work_memfor muito alta (leia o manual ao qual fiz o link!), considere configurá-lo alto o suficiente localmente para o seu consulta, como:

    BEGIN;
SET LOCAL work_mem = '500MB';  -- adapt to your needs
SELECT ...;
COMMIT;

    Esteja ciente de que ainda SET LOCALpermanece até o final da transação. Você pode querer redefinir se colocar mais na mesma transação:

    RESET work_mem;

    Ou encapsule a consulta em uma função com uma configuração local de função. Resposta relacionada com exemplo para função:

    Índices

    Eu também tentaria esses índices:

    CREATE INDEX events_event_type_created_at_idx ON events (event_type, created_at, id);

    Adicionar idcomo última coluna só faz sentido se você obtiver varreduras somente de índice dela. Ver:

    E um índice parcial sobre event_refs:

    CREATE INDEX event_refs_foo_idx ON event_refs (event_id, reference_value);
WHERE  reference_key = 'project';

    O predicado WHERE reference_key = 'project'não ajuda muito em seu caso de teste (exceto talvez para planejamento de consulta), mas deve ajudar muito em sua implantação completa onde there will be different types added later.

    E isso também deve permitir varreduras somente de índice.

    Possível consulta alternativa

    Como você está selecionando grandes partes de eventsqualquer maneira, essa consulta alternativa pode ser mais rápida (depende muito da distribuição de dados):

    SELECT EXTRACT(EPOCH FROM (MAX(e.created_at) - MIN(e.created_at))) as funnel_time
FROM   events e
JOIN  (
   SELECT event_id, reference_value, count(*) AS ct
   FROM   event_refs
   WHERE  reference_key = 'project'                   
   GROUP  BY event_id, reference_value
   ) r ON r.event_id = e.id
WHERE (e.event_type = 'event1' OR
       e.event_type = 'event2')        -- see below !
AND    e.created_at >= '2015-07-01 00:00:00+08:00'
AND    e.created_at <  '2015-12-01 00:00:00+08:00'
GROUP  BY r.reference_value
HAVING sum(r.ct) > 1;

    Suspeito de um bug na consulta e que você deseja parênteses na cláusula WHEREcomo adicionei. De acordo com a precedência do operador , ANDvincula antes OR.

    Só faz sentido se houver muitas linhas por (event_id, reference_value)in event_refs. Novamente, o índice acima ajudaria.

    • 14

relate perguntas