Optimisations postgres – index et tables fragmentées

Lors d’un projet, j’ai eu récemment à travailler sur des tables volumineuses, pour lesquelles les requêtes ne donnaient pas les performances attendues. Il semble que postgres gère assez mal les index sur des données fragmentées.

Le contexte

 

Table page :

CREATE TABLE page (
page_cod bigint NOT NULL,
page_url character varying(255),
page_site_cod bigint,
page_titre text,
page_img character varying(255),
page_dmaj timestamp with time zone,
page_img_orig character varying(255),
page_dmaj_img timestamp with time zone,
page_domaine character varying(255)
);
CREATE SEQUENCE page_page_cod_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE page_page_cod_seq OWNED BY page.page_cod;
ALTER TABLE ONLY page ALTER COLUMN page_cod SET DEFAULT nextval('page_page_cod_seq'::regclass);
ALTER TABLE ONLY page ADD CONSTRAINT pk_page_cod PRIMARY KEY (page_cod);

On a donc là une table classique, avec page_cod comme clé primaire, et pas d’autre index.

Table idx_detail :

CREATE TABLE idx_detail (
idd_cod bigint DEFAULT nextval('seq_idd_cod'::regclass) NOT NULL,
idd_idm_cod bigint,
idd_page_cod bigint,
idd_poids integer
);
ALTER TABLE ONLY idx_detail
ADD CONSTRAINT idx_detail_pkey PRIMARY KEY (idd_cod);
CREATE INDEX idx_idd_idm_cod ON idx_detail USING btree (idd_idm_cod);
CREATE INDEX idx_idd_page_cod ON idx_detail USING btree (idd_page_cod);
CREATE INDEX idx_idd_poids ON idx_detail USING btree (idd_poids);
ALTER TABLE ONLY idx_detail
ADD CONSTRAINT idx_detail_idd_page_cod_fkey FOREIGN KEY (idd_page_cod) REFERENCES page(page_cod) ON UPDATE CASCADE ON DELETE CASCADE;

On a une table dont la clé primaire est idd_cod, avec une contrainte de clé étrangère de idd_page_cod sur page.page_cod (en cascade).

La table page contient environ 10 000 lignes, et la table idx_detail contient environ 5 000 000 lignes.

Problématique :

J’ai une requête très récurrente qui est celle-ci :
select page_cod,page_url,page_img,idd_poids,page_titre
from idx_detail,page
where idd_idm_cod = [[variable]]
and idd_page_cod = page_cod
order by idd_poids desc
limit 20;

Lorsque je fais un explain plan de cette requête, j’obtiens ceci :


QUERY PLAN
---------------------------------------------------------------------------------------------------
Limit (cost=2105.71..2105.76 rows=20 width=263)
-> Sort (cost=2105.71..2106.58 rows=350 width=263)
Sort Key: idx_detail.idd_poids
-> Hash Join (cost=797.30..2096.40 rows=350 width=263)
Hash Cond: (idx_detail.idd_page_cod = page.page_cod)
-> Bitmap Heap Scan on idx_detail (cost=7.44..1298.23 rows=350 width=12)
Recheck Cond: (idd_idm_cod = 56086)
-> Bitmap Index Scan on idx_idd_idm_cod (cost=0.00..7.35 rows=350 width=0)
Index Cond: (idd_idm_cod = 56086)
-> Hash (cost=668.27..668.27 rows=9727 width=259)
-> Seq Scan on page (cost=0.00..668.27 rows=9727 width=259)
(11 rows)

Et bien sur, les performances ne sont pas au rendez vous. Cela est du à cette ligne là :

-> Bitmap Heap Scan on idx_detail (cost=7.44..1298.23 rows=350 width=12)

Cela est du au mode de remplissage de la table : elle s’incrémente petit à petit avec les données qui arrivent, et du coup, les données se trouvant dans idd_idm_cod peuvent être très fragmentées. Du coup, postgres est incapable d’utiliser un index (pourtant présent) afin de trier les données en fonction de cette colonne, et ce malgré plusieurs vacuum analyze.

La solution : réorganiser la table

idd_idm_cod est le champ principal de recherche sur cette table. Pour aider postgres à utiliser l’index, on va réorganiser la table. Voici deux méthodes dont le résultat est le même :

Méthode 1 : à l’ancienne

On va créer une table temporaire triée, puis la réinjecter dans la table d’origine.

create table temp as select * from idx_detail order by idd_idm_cod;
truncate table idx_detail;
insert into idx_detail select * from temp;

L’inconvénient majeur de cette méthode est qu’elle ne peut pas se faire en période de production. Il ne faut pas qu’il y ait de modification sur la table idx_detail pendant tout le temps de traitement.

Note : on peut aussi faire un drop de la table idx_detail, puis un rename de la table temp en idx_detail pour gagner du temps. Cette solution est dangereuse car on risque également de faire un drop des séquences associées, et qu’il faudra ensuite penser à recréer tous les index et toutes les contraintes d’intégrité.

 Méthode 2 : en utilisant la fonction cluster

Postgres a une fonction cluster qui permet de réorganiser une table en fonction d’un index. Dans notre cas, il suffit d’utiliser la commande

cluster idx_detail using idx_idd_idm_cod

et la table va se réorganiser. Cette opération est longue, et provoque un lock exclusif de la table. Pendant sa durée, aucune modification ne pourra être apportée à la table, mais elle est moins risquée qu’une réorganisation à l’ancienne, car on ne court pas le risque de données modifiées.

Une fois que cette commande a été lancée au moins une fois, on peut la relancer plus simplement

cluster idx_detail

sans utiliser l’index à utiliser. Postgres va réorganiser la table en utilisant le dernier index qui a été utilisé pour la fonction cluster.

Les résultats

Une fois l’une de ces opérations effectuée, on va relance le explain plan :

QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..446.60 rows=20 width=263)
-> Nested Loop (cost=0.00..242414.94 rows=10856 width=263)
-> Index Scan Backward using idx_idd_poids on idx_detail (cost=0.00..236931.63 rows=10856 width=12)
Filter: (idd_idm_cod = 56086)
-> Index Scan using pk_page_cod on page (cost=0.00..0.49 rows=1 width=259)
Index Cond: (page_cod = idx_detail.idd_page_cod)

Et là, on voit bien qu’on a atteint le résultat escompté 🙂

Le temps de traitement est divisé par plus de 4, sur une requête lancée plusieurs dizaines de fois par minute, cela se ressent énormément sur les performances de l’application.

Conclusion

Cette opération permet d’accélérer notablement les traitements, mais ce n’est pas une solution magique. En effet, quand la table va continuer à se remplir, elle va recommencer à se morceler. Il faudra donc surveiller les performances, et relancer la commande cluster régulièrement pour garder un système réactif.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.