Na tabela person_rate do SQL, armazenamos um valor flutuante de taxa que muda no tempo. Colunas:

id (serial, PK)
person_id (int)
date_from (date)
rate (float)

(person_id, date_from) é único, porque no máximo uma alteração por dia é permitida (talvez possa ser uma PK, mas não é importante)

O valor da taxa para o dado person_id é válido no domínio do tempo dedate_from data para o próximo registro com sucessodate_from, ou para o futuro infty se não houver tal registro. Exemplo:

id   person_id  date_from  rate
101  1          2011-01-01 100.0
145  1          2012-01-01 180.0
193  1          2012-05-01 140.0

Agora eu preciso de uma consulta SELECT que para cadaperson_id retorna umrate válido para alguma data dada. Deixe dizer para 2012-03-01 é 180; para 2012-05-02 é 140 e assim.

Soluções que testei:

1) condiçãodate_from <= $date + use a função de janelarank() OVER (PARTITION BY person_id ORDER BY date_from DESC + em superseleçãoWHERE rank = 1

2) semelhante a 1) mas useSELECT DISTINCT ON (person_id) em vez de limitar a classificação a 1

Ambos 1) e 2) não funcionam bem, EXPLAIN mostra que o banco de dados precisa classificar todos os registros para cadaperson_id e depois limite para 1 primeiro. Provavelmente este tipo de consulta não pode utilizar completamente o índicedate_from?

IDÉIA - adicione umdate_to column, que será um pouco redundante, porque o valor será "date_from of succesive record, menos 1 day" (ou + infty se não houver registro sucessivo). Mas a consulta poderia ser comdate_from <= $date AND date_to >= $date - que provavelmente teria um bom desempenho com índices em date_from e date_to.

Mas estou com um pouco de medo de como gerenciar a integridade dos dados nesse caso - como configurar a restrição de que [date_from .. date_to] interval para um person_id não devem ser sobrepostos?

Qual é a melhor solução para o postgresql para este tipo de consulta? A carga é mais lida, não há muita gravação na tabela person_rate. Consulta típica precisaria internamente obter uma taxa para cada dia no mês ...

Talvez istoConsulta SQL para índice / chave primária com os novos índices na página 9.2 pode ajudar de alguma forma?