Ottimizza la query con OFFSET su una tabella di grandi dimensioni

Ho un tavolo

create table big_table ( id serial primary key, -- other columns here vote int ); 

Questa tabella è molto grande, circa 70 milioni di righe, ho bisogno di interrogare:

 SELECT * FROM big_table ORDER BY vote [ASC|DESC], id [ASC|DESC] OFFSET x LIMIT n -- I need this for pagination 

Come forse saprai, quando x è un numero elevato, query come questa sono molto lente.

Per l’ottimizzazione delle prestazioni ho aggiunto degli indici:

 create index vote_order_asc on big_table (vote asc, id asc); 

e

 create index vote_order_desc on big_table (vote desc, id desc); 

EXPLAIN mostra che la suddetta query SELECT utilizza questi indici, ma è comunque molto lenta con un ampio offset.

Cosa posso fare per ottimizzare le query con OFFSET nei grandi tavoli? Forse le versioni PostgreSQL 9.5 o anche più recenti hanno alcune funzionalità? Ho cercato ma non ho trovato nulla.

Un OFFSET grandi dimensioni sarà sempre lento. Postgres deve ordinare tutte le righe e contare quelle visibili fino al tuo offset. Per saltare direttamente tutte le righe precedenti è ansible aggiungere un row_number indicizzato alla tabella (o creare una MATERIALIZED VIEW includa detto row_number ) e lavorare con WHERE row_number > x invece di OFFSET x .

Tuttavia, questo approccio è sensato solo per dati di sola lettura (o per lo più). Implementare lo stesso per i dati della tabella che possono cambiare contemporaneamente è più difficile. È necessario iniziare definendo esattamente il comportamento desiderato.

Suggerisco un approccio diverso per l’ impaginazione :

 SELECT * FROM big_table WHERE (vote, id) > (vote_x, id_x) -- ROW values ORDER BY vote, id -- needs to be deterministic LIMIT n; 

Dove vote_x e id_x provengono dall’ultima riga della pagina precedente (sia per DESC che per ASC ). O dal primo se si naviga all’indietro .

Il confronto dei valori delle righe è supportato dall’indice già presente, una funzionalità conforms a ANSI SQL, ma non tutti gli RDBMS lo supportano.

 CREATE INDEX vote_order_asc ON big_table (vote, id); 

O per ordine decrescente:

 SELECT * FROM big_table WHERE (vote, id) < (vote_x, id_x) -- ROW values ORDER BY vote DESC, id DESC LIMIT n; 

Può usare lo stesso indice.
Ti suggerisco di dichiarare le tue colonne NOT NULL o di familiarizzare con il costrutto NULLS FIRST|LAST :

  • PostgreSQL ordina per datetime asc, null prima?

Nota due cose in particolare:

  1. I valori ROW nella clausola WHERE non possono essere sostituiti con campi membri separati. WHERE (vote, id) > (vote_x, id_x) non può essere sostituito con:

     WHERE vote >= vote_x AND id > id_x 

    Questo escluderebbe tutte le righe con id < = id_x , mentre vogliamo farlo solo per lo stesso voto e non per il prossimo. La traduzione corretta sarebbe:

     WHERE (vote = vote_x AND id > id_x) OR vote > vote_x 

    ... che non funziona bene con gli indici, e diventa sempre più complicato per più colonne.

    Sarebbe semplice per una singola colonna, ovviamente. Questo è il caso speciale che ho menzionato all'inizio.

  2. La tecnica non funziona per le direzioni miste in ORDER BY come:

     ORDER BY vote ASC, id DESC 

    Almeno non riesco a pensare a un modo generico per implementarlo in modo efficiente. Se almeno una delle due colonne è di tipo numerico, è ansible utilizzare un indice funzionale con un valore invertito attivo (vote, (id * -1)) e utilizzare la stessa espressione in ORDER BY :

     ORDER BY vote ASC, (id * -1) ASC 

Relazionato:

  • Termine SQL per 'WHERE (col1, col2) < (val1, val2)'
  • Migliora le prestazioni per ordine con colonne di molte tabelle

Si noti in particolare la presentazione di Markus Winand I legata a:

  • "L'impaginazione ha fatto il modo PostgreSQL"

Hai provato a partionare il tavolo?

Facilità di gestione, scalabilità e disponibilità migliorate e una riduzione del blocco sono i motivi comuni delle tabelle di partizione. Migliorare le prestazioni delle query non è un motivo per utilizzare il partizionamento, anche se in alcuni casi può essere un effetto collaterale vantaggioso. In termini di prestazioni, è importante assicurarsi che il piano di implementazione includa una revisione delle prestazioni delle query. Confermare che gli indici continuino a supportare in modo appropriato le query dopo che la tabella è stata partizionata e verificare che le query che utilizzano gli indici cluster e non cluster traggano vantaggio dall’eliminazione della partizione laddove applicabile.

http://sqlperformance.com/2013/09/sql-indexes/partitioning-benefits