Otimização de SQL

Minha ideia era essa, estava tentando desta forma, mas não fecha, pq pode acontecer de ter várias trocas de status no mesmo dia e preciso me basear no último status daquela tarefa para aquela data.
Por isso uso sempre este trecho abaixo para traze o último status da tarefa:

select DISTINCT on (1) ts.id_tarefa, ts.id, ts.status, ts.data_hora 
FROM tarefa_status ts
WHERE cast(ts.data_hora as DATE) <= s.data
order by 1,4 desc,2 desc

Como basicamente cada tarefa passa por cada status, sem isso ele contabiliza todas para o primeiro status, pois todas já passaram ou estão nele ainda, e na verdade eu preciso saber quantas AINDA ESTÃO no primeiro status e nos seguintes, ou seja, contabilizar pelo status atual.
Eu já tinha conseguido fazer mas com auxílio de software, percorrendo o intervalo de datas e ai buscando os valores. Fico mais complicado colocando o intervalo na busca.

Usando um MAX na data não resolve o caso do último status?

SELECT 
    data,
    COUNT( CASE WHEN status = 1 THEN 1 ELSE 0) AS S1,
    COUNT( CASE WHEN status = 2 THEN 1 ELSE 0) AS S2,
    COUNT( CASE WHEN status = 3 THEN 1 ELSE 0) AS S3,
    COUNT( CASE WHEN status = 4 THEN 1 ELSE 0) AS S4,
....
FROM tarefa_status
WHERE data between '2017-03-01' and '2017-03-30'
GROUP BY data HAVING MAX(extract(hour from data))
ORDER BY data

Vi na doc do postgresql (é esse que você está usando certo?) a função extract

Não, to usando cast pra date, pq meu campo é timestamp.

Não entendi este having, deve retornar boolean (comparação por exemplo). Não aceita HAVING MAX(extract(hour from data))

Mas a função extract usa justamente um timestamp…

Quanto ao having, fiz meio que chute, o mysql aceita coisas desse tipo… enfim, se colocar o max junto ao select?

Obs: o having serve para fazer um filtro enquanto agrupa, minha lógica seria para não exibir uma coluna extra contendo a maior hora…

Ainda não dá, ele sempre traz o mesmo valor pra cada status.
Também devo considerar que o último status pode não ter sido definido naquela data e sim dias antes, porém é ele que vale por ser o mais atual. Exemplo: para contabilizar a quantidade de tarefas por status, devo usar o status mais recente de cada tarefa para o dia o qual vou contabilizar. No caso daquele intervalo de data, preciso a contagem de cada status para cada dia, assim como no resultado que postei, mas cuidando que ocorrem tarefas que tiveram a ultima atualização de status antes daquele intervalo. E conta junto, pois é o mais recente.

E agrupando também pelo id_tarefa com ordenação desc pela data?

GROUP BY data, id_tarefa
ORDER BY data DESC

Vai trazer os últimos primeiro, mas traria a maior hora, se é isso que você quer…

Obs: outros exemplos usando HAVING,

Não há como fazer somente com uma select. O status mais recente pode estar fora do intervalo que busco, ai a tarefa ficaria sem status.
EX: para contabilizar a quantidade de tarefas por status, devo usar o status mais recente de cada tarefa para o dia o qual vou contabilizar. No caso daquele intervalo de data, preciso a contagem de cada status para cada dia, assim como no resultado que postei, mas cuidando que ocorrem tarefas que tiveram a ultima atualização de status antes daquele intervalo. E conta junto, pois é o mais recente.

Para evitar uma consulta para cada coluna, tentei via case numa consulta só, mas dentro do contexto de intervalo de datas não rola.

select s.data, sss.*
from 
(select cast(s.data_hora as DATE) as data
      from tarefa_status s
      where cast(s.data_hora as DATE) between '2017-03-01' and '2017-03-30' 
      group by 1
) s
join (
	select  
	count(case ss.status when 1 then 1 else null end) as s1,
	count(case ss.status when 2 then 1 else null end) as s2,
	count(case ss.status when 3 then 1 else null end) as s3,
	count(case ss.status when 4 then 1 else null end) as s4,
	count(case ss.status when 5 then 1 else null end) as s5,
	count(case ss.status when 6 then 1 else null end) as s6,
	count(case ss.status when 7 then 1 else null end) as s7
	from (
		select DISTINCT on (1) ts.id_tarefa, ts.id, ts.status, ts.data_hora 
      		from tarefa_status ts
      		where cast(ts.data_hora as DATE) <= s.data -- n funciona
      		order by 1,4 desc,2 desc
	) ss
	group by 1
	order by 1
) sss on (???????????)
order by 1

Não sei como resolver este join nem como poderia usar a data da consulta principal para a contabilização dentro do join. Alí que parei.

Eu entendi seu problema… mas usando um or, por causa do status recente que não cai no between…

...
where (cast(s.data_hora as DATE) between '2017-03-01' and '2017-03-30') 
    or ts.data_hora <= now()
...

Afinal pelo que entendi, se o status da tarefa mudou hoje, ele tem de entrar certo?

Rapaz, vou ser sincero, ver subquery me dá agonia

Ideal seria matar esse problema com uma query só e um join sem subquery

Da forma abaixo, até executa, só não pega o último status para cada dia, tá pegando o último geral:

select s.data, sss.*
from 
(select cast(s.data_hora as DATE) as data
      from tarefa_status s
      where cast(s.data_hora as DATE) between '2017-03-01' and '2017-03-30' 
      group by 1
) s
join (
	select cast(ss.data_hora as DATE) as data,
	count(case ss.status when 1 then 1 else null end) as s1,
	count(case ss.status when 2 then 1 else null end) as s2,
	count(case ss.status when 3 then 1 else null end) as s3,
	count(case ss.status when 4 then 1 else null end) as s4,
	count(case ss.status when 5 then 1 else null end) as s5,
	count(case ss.status when 6 then 1 else null end) as s6,
	count(case ss.status when 7 then 1 else null end) as s7
	from (
		select DISTINCT on (1) ts.id_tarefa, ts.id, ts.status, ts.data_hora 
      		from tarefa_status ts
      		--where cast(ts.data_hora as DATE) <= s.data --s.data
      		order by 1,4 desc,2 desc
	) ss
	group by 1
	order by 1
) sss on (sss.data = s.data)
order by 1

sss on (???????????)

Seria no caso?

ss.id = ts.id_tarefa

Certo, mas não pode ser menor que AGORA pq devo considerar o status em que a tarefa se encontrada naquele determinado dia, não depois. Ex.: Em 25/12/1988 qual era o status da tarefa x? Ele pode ter sido setado em 1950, mas é ele que vale, se tiver outro status de 26/12/1988 deve ser desconsiderado.

Agora complicou tudo , você acabou de dizer que precisa pegar o status mais recente, nesse seu exemplo não seria o de 1988?

Eu coloquei o agora (now()) imaginando que você ainda não quer listar status futuros, até porque creio que nem foram lançados…

rsrs, não. em 25/12/1988 não tem status, é o dia que estou usando para consultar.
Vamos para mais um exemplo
Tarefa 1
Status da tarefa 1:
Tarefa 1 - 05/01/2017 - status 1
Tarefa 1 - 20/01/2017 - status 2
Tarefa 1 - 15/02/2017 - status 3
Qual é o status da tarefa 1 no dia 01/02/2017. É 2. Não é 3, pq 3 já seria no “futuro”, já que se estivessemos em 01/02/2017 o status atual dela seria 2, ela não teria ainda o status 3 pq isso ainda não aconteceu.

Isso é um histórico pra mim, preciso de uma espécie de regressão. Analisando cada data de lá pra tráz. O hj não importa, n deve ser considerado.

Será que chegamos em algum lugar?

SELECT
    t.id,
    t.data_hora,
    count(case ts.status when 1 then 1 else null end) as s1,
    count(case ts.status when 2 then 1 else null end) as s2,
    count(case ts.status when 3 then 1 else null end) as s3,
    count(case ts.status when 4 then 1 else null end) as s4,
    count(case ts.status when 5 then 1 else null end) as s5,
    count(case ts.status when 6 then 1 else null end) as s6,
    count(case ts.status when 7 then 1 else null end) as s7
FROM tarefa t
INNER JOIN tarefa_status ts ON t.id = ts.id_tarefa
WHERE 
    cast(t.data_hora as DATE) BETWEEN '2017-03-01' and '2017-03-30'
    AND cast(ts.data_hora as DATE) <= t.data
GROUP BY t.id, t.data_hora

Status da tarefa for anterior a data da tarefa? n rola.

Consegui evoluir algo com JOIN LATERAL:

select s.data, sss.*
from 
(select cast(s.data_hora as DATE) as data
      from tarefa_status s
      where cast(s.data_hora as DATE) between '2017-03-01' and '2017-03-30' 
      group by 1
) s
join LATERAL(
	select cast(ss.data_hora as DATE) as data,
	count(case ss.status when 1 then 1 else null end) as s1,
	count(case ss.status when 2 then 1 else null end) as s2,
	count(case ss.status when 3 then 1 else null end) as s3,
	count(case ss.status when 4 then 1 else null end) as s4,
	count(case ss.status when 5 then 1 else null end) as s5,
	count(case ss.status when 6 then 1 else null end) as s6,
	count(case ss.status when 7 then 1 else null end) as s7
	from (
		select DISTINCT on (1) ts.id_tarefa, ts.id, ts.status, ts.data_hora 
      		from tarefa_status ts
      		where cast(ts.data_hora as DATE) <= s.data
      		order by 1,4 desc,2 desc
	) ss
	group by 1
	order by 1
) sss on (sss.data = s.data)
order by 1

Agora ele me permite usar aquele campo data dentro do join. Só que ele não me traz os valores corretos, acredito ser culpa do group da data do join. Está contanto qt de trocas daquele status naquele dia.
Preciso tirar a coluna de data que está junto com os count, tiro o group by também, mas daí não consigo jazer o ON do join.

A sql abaixo me resolveu 100%:

select s.data, sss.*
from 
(select cast(s.data_hora as DATE) as data
      from tarefa_status s
      where cast(s.data_hora as DATE) between '2017-03-01' and '2017-03-30' 
      group by 1
) s
join LATERAL(
	select --cast(ss.data_hora as DATE) as data,
	count(case ss.status when 1 then 1 else null end) as s1,
	count(case ss.status when 2 then 1 else null end) as s2,
	count(case ss.status when 3 then 1 else null end) as s3,
	count(case ss.status when 4 then 1 else null end) as s4,
	count(case ss.status when 5 then 1 else null end) as s5,
	count(case ss.status when 6 then 1 else null end) as s6,
	count(case ss.status when 7 then 1 else null end) as s7
	from (
		select DISTINCT on (1) ts.id_tarefa, ts.id, ts.status, ts.data_hora 
      		from tarefa_status ts
      		where cast(ts.data_hora as DATE) <= s.data --'2017-03-01'
      		order by 1,4 desc,2 desc
	) ss
	--group by 1
	--order by 1
) sss on 1=1--(sss.data = s.data)
order by 1

POREM, tive q usar aquele 1=1 pra juntar as consultas, mas me traz os mesmos resultados que a primeira sql que tinhas subconsultas nas colunas. Agora de 24 segundos, passou para 4 segundos. Poderia ser melhor…

Eu nao tenho certeza se eu entendi seu problema corretamente. Se desse uns exemplos de dados existentes e o resultado esperado, ficaria mais fácil tentar uma soluçao equivalente mais simples e performática.
Você poderia mostrar seu esquema aqui por exemplo: http://sqlfiddle.com/

Com relaçao a performance que você tem agora, se nao encontrar uma query mais simples, o próximo passo é analisar o plano de execuçao, ver se precisa criar indíces, etc.

Quantos registros existem na tabela no total? Quantos serao geralmente usados nessa query?

http://sqlfiddle.com/#!15/7788d/2