Optimisation de ILIKE interroger sur les relations de jointure externe GAUCHE

Question 1

Assez nouveau pour postgres et semblent avoir un problème ici, et je ne suis pas sûr de la direction à prendre.

J'ai une requête qui est à l'origine de certains problèmes de performances et je ne peux pas travailler sur la façon de l'optimiser.

La requête est en fait assez simple:

SELECT transactions.* FROM transactions
LEFT OUTER JOIN companies ON "companies"."id" = "transactions"."company_id"
WHERE companies.code ILIKE '%777%'
ORDER BY transactions.id desc LIMIT 10

Je veux trouver toutes les opérations qui sont pour une entreprise qui a 777 dans le nom. Il y a environ s'élevant à 20 millions de transactions dans la base de données et à environ 200 entreprises.

La requête en cours des temps, même si nous avons une LIMITE de 10. Je crois que c'est parce que le ILIKE est pas l'utilisation d'un index de sorte qu'il est horriblement lent.

Serait-ce un cas où vous voudrez peut-être ajouter de l'endroit OÙ le filtre à la REJOINDRE à la place? J'ai testé et ça marche ultra-rapide SI un enregistrement est trouvé. Si aucun enregistrement n'est trouvé de nouveau fois sur.

Est-il un type d'Indice, nous devrions être à la recherche à ce qui permettrait d'améliorer la vitesse ici?

Question 2

Vous pouvez essayer un EXISTE condition que vous n'avez pas besoin de toutes les colonnes de la table sociétés:

SELECT tr.* 
FROM transactions tr
WHERE exists (select *  
              from companies c 
              where c.id = tr.company_id 
                and c.code ILIKE '%777%')
ORDER BY tr.id desc 
LIMIT 10

Mais en fin de compte, le order by est probablement le goulot d'étranglement ici. Si par exemple, 10 millions de transactions sont retournés, le tri de ces 10 millions de lignes prendra un certain temps.

Question 3

Un EXPLAIN de la requête serait utile. Cela dit, je ne pense pas que le ILIKE est de goulet d'étranglement vous ici, mais plutôt une analyse sur la table des opérations. Suivie par un large ORDER BY l'opération de tri

Bien que (je pense) le système est déjà faire cela automatiquement, nous allons diviser les fonctionnalités et les force de l'ordre des opérations manuellement à l'aide d'un (indexée) temptable.

Quelque chose le long des lignes de cette:

SELECT id
  INTO TEMPORARY TABLE matching_companies
  FROM companies 
 WHERE companies.code ILIKE '%777%';
 
CREATE UNIQUE INDEX idx_matching_companies_id ON matching_companies (id);

SELECT t.* 
  FROM transactions t
  JOIN matching_companies c
    ON c."id" = t."company_id"
ORDER BY t.id desc LIMIT 10;

En supposant que peu d'entreprises correspondent le 777-exigence, ce qui amène à la lumière d'un index sur la table des opérations sur company_id devraient être utiles ici.

Je ne serais pas trop surpris si simplement en ajoutant de l'index peut déjà augmenter la vitesse de votre requête d'origine, car à mon humble avis, le système va diviser la requête à peu près les mêmes opérations (moins le idx_matching_companies_id peut-être)

a_horse_with_no_name · Answer 1 · 2021-11-17T15:46:23

Vous pouvez essayer un EXISTE condition que vous n'avez pas besoin de toutes les colonnes de la table sociétés:

SELECT tr.* 
FROM transactions tr
WHERE exists (select *  
              from companies c 
              where c.id = tr.company_id 
                and c.code ILIKE '%777%')
ORDER BY tr.id desc 
LIMIT 10

Mais en fin de compte, le order by est probablement le goulot d'étranglement ici. Si par exemple, 10 millions de transactions sont retournés, le tri de ces 10 millions de lignes prendra un certain temps.

Un comportement similaire avec la sous-requête. Après 30 secondes sur mon réplica en lecture. Si je supprime le ILIKE dans son intégralité le système me renvoie 10 lignes assez rapidement avec l'ORDRE PAR.

deroby · Answer 2 · 2021-11-18T13:02:07

Un EXPLAIN de la requête serait utile. Cela dit, je ne pense pas que le ILIKE est de goulet d'étranglement vous ici, mais plutôt une analyse sur la table des opérations. Suivie par un large ORDER BY l'opération de tri

Bien que (je pense) le système est déjà faire cela automatiquement, nous allons diviser les fonctionnalités et les force de l'ordre des opérations manuellement à l'aide d'un (indexée) temptable.

Quelque chose le long des lignes de cette:

SELECT id
  INTO TEMPORARY TABLE matching_companies
  FROM companies 
 WHERE companies.code ILIKE '%777%';
 
CREATE UNIQUE INDEX idx_matching_companies_id ON matching_companies (id);

SELECT t.* 
  FROM transactions t
  JOIN matching_companies c
    ON c."id" = t."company_id"
ORDER BY t.id desc LIMIT 10;

En supposant que peu d'entreprises correspondent le 777-exigence, ce qui amène à la lumière d'un index sur la table des opérations sur company_id devraient être utiles ici.

Je ne serais pas trop surpris si simplement en ajoutant de l'index peut déjà augmenter la vitesse de votre requête d'origine, car à mon humble avis, le système va diviser la requête à peu près les mêmes opérations (moins le idx_matching_companies_id peut-être)

Optimisation de ILIKE interroger sur les relations de jointure externe GAUCHE

La question

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie