Como evitar uma subconsulta na cláusula FILTER?

Várias pequenas melhorias:

SELECT created::date AS the_date
     , COUNT(*) AS total
     , COUNT(*) FILTER( WHERE is_preliminary = false
                        AND   NOT EXISTS (
                           SELECT 1
                           FROM   applications
                           WHERE  physical_person_id = a.physical_person_id
                           AND    created < a.created
                        -- AND    created < a.created::date  -- alternative? see below
                        -- AND    is_preliminary = false     -- omission? see below
                        -- AND    country = 'CZ'             -- not sure. see below
                           LIMIT  1
                           )
                        ) AS is_first_app
FROM   applications a
WHERE  created >= '2017-01-01'::timestamptz
AND    created <  '2017-07-01'::timestamptz
AND    country = 'CZ'
GROUP  BY created::date
ORDER  BY created::date;

• COALESCE( count(...), 0)é sempre ruído redundante, pois count()nunca retorna NULL para começar. Basta removê-lo. Relacionado:

  • Otimização de consultas ou índices ausentes?

• Do jeito que você fez, você agrupa e classifica pela representação de texto da sua timestamptzcoluna created, o que funciona muito bem. Mas é mais caro do que agrupar e classificar por uma data real (valor inteiro de 4 bytes internamente). Classificar por uma data ou carimbo de data/hora real também costuma ser mais confiável, embora não faça diferença nessa consulta específica. A maneira mais simples de conseguir isso é um elenco simples até o momento: created::date. Você ainda pode formatar a saída se desejar: to_char(created::date, 'YYYY-MM-DD') AS date. Mesmo resultado, mas como nós GROUP BY created::date, você deve repetir a expressão agrupada.

• Não use comoBETWEEN foi aconselhado. Seu filtro com >=e <é superior. BETWEENtraduziria para >=e <=, o que leva a casos de canto feios com valores fracionários em timestamp(ou timestamptz). Mas como o tipo de dados da coluna subjacente é timestamptz, converta timestamptzdiretamente para. Mesmo resultado, apenas uma operação de conversão a menos:

  WHERE  created >= '2017-01-01'::timestamptz
  AND    created <  '2017-07-01'::timestamptz
  

• Você está ciente de que a data derivada de um timestamptzvalor (assim como a conversão para timestamptzsem especificar um fuso horário) sempre depende da configuração do fuso horário atual , certo? Você pode colocar a consulta em um fuso horário selecionado explicitamente se quiser eliminar essa fonte de erro sorrateira. Fundamentos:

  • Ignorando completamente os fusos horários no Rails e no PostgreSQL
  • Desvantagens de usar date_trunc

• Pode haver erro(s) de lógica no cálculo de is_first_app. No entanto, isso é apenas especulação do meu lado: você está verificando se alguma linha applicationsda mesma pessoa é anterior à atual. Mas, embora você permita apenas is_preliminary = falsea linha atual, não impõe o mesmo predicado para as linhas com as quais comparar. Normalmente, você gostaria de comparar com linhas que também são is_preliminary = false. Eu adicionei uma linha comentada na consulta acima.

  Além disso, como você forma grupos por dia , você realmente deseja contar as linhas que têm uma entrada anterior no mesmo dia também? Talvez sim, mas talvez você realmente queira verificar as linhas anteriores ao dia com created < a.created::date.

  Finalmente, ainda menos seguro sobre isso, você pode querer repetir o predicado AND country = 'CZ'para restringir a comparação ao mesmo país. Não tenho informações suficientes para dizer mais.

• Eu também encurtei a sintaxe cortando aspas duplas de ruído (todos os identificadores são legais de qualquer maneira) e usando um alias de tabela estratégica ( applications a) no arquivo SELECT.

Índices

Como você está preocupado em otimizar o desempenho de leitura ...

Seu índice de várias colunas country_createdparece ideal para o SELECT. Mas continue lendo...

Mas você pode melhorar facilmente a EXISTSsubconsulta com outro índice de várias colunas:

CREATE INDEX app_person_created_idx ON applications (physical_person_id, created);

Para permitir varreduras somente de índice (somente se seus padrões de gravação permitirem!):

CREATE INDEX app_country_created_person_preliminary_idx
ON applications (country, created, physical_person_id, is_preliminary);

As colunas anexadas physical_person_ide is_preliminarysó fazem sentido se você obtiver varreduras somente de índice.

Recebo duas varreduras somente de índice depois de adicionar o último índice, que é muito mais rápido para tabelas grandes.

Mais sobre verificações somente de índice:

• Varreduras de índice lentas em uma tabela grande

Solução alternativa

Seu último comentário abre novas opções:

quando o aplicativo é criado pela primeira vez, uma nova pessoa também é criada com o mesmo valor criado.

(A afirmação anterior na pergunta era muito ambígua para trabalhar com ela.)

Se isso for aplicado de forma confiável (e creatednunca for atualizado em nenhuma tabela), há uma consulta mais simples e rápida que também acontece para " evitar uma subconsulta na FILTERcláusula " - usando um LEFT [OUTER] JOINem vez disso:

SELECT a.created::date AS date
     , COUNT(*)        AS total
     , COUNT(p.id)     AS is_first_app  -- count only counts non-null values
FROM   applications a
LEFT   JOIN persons p ON a.is_preliminary = false
                     AND p.id = a.physical_person_id  -- FK enforces max. 1 match
                     AND p.created = a.created
WHERE  a.created >= '2017-01-01'::timestamptz
AND    a.created <  '2017-07-01'::timestamptz
AND    a.country = 'CZ'
GROUP  BY a.created::date
ORDER  BY a.created::date;

Para um desempenho de leitura perfeito com duas varreduras somente de índice, você teria o índiceapp_country_created_person_preliminary_idxacima. Além disso, este empersons:

CREATE INDEX pers_id_created ON persons (id, created);

Por alguns pontos.

1. Se o seu tipo já é timestamp with timezone, então ::timestampnão faz nada.
2. Seu intervalo é escrito de forma mais limpa com BETWEEN.
3. GROUP BY 1no seu caso, está realmente lançando seus tempos para string e, em seguida, agrupando por isso. O que você quer fazer é simplesmente GROUP BY datee, em seguida, definir a data para stringify em outro select se você insistir em fazer isso no servidor (o que eu não faria de qualquer maneira).
4. ORDER BY 1no seu caso está realmente ordenando uma lista de strings.
5. Todos esses varchar acima devem ser texto. No PostgreSQL raramente usamos varchar. É texto, mas mais lento devido a uma restrição de comprimento muitas vezes inútil. A única coisa que não é texto que eu teria em seu esquema é o código do país de dois caracteres, eu o chamaria de country2char e o teria explicitamente, char(2)mas apenas por verbosidade.
6. Aspas duplas são uma prática muito ruim e são sempre altamente desencorajadas.

Então tente isso,

SELECT to_char(created, 'YYYY-MM-DD') AS "Date", total AS "Total", is_first_app AS "Is First App"
FROM (
  SELECT
    created::date AS created
    COUNT(*) AS total,
    COALESCE(
      COUNT(*) FILTER(
        WHERE applications.is_preliminary = false
        AND NOT EXISTS(
          SELECT 1
          FROM applications A
          WHERE A.physical_person_id = applications.physical_person_id
            AND A.created < applications.created
          LIMIT 1
        )
      )
      , 0
    ) AS is_first_app
  FROM applications
  WHERE
    created BETWEEN '2017-01-01' AND '2017-07-01'
    AND country = 'CZ'
  GROUP BY 1
) AS t
ORDER BY created;

Agora, para a subconsulta, acho que exigiria acesso aos dados para grok e reescrever esse. Eu não posso fazer isso na minha cabeça.

Veja este post para mais informações

• O GROUP BY select-list-ref é menos otimizado que o GROUP BY colname?

Não tenho certeza se teria um desempenho melhor, mas você pode tentar separar as duas funções de contagem e mesclar os resultados:

SELECT "Date"
      ,"Total"
      ,"Is First App"
FROM
       (SELECT
          to_char(created, 'YYYY-MM-DD')     AS "Date",
          COUNT(*) AS "Total",
        FROM
          applications
        WHERE
          created >= '2017-01-01'::TIMESTAMP AND created < '2017-07-01'::TIMESTAMP
          AND country = 'CZ'
        GROUP BY 1
       ) ttl
       LEFT  JOIN 
       (SELECT
          to_char(created, 'YYYY-MM-DD')     AS "Date",
          COUNT(*) AS "Is First App"
        FROM
             (SELECT
                physical_person_id,
                MIN(created) as created
              FROM
                applications
              WHERE
                country = 'CZ'
                AND is_preliminary = false
              GROUP BY 1
             ) fdt -- first date
        WHERE
          created >= '2017-01-01'::TIMESTAMP AND created < '2017-07-01'::TIMESTAMP
        GROUP BY 1
       ) ifa ON (ttl."Date" = ifa."Date")
ORDER BY 1
;

A subconsulta rotulada fdtobtém a primeira data de aplicação (onde countryé 'CZ') para cada physical_person_id.

A subconsulta rotulada ifapega esses resultados, elimina todas as linhas fora do intervalo desejado e fornece a contagem dos primeiros aplicativos para cada data.

A subconsulta rotulada ttlé sua consulta original, com a parte "é o primeiro aplicativo" removida. LEFT JOINesses resultados para os ifaresultados, e você deve ter o que você quer.

Não precisamos combinar physical_person_identre ttle ifa; cada contagem é independente e completa em si mesma, então só precisamos combinar as datas.

Obviamente, se demorar muito para obter o primeiro aplicativo para todos os usuários, isso pode ter um desempenho pior do que a consulta original. No entanto, ao eliminar a subconsulta correlacionada, estamos procurando esses primeiros aplicativos apenas uma vez, em vez de cada pessoa.

NOTA: o código não foi testado.