Forums PostgreSQL.fr

paftek

Membre

Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

Bonjour,

Je n'arrive pas à expliquer une importante différence de performance sur une requête simple, en fonction de l'évolution du seul paramètre "account_id" :

SELECT * FROM listings_5
WHERE account_id = 981
ORDER BY state ASC
LIMIT 50 OFFSET 0

"listings_5" est une table enfant de "listings". Le découpage est effectuée en fonction de la valeur d'un champ "store_id".
Les colonnes "account_id", "state" et "store_id" sont indexées.

Lorsque je lance la requête ci-dessus directement sur la table "listings_5" pour account_id = 981, j'obtiens ceci :
(la table contient 154 461 enregistrements pour le compte 981)

Limit  (cost=0.00..684.02 rows=50 width=1452) (actual time=238.055..2027.142 rows=50 loops=1)
  ->  Index Scan using listings_5_state_idx on listings_5  (cost=0.00..1960460.66 rows=143305 width=1452) (actual time=238.053..2027.093 rows=50 loops=1)
        Filter: (account_id = 981)
Total runtime: 2027.265 ms

Pour account_id = 1699 :
(alors que la table ne contient aucun enregistrement pour le compte 1699)

Limit  (cost=0.00..17029.71 rows=50 width=1452) (actual time=95497.761..95497.761 rows=0 loops=1)
  ->  Index Scan using listings_5_state_idx on listings_5  (cost=0.00..1960460.66 rows=5756 width=1452) (actual time=95497.759..95497.759 rows=0 loops=1)
        Filter: (account_id = 1699)
Total runtime: 95497.856 ms

En exécutant ces requêtes l'une après l'autre à plusieurs reprises, j'obtiens à peu près le même résultat.

J'ai effectué un VACUUM ANALYSE sur toutes les tables, j'ai redémarré la base, j'ai réindexé les tables listings, sans succès.
De plus je ne constate pas cette différence sur ma seconde machine de test, même matériel, même config, mais qui contient une version plus récente de ma base de prod (donc plus lourde).

Autres précisions :
- Si j'effectue la même requête sur la table mère, en précisant store_id = 5, la tendance s'inverse... (instantané pour 1699, quelques secondes pour 981)
- Si je supprime le critère de tri, le temps de réponse est "normal"...
- Si j'ajoute un critère de tri final (listing_id, la clé primaire), le temps de réponse est "normal" également... (instantané pour 1699, quelques secondes pour 981) :

SELECT * FROM listings_5
WHERE account_id = 1699
ORDER BY state ASC, listing_id
LIMIT 50 OFFSET 0

-------

Limit  (cost=19855.16..19855.28 rows=50 width=1468) (actual time=0.015..0.015 rows=0 loops=1)
  ->  Sort  (cost=19855.16..19869.84 rows=5872 width=1468) (actual time=0.014..0.014 rows=0 loops=1)
        Sort Key: state, listing_id
        Sort Method:  quicksort  Memory: 17kB
        ->  Index Scan using listings_ebay_account_idx on listings_ebay  (cost=0.00..19660.09 rows=5872 width=1468) (actual time=0.008..0.008 rows=0 loops=1)
              Index Cond: (account_id = 1699)
Total runtime: 0.149 ms

Bref, je vous avoue que je suis un peu perdu. Je ne sais pas quelle démarche effectuer pour traquer le problème.
C'est pourquoi je fais appel à vous...

Merci pour tout élément de réponse !

Dernière modification par paftek (08/10/2009 16:44:43)

Marc Cousin

Membre

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

Le problème, c'est que postgres fait le "pari" qu'en prenant par l'index sur state, il va rapidement trouver quelques entrées correspondant à l'account_id demandé, plutôt que de prendre tous les enregistrements sur l'account_id, puis les trier tous par state, et ensuite jeter tout sauf 50.

Deux choses sont à essayer à mon avis :
- D'abord augmenter la précision des statistiques: il y a vraisemblablement une très grosse erreur d'estimation sur la probabilité de trouver des enregistrements ayant account_id =1699. Ça peut se faire globalement en modifiant default_statistics_target, ou localement à une colonne avec un alter table xxx alter column yyy set statistics zzz
Si la valeur est de 10, on peut déjà la monter à 100 pour voir si c'est suffisant. Il faut repasser les statistiques avec un analyze sur la table
- Sinon, donner au moteur un index lui permettant directement d'aller chercher les bonnes données (index composé sur account_id,state). Mais un index supplémentaire a un coût à l'insertion

---

Marc.

paftek

Membre

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

Merci Marc pour ces propositions.

Monter default_statistics_target à 100 ne change rien à mon problème.
Par contre, l'index composé (account_id, state) le résoud (j'ai laissé default_statistics_target à 100, je peux éventuellement essayer de le repasser à 10).

En parallèle, j'ai effectué un VACUUM FULL (hier je me suis contenté d'un ANALYZE) sur ma seconde machine sans ajouter l'index composé et j'obtiens également des performances satisfaisantes.

2 questions me viennent à l'esprit :

- Si je comprends bien l'index sur state me dessert plus qu'autre chose dans ce cas précis, car comme vous le dites PostgreSQL fait le mauvais paris. Simple curiosité, existe-t'il d'autre moyens que l'index composé pour "forcer" l'utilisation d'un index plutôt qu'un autre ? Parce que je m'attendais naturellement à ce qu'il utilise l'index sur account_id, puis state...

- Le démon autovacuum passe régulièrement sur toutes mes tables, je pensais ne plus avoir besoin de VACUUM FULL... Est-ce qu'effectuer régulièrement un VACUUM FULL est une bonne pratique ? Ou alors cela résulte d'une mauvaise configuration ?

Dernière modification par paftek (08/10/2009 16:45:29)

Marc Cousin

Membre

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

- l'index ne dessert pas vraiment. mais dans le cas de la requête, le moteur n'a pas de vraiment bon choix à faire, et doit donc choisir un 'moins pire'. Peut être qu'avec des stats encore plus fines, ça finirait par aller, il aurait fallu voir les estimations du moteur

- le vacuum full ne sert presque plus jamais. sauf dans des cas très particuliers. le passer systèmatique n'est pas une bonne pratique, dans la mesure du possible il vaut mieux essayer de trouver le coupable des modifications entrainant un besoin de vacuum full (l'update de tous les enregistrements d'une table dans une seule transaction par exemple)

---

Marc.

gleu

Administrateur

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

Si un VACUUM FULL est nécessaire, c'est que la maintenance de la base n'est pas faite : soit pas suffisamment de VACUUM, soit une structure FSM pas suffisamment grosse (voir les paramètres max_fsm_pages et max_fsm_relations).

---

Guillaume.

Marc Cousin

Membre

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

T'es injuste… on peut très bien avoir du bloat sur une table en faisant des très mauvaises requêtes, même si la maintenance est bien faite

---

Marc.

paftek

Membre

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

- Ce qui me chiffonne c'est que les VACUUM ANALYSE sont effectués régulièrement sur mes tables par l'autovacuum.
Question bête : les VACUUM ANALYSE lancés par l'autovacuum sont-ils moins "performants" ? Comprendre : agissent-ils différent que ceux lancés manuellement ?

- J'ai l'impression que ma structure FSM est adaptée à mes besoins :
max_fsm_pages = 800000
max_fsm_relations = 1000

Résultat final d'un VACUUM ANALYSE sur l'ensemble de mes données :

INFO:  la structure FSM contient 54193 pages dans 89 relations
DETAIL:  Un total de 55248 emplacements de pages est utilisé (ceci incluant la
surcharge).
55248 emplacements de pages sont requis pour tracer tout l'espace libre.
Les limites actuelles sont : 800000 emplacements de pages, 1000 relations,
utilisant 4752 Ko.
VACUUM

- Pour en revenir au VACUUM FULL, quelles sont ces "très mauvaises requêtes" qui mettraient en défaut une maintenance bien faite ?
La suppression de 10% d'enregistrements sur une table de plus d'un million par exemple ?

Marc Cousin

Membre

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

La FSM a l'air bonne, pas de problème.
Le vacuum et vacuum analyze sont les mêmes dans autovacuum.
Très mauvaises requêtes, c'est des requêtes comme faire un update de 100% de la table en une seule transaction, voire pire (le faire plusieurs fois dans une seule)
Supprimer 10% d'enregistrements n'est pas un problème, même si vacuum risque d'être un peu lent à faire le ménage sur une grosse table derrière.

---

Marc.

gleu

Administrateur

Re : Baisse de performance sur table enfant en utilisant ORDER BY et LIMIT

La FSM a l'air bonne pour une base. Il faut avoir le résultat pour chacune des bases, les additionner, pour savoir si la taille est bonne pour le cluster.

---

Guillaume.