Re: Performances fetchs 3.5M lignes

Bonjour

> C'est mon premier post ici. Alors j'espère que je respecte bien les règles
> d'usage.

oui :)

> ALTER TABLE user_magazines ALTER COLUMN id SET STATISTICS 1000;
> ALTER TABLE user_parutions ALTER COLUMN id SET STATISTICS 1000;

sur une PK c'est inutile et ralenti la phase d'optimisation des
requêtes, que l'on extrait 10 ou 1000 valeurs: elles seront toutes
différentes, dans l'absolu un petit échantillon de 10 valeurs est
suffisant pour une PK, mais 100 est désormais la valeur par défaut,
aucune raison de changer cela ici.

> production=> explain analyze select um.*, up.* from user_parutions up join
> user_magazines um on (um.id=up.user_magazine_id);
>
>  Hash Join  (cost=41033.68..388209.57 rows=3523087 width=256) (actual
> time=1033.790..57197.418 rows=3523087 loops=1)
>    Hash Cond: (up.user_magazine_id = um.id)
>    ->  Seq Scan on user_parutions up  (cost=0.00..118331.87 rows=3523087
> width=158) (actual time=0.010..2334.002 rows=3523087 loops=1)
>    ->  Hash  (cost=24967.30..24967.30 rows=571230 width=98) (actual
> time=1031.240..1031.240 rows=571230 loops=1)
>          Buckets: 1024  Batches: 128  Memory Usage: 609kB
>          ->  Seq Scan on user_magazines um  (cost=0.00..24967.30 rows=571230
> width=98) (actual time=0.006..296.016 rows=571230 loops=1)
>  Total runtime: 58549.933 ms
> (7 lignes)

l'ensemble des lignes des deux tables sont remontés, ce n'est pas
choquant d'avoir des seq scan.
Surtout si les tables sont relativement ordonnées.
Vous pouvez forcer le comportement sans seq scan (juste pour tester,
jamais de ca en prod!):

set enable_seqscan to off;
explain analyze
select um.*, up.*
from user_parutions up
join user_magazines um on (um.id=up.user_magazine_id);

Si les tables ne sont pas ordonnées, un index scan ou un bitmap scan
peut aider en réduisant les tris, à voir, cela dépend de la qualité
des disques, de la mémoire, et des CPU.

>
> Je n'arrive pas à savoir si :
> * 58 secondes pour 3.5M lignes c'est correct (on trouve toujours que c'est
> trop)

c'est pas terrible, dépend du matériel.

> * Si le PLAN est correct car je suis étonné de ne pas le voir utiliser
> d'index.

un index représente plus d'accès disques et des lectures aléatoires.
Un seqscan, ca lit tout, sans avoir besoin de déplacer les tetes de
lectures radicalement entre chaque requete (ou si peu que cela ne se
voit pas).

> C'est un peu frustrant parce que je suis en train de faire des maintenance
> de la base à la recherche de doublons sur des champs texte avec des
> jointures un peu complexes et si cette requête "de base" n'est pas optimale,
> tout le reste en pâtit.

Il y a peut-être des optimisations à faire dans votre recherche de doublons.

> J'ai ajusté les paramètres pour shared_mem dans postgresql.conf en suivant
> les indications de différents sites, je n'ai pas constaté d'améliorations
> notable des performances.

utiliser "explain (analyse on, buffers on) " pour voir les optims
possibles (work_mem, effective cache size, random_page_cost,
cpu_*_cost, ...)

> (Je songe sérieusement à mandater un audit de ce serveur et des bases qu'il
> héberge... à qui je dois m'adresser ?)

Plusieurs sociétés, dont la mienne, 2ndQuadrant.
Voir la liste sur le site officiel de PostgreSQL:
http://www.postgresql.org/support/professional_support/europe/

conseil: cherchez "french" sur la page pour isoler ces dernières plus
facilement.

Vous pouvez aussi regarder
http://wiki.postgresql.org/wiki/Slow_Query_Questions et tenter de
résoudre les soucis de performances par vous-même, le support de la
communauté est très bon (ici comme sur les listes anglophones).

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation