Otimize uma consulta LATERAL JOIN em uma tabela grande

Notas preliminares

• Você está usando tipos de dados ímpares. character(24)? char(n)é um tipo desatualizado e quase sempre a escolha errada. Você tem índices ativados person_ide se junta a eles repetidamente. integerseria muito mais eficiente por vários motivos. (Ou bigint, se você planeja gravar mais de 2 bilhões de linhas durante o tempo de vida da tabela.) Relacionado:

  • A pesquisa de índice seria visivelmente mais rápida com char vs varchar quando todos os valores forem 36 caracteres

• LIKEé inútil sem curingas. Use =em vez disso. Mais rápido.
  x.location_host LIKE '2015.testonline.ca'
x.location_host = '2015.testonline.ca'

• Use count(e1.*)ou count(*)em vez de adicionar uma coluna fictícia com o valor 1para cada subconsulta. (Exceto para o último ( e3), onde você não precisa de nenhum dado real.)

• Você é inconsistente em às vezes converter a string literal para timestampe às vezes não ( timestamp '2016-04-30 23:59:59.999'). Ou faz sentido, então faça o tempo todo , ou não faz, então não faça.
  Não. Quando comparado a uma timestampcoluna, uma string literal é coagida de timestampqualquer maneira. Portanto, você não precisa de um elenco explícito.

• O tipo de dados Postgres timestamptem até 6 dígitos fracionários. Suas BETWEENexpressões deixam casos de canto. Eu os substituí por expressões menos propensas a erros.

Índices

Importante: para otimizar o desempenho, crie índices multicolunas .
Para a primeira subconsulta hp:

CREATE INDEX event_pg_location_host_timestamp__idx
ON event_pg (location_host, timestamp_);

Ou, se você puder obter varreduras somente de índice, anexe person_idao índice:

CREATE INDEX event_pg_location_host_timestamp__person_id_idx
ON event_pg (location_host, timestamp_, person_id);

Para intervalos de tempo muito grandes abrangendo a maior parte ou toda a tabela, esse índice deve ser preferível - ele também oferece suporte à hlpsubconsulta, portanto, crie-o de qualquer maneira:

CREATE INDEX event_pg_location_host_person_id_timestamp__idx
ON event_pg (location_host, person_id, timestamp_);

Para tnk:

CREATE INDEX event_pg_location_fragment_timestamp__idx
ON event_pg (location_fragment, person_id, timestamp_);

Otimizado com índices parciais

Se seus predicados forem constantes location_host, location_fragmentpodemos usar índices parciais muito mais baratos , especialmente porque suas location_*colunas parecem grandes:

CREATE INDEX event_pg_hp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_host = '2015.testonline.ca';

CREATE INDEX event_pg_hlp_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_host = 'helpcentre.testonline.ca';

CREATE INDEX event_pg_tnk_person_id_ts_idx ON event_pg (person_id, timestamp_)
WHERE  location_fragment = '/file/thank-you';

Considerar:

• Índice e desempenho de várias colunas
• Um índice composto também é bom para consultas no primeiro campo?

Novamente, todos esses índices são substancialmente menores e mais rápidos com integerou bigintpara person_id.

Geralmente, você precisa acessar ANALYZEa tabela depois de criar um novo índice - ou esperar até que o autovacuum entre em ação para fazer isso por você.

• Índice que não é usado, mas influencia a consulta
• Índice parcial do PostgreSQL não utilizado quando criado em uma tabela com dados existentes

Para obter varreduras somente de índice , sua tabela deve ser VACUUM'editada o suficiente. Teste imediatamente depois VACUUMcomo prova de conceito. Leia a página vinculada do Postgres Wiki para obter detalhes se você não estiver familiarizado com varreduras somente de índice .

Consulta básica

Implementando o que eu discuti. Consulta para intervalos pequenos ( poucas linhas por person_id):

SELECT count(*)::int           AS view_homepage
     , count(hlp.hlp_ts)::int AS use_help
     , count(tnk.yes)::int     AS thank_you
FROM  (
   SELECT DISTINCT ON (person_id)
          person_id, timestamp_ AS hp_ts
   FROM   event_pg
   WHERE  timestamp_ >= '2016-04-23'
   AND    timestamp_ <  '2016-05-01'
   AND    location_host = '2015.testonline.ca'
   ORDER  BY person_id, timestamp_
   ) hp
LEFT JOIN LATERAL (
   SELECT timestamp_ AS hlp_ts
   FROM   event_pg y 
   WHERE  y.person_id = hp.person_id
   AND    timestamp_ >= hp.hp_ts
   AND    timestamp_ <  '2016-05-01'
   AND    location_host = 'helpcentre.testonline.ca'
   ORDER  BY timestamp_
   LIMIT  1
   ) hlp ON true 
LEFT JOIN LATERAL (
   SELECT true AS yes                   -- we only need existence
   FROM   event_pg z
   WHERE  z.person_id = hp.person_id    -- we can use hp here
   AND    location_fragment = '/file/thank-you'
   AND    timestamp_ >= hlp.hlp_ts      -- this introduces dependency on hlp anyways.
   AND    timestamp_ <  '2016-05-01'
   ORDER  BY timestamp_
   LIMIT  1
   ) tnk ON true;

DISTINCT ONgeralmente é mais barato para algumas linhas por person_id. Explicação detalhada:

• Selecione a primeira linha em cada grupo GROUP BY?

Se você tiver muitas linhas porperson_id(mais provável para intervalos de tempo maiores), o CTE recursivo discutido nesta resposta no capítulo 1a pode ser (muito) mais rápido:

• Otimize a consulta GROUP BY para recuperar o registro mais recente por usuário

Veja integrado abaixo.

Otimize e automatize a melhor consulta

É o velho enigma: uma técnica de consulta é melhor para um conjunto menor, outra para um conjunto maior. No seu caso particular, temos um indicador muito bom desde o início - a duração de um determinado período de tempo - que podemos usar para decidir.

Envolvemos tudo em uma função PL/pgSQL. Minha implementação muda DISTINCT ONpara rCTE quando o período de tempo determinado é maior que um limite definido:

CREATE OR REPLACE FUNCTION f_my_counts(_ts_low_inc timestamp, _ts_hi_excl timestamp)
  RETURNS TABLE (view_homepage int, use_help int, thank_you int) AS
$func$
BEGIN

CASE
WHEN _ts_hi_excl <= _ts_low_inc THEN
   RAISE EXCEPTION 'Timestamp _ts_hi_excl (1st param) must be later than _ts_low_inc!';

WHEN _ts_hi_excl - _ts_low_inc < interval '10 days' THEN  -- example value !!!
-- DISTINCT ON for few rows per person_id
   RETURN QUERY
   WITH hp AS (
      SELECT DISTINCT ON (person_id)
             person_id, timestamp_ AS hp_ts
      FROM   event_pg
      WHERE  timestamp_ >= _ts_low_inc
      AND    timestamp_ <  _ts_hi_excl
      AND    location_host = '2015.testonline.ca'
      ORDER  BY person_id, timestamp_
      )
    , hlp AS (
      SELECT hp.person_id, hlp.hlp_ts
      FROM   hp
      CROSS  JOIN LATERAL (
         SELECT timestamp_ AS hlp_ts
         FROM   event_pg
         WHERE  person_id = hp.person_id
         AND    timestamp_ >= hp.hp_ts
         AND    timestamp_ < _ts_hi_excl
         AND    location_host = 'helpcentre.testonline.ca'  -- match partial idx
         ORDER  BY timestamp_
         LIMIT  1
         ) hlp
      )
   SELECT (SELECT count(*)::int FROM hp)   -- AS view_homepage
        , (SELECT count(*)::int FROM hlp)  -- AS use_help
        , (SELECT count(*)::int            -- AS thank_you
           FROM   hlp
           CROSS  JOIN LATERAL (
              SELECT 1                     -- we only care for existence
              FROM   event_pg
              WHERE  person_id = hlp.person_id
              AND    location_fragment = '/file/thank-you'
              AND    timestamp_ >= hlp.hlp_ts
              AND    timestamp_ < _ts_hi_excl
              ORDER  BY timestamp_
              LIMIT  1
              ) tnk
           );

ELSE
-- rCTE for many rows per person_id
   RETURN QUERY
   WITH RECURSIVE hp AS (
      (  -- parentheses required
      SELECT person_id, timestamp_ AS hp_ts
      FROM   event_pg
      WHERE  timestamp_ >= _ts_low_inc
      AND    timestamp_ <  _ts_hi_excl
      AND    location_host = '2015.testonline.ca'  -- match partial idx
      ORDER  BY person_id, timestamp_
      LIMIT  1
      )
      UNION ALL
      SELECT x.*
      FROM   hp, LATERAL (
         SELECT person_id, timestamp_ AS hp_ts
         FROM   event_pg
         WHERE  person_id  > hp.person_id  -- lateral reference
         AND    timestamp_ >= _ts_low_inc  -- repeat conditions
         AND    timestamp_ <  _ts_hi_excl
         AND    location_host = '2015.testonline.ca'  -- match partial idx
         ORDER  BY person_id, timestamp_
         LIMIT  1
         ) x
      )
    , hlp AS (
      SELECT hp.person_id, hlp.hlp_ts
      FROM   hp
      CROSS  JOIN LATERAL (
         SELECT timestamp_ AS hlp_ts
         FROM   event_pg y 
         WHERE  y.person_id = hp.person_id
         AND    location_host = 'helpcentre.testonline.ca'  -- match partial idx
         AND    timestamp_ >= hp.hp_ts
         AND    timestamp_ < _ts_hi_excl
         ORDER  BY timestamp_
         LIMIT  1
         ) hlp
      )
   SELECT (SELECT count(*)::int FROM hp)   -- AS view_homepage
        , (SELECT count(*)::int FROM hlp)  -- AS use_help
        , (SELECT count(*)::int            -- AS thank_you
           FROM   hlp
           CROSS  JOIN LATERAL (
              SELECT 1                     -- we only care for existence
              FROM   event_pg
              WHERE  person_id = hlp.person_id
              AND    location_fragment = '/file/thank-you'
              AND    timestamp_ >= hlp.hlp_ts
              AND    timestamp_ < _ts_hi_excl
              ORDER  BY timestamp_
              LIMIT  1
              ) tnk
           );
END CASE;

END
$func$  LANGUAGE plpgsql STABLE STRICT;

Ligar:

SELECT * FROM f_my_counts('2016-01-23', '2016-05-01');

O rCTE trabalha com um CTE por definição. Também coloquei CTEs para a DISTINCT ONconsulta (como discuti com @Lennart nos comentários ), o que nos permite usar CROSS JOINem vez de LEFT JOINreduzir o conjunto a cada etapa, pois podemos contar cada CTE separadamente. Isso tem efeitos trabalhando em direções opostas:

• Por um lado, reduzimos o número de linhas, o que deve tornar a terceira junção mais barata.
• Por outro lado, introduzimos sobrecarga para os CTEs e precisamos de muito mais RAM, o que pode ser particularmente importante para grandes consultas como a sua.

Você terá que testar qual supera o outro.