Optimalizace ILIKE dotaz na left outer join vztahy

Question 1

Nový postgres a zdá se, že mám problém a nejsem si jistý, jakým směrem jít.

Mám dotaz, který způsobuje některé problémy s výkonem a nemohu přijít na to, jak optimalizovat.

Dotaz je vlastně docela jednoduchý:

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

Chci najít všechny transakce, které jsou pro společnost, která má 777 v názvu. Tam jsou kolem 20million transakce v databázi a kolem 200 firem.

Dotaz v současné době, i když máme LIMIT 10. Věřím, že to je proto, že ILIKE není použití indexu tak, že je to hrozně pomalé.

By to být instance, kde budete chtít přidat filtr, KDE se PŘIPOJIT? Testoval jsem to a funguje to, bleskově rychlé, POKUD záznam je nalezen. Pokud není záznam nalezen znovu krát.

Je nějaký typ Indexu, měli bychom být při pohledu na který by se zlepšit rychlost?

Question 2

Můžete zkusit EXISTUJE stavu, jako nepotřebujete žádné sloupce z tabulky firmy:

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

Ale na konci, order by je pravděpodobně překážkou. Pokud například 10 milionů transakcí jsou vráceny, a pak vytřídění těch 10 milionů řádků bude nějakou dobu trvat.

Question 3

O EXPLAIN dotazu by bylo užitečné. To řekl, nemyslím si, že ILIKE je nedostatků si zde ale spíše skenování na transakce stolu. Následuje potenciálně velký ORDER BY třídění provozu

I když (si myslím) systém už dělá automaticky, pojďme rozdělit funkce a platnost pořadí operací ručně pomocí (indexovány) temptable.

Něco ve smyslu tohoto:

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;

Za předpokladu, několik společností zápas 777-požadavek, to přináší na světlo, že index na transakce stůl na company_id by měla přijít vhod zde.

Já bych neměl být příliš překvapen, pokud jednoduše přidáním indexu již může zrychlit váš původní dotaz, protože IMHO bude systém rozdělen na dotaz v podstatě stejné operace (minus idx_matching_companies_id možná)

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

Můžete zkusit EXISTUJE stavu, jako nepotřebujete žádné sloupce z tabulky firmy:

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

Ale na konci, order by je pravděpodobně překážkou. Pokud například 10 milionů transakcí jsou vráceny, a pak vytřídění těch 10 milionů řádků bude nějakou dobu trvat.

Podobné chování s poddotazem. Krát po dobu 30 sekund na můj přečtěte si repliku. Pokud jsem odstranit ILIKE v plném rozsahu, systém vrátí mi 10 řádků docela rychle s objednávkou.

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

O EXPLAIN dotazu by bylo užitečné. To řekl, nemyslím si, že ILIKE je nedostatků si zde ale spíše skenování na transakce stolu. Následuje potenciálně velký ORDER BY třídění provozu

I když (si myslím) systém už dělá automaticky, pojďme rozdělit funkce a platnost pořadí operací ručně pomocí (indexovány) temptable.

Něco ve smyslu tohoto:

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;

Za předpokladu, několik společností zápas 777-požadavek, to přináší na světlo, že index na transakce stůl na company_id by měla přijít vhod zde.

Já bych neměl být příliš překvapen, pokud jednoduše přidáním indexu již může zrychlit váš původní dotaz, protože IMHO bude systém rozdělen na dotaz v podstatě stejné operace (minus idx_matching_companies_id možná)

Optimalizace ILIKE dotaz na left outer join vztahy

Otázka

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii