Combina tsvector e timestamp in un indice?

Question

Ho una tabella che ha un campo di testo e un timestamp. Ho vari indici, come un btree sul timestamp (funziona benissimo per "ottieni la N più recente") e un GIN sul testo (per la ricerca full text, sembra CREATE INDEX foo ON bar USING GIN (to_tsvector('english', the_text)).

Ho bisogno di supportare una query simile a SELECT * FROM foo WHERE to_tsvector('english', the_text) @@ to_tsquery('english', ?) ORDER BY timestamp DESC LIMIT 1000. Funziona bene quando la query di testo ha pochissime corrispondenze, ma quando la query ha molte corrispondenze nel GIN, ci vuole un tempo incredibilmente lungo, poiché sembra che stia catturando il timestamp dall'heap per ciascuna di esse, quindi l'ordinamento.

C'è un modo per creare un singolo indice su entrambe le colonne che lo supporti?

Ad esempio, se avessi due colonne normali ae b, so che la creazione di un indice su (a, b)velocizzerebbe SELECT * FROM table WHERE a = ? ORDER BY b DESC LIMIT 1000. Esiste un equivalente per quando aè una ricerca full-text ed bè un timestamp?

Ho provato CREATE EXTENSION btree_ginquindi a creare un indice (to_tsvector('english', the_text), timestamp)o (timestamp, to_tsvector('english', the_text))utilizzare GIN o GiST. Ma nessuno di questi quattro indici sembra modificare il piano di query su una tabella di test con dati fittizi. Potrei provarli in produzione, ma richiederebbero molto tempo per crearli (giorni).

Answer 1

Potresti creare un indice RUM come questo:

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

E query come

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;

Hai bisogno di una WHEREcondizione con timestampper l'indice RUM.