Combinar tsvector e timestamp em um índice?

Question

Eu tenho uma tabela que tem um campo de texto e um carimbo de data / hora. Eu tenho vários índices, como um btree no carimbo de data / hora (isso funciona muito bem para "obter o N mais recente") e um GIN no texto (para pesquisa de texto completo, parece CREATE INDEX foo ON bar USING GIN (to_tsvector('english', the_text)).

Eu preciso oferecer suporte a uma consulta semelhante a SELECT * FROM foo WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?) ORDER BY timestamp DESC LIMIT 1000. Isso funciona bem para quando a consulta de texto tem muito poucas correspondências, mas quando a consulta tem muitas correspondências no GIN, leva uma quantidade de tempo incrivelmente longa, uma vez que parece estar pegando o carimbo de data / hora do heap para cada uma, em seguida, classificação.

Existe alguma maneira de criar um único índice em ambas as colunas que suporte isso?

Por exemplo, se eu tivesse duas colunas normais ae b, eu sei que criar um índice em (a, b)seria mais rápido SELECT * FROM table WHERE a = ? ORDER BY b DESC LIMIT 1000. Existe um equivalente para quando aé uma pesquisa de texto completo e bum carimbo de data / hora?

Tentei CREATE EXTENSION btree_ginentão criar um índice em (to_tsvector('english', the_text), timestamp)ou (timestamp, to_tsvector('english', the_text))usando GIN ou GiST. Mas nenhum desses quatro índices parece alterar o plano de consulta em uma tabela de teste com dados fictícios. Eu poderia experimentá-los em produção, mas levariam muito tempo para serem criados (dias).

Answer 1

Você pode criar um índice RUM como este:

CREATE INDEX ON bar USING rum (
   to_tsvector('english', the_text) rum_tsvector_hash_ops,
   timestamp
);

E consulta como

SELECT * FROM foo
WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?)
  AND timestamp < '2021-01-01 00:00:00'
ORDER BY timestamp DESC
LIMIT 1000;

Você precisa de uma WHEREcondição com timestamppara o índice RUM.