Requête lente avec une jointure externe gauche et une condition nulle.

Question

J'ai une requête simple (postgresql si cela compte) qui récupère tous les articles d'un utilisateur, à l'exception de ceux qui figurent sur sa liste de souhaits. :

select i.* 
from core_item i 
left outer join core_item_in_basket b on (i.id=b.item_id and b.user_id=__some_user__)
where b.on_wishlist is null;

La requête ci-dessus s'exécute en ~50000ms (oui, le chiffre est correct). Si je supprime la condition "b.on_wishlist is null" ou si je la transforme en "b.on_wishlist is not null", la requête s'exécute en 50 ms environ (un sacré changement).

La requête comporte d'autres jointures et conditions, mais cela n'a pas d'importance car seule celle-ci ralentit la requête.

Quelques informations sur la taille de la base de données :

• core_items a ~ 10.000 enregistrements
• core_user a ~5.000 enregistrements
• core_item_in_basket a ~2.000
• (dont environ 50 % ont on_wishlist = true, le reste est nul).

Je n'ai pas d'index (sauf pour les identifiants et les clés étrangères) sur ces deux tables.

La question est la suivante : que dois-je faire pour que ça aille plus vite ? J'ai moi-même quelques idées à vérifier ce soir, mais j'aimerais que vous m'aidiez aussi, si possible.

Merci !

Answer 1

Essayez d'utiliser not exists :

select i.* 
from   core_item i 
where  not exists (select * from core_item_in_basket b where i.id=b.item_id and b.user_id=__some_user__)

Answer 2

Désolé d'avoir ajouté une deuxième réponse, mais stackoverflow ne me laisse pas formater les commentaires correctement, et comme le formatage est essentiel, je dois poster la réponse.

Deux options :

1. CREATE INDEX q ON core_item_in_basket (user_id, item_id) WHERE on_wishlist is null ;
2. même index, mais changer l'ordre des colonnes dans celui-ci.
3. SELECT i.* FROM core_item i WHERE i.id not in (select item_id FROM core_item_in_basket WHERE on_wishlist is null AND user_id = __some_user__) ; (cette requête peut bénéficier de l'index du point #1, mais ne bénéficiera pas de l'index #2.
4. SELECT * from core_item where id in (select id from core_item EXCEPT select item_id FROM core_item_in_basket WHERE on_wishlist is null AND user_id = __some_user__) ;

Faites-nous part des résultats :)

Answer 3

Vous pourriez vouloir expliquer davantage l'objectif de cette requête, car certaines techniques ont un sens et d'autres non, selon le cas d'utilisation.

A quelle fréquence l'utilisez-vous ?

Est-il exécuté pour un seul utilisateur, ou pour tous les utilisateurs dans une sorte de boucle ?

Faire : expliquer analyser et mettre la sortie sur expliquer.depesz.com donc vous verrez pourquoi c'est si lent.

Answer 4

Avez-vous essayé d'ajouter un indice en on_wishlist ?

Il semble que cette colonne doive être vérifiée pour chaque ligne de la requête. Si vos tables sont aussi grandes, cela peut avoir un impact significatif sur la vitesse de la requête.

Comme vous mettez le on_wishlist dans le where qui la fera évaluer (en fonction de ce que le planificateur de requêtes décide) après l'exécution de la jointure, cette comparaison doit être effectuée pour potentiellement chaque ligne résultant de la jointure. Les deux clauses core_items y core_item_in_basket sont assez grandes et vous n'avez pas d'index pour cette colonne. L'optimiseur de requêtes n'a donc pas grand-chose à faire, ce qui explique probablement le temps de requête excessif.

La taille de core_user ne devrait avoir aucune influence (puisqu'il n'est pas référencé dans la requête).