SOL dış birleştirme ilişkilerinde İLİKE sorgusunun optimizasyonu

Question 1

Postgres için oldukça yeni ve burada bir sorun var gibi görünüyor ve hangi yöne gideceğimden emin değilim.

Bazı performans sorunlarına neden olan bir sorgum var ve bunu nasıl optimize edeceğimi çözemiyorum.

Sorgu aslında oldukça basittir:

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

Adına 777 olan bir şirket için olan tüm işlemleri bulmak istiyorum. Veritabanında yaklaşık 20 milyon işlem ve yaklaşık 200 şirket var.

Sorgu şu anda 10'luk bir sınırımız olsa bile zaman aşımına uğruyor. Bunun, İLİKE bir dizin kullanmadığı için korkunç derecede yavaş olduğuna inanıyorum.

Bu, WHERE filtresini BİRLEŞİME eklemek isteyebileceğiniz bir örnek olabilir mi? Bunu test ettim ve bir kayıt bulunursa yıldırım hızında çalışıyor. Kayıt bulunamazsa, tekrar zaman aşımına uğrar.

Bakmamız gereken, buradaki hızı artıracak bir Endeks türü var mı?

Question 2

Şirketler tablosundan herhangi bir sütuna ihtiyacınız olmadığından EXISTS koşulunu deneyebilirsiniz:

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

Ama sonunda, order by muhtemelen buradaki darboğazdır. Örneğin 10 milyon işlem iade edilirse, bu 10 milyon satırın sıralanması biraz zaman alacaktır.

Question 3

Bir EXPLAIN sorgunun yararlı olacaktır. Bununla birlikte, ilike'nin sizi burada darboğaz ettiğini sanmıyorum, aksine işlemler tablosundaki bir tarama. Ardından potansiyel olarak büyük ORDER BY sıralama işlemi

Her ne kadar (bence) sistem bunu otomatik olarak yapıyor olsa da, işlevleri bölelim ve (endeksli) bir temptable kullanarak işlemlerin sırasını manuel olarak zorlayalım.

Bu çizgide bir şey:

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;

Çok az sayıda şirketin 777 gereksinimiyle eşleştiğini varsayarsak, bu, company_id'deki işlemler tablosundaki bir endeksin burada kullanışlı olması gerektiğini ortaya çıkarır.

Dizin eklemek, orijinal sorgunuzu zaten hızlandırabilirse çok şaşırmam çünkü IMHO sistem sorguyu hemen hemen aynı işlemlere böler (eksi idx_matching_companies_id belki)

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

Şirketler tablosundan herhangi bir sütuna ihtiyacınız olmadığından EXISTS koşulunu deneyebilirsiniz:

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

Ama sonunda, order by muhtemelen buradaki darboğazdır. Örneğin 10 milyon işlem iade edilirse, bu 10 milyon satırın sıralanması biraz zaman alacaktır.

Alt sorgu ile benzer davranış. Okuma kopyamda 30 saniye sonra zaman aşımına uğradı. Eğer İLİKE'Yİ bütünüyle kaldırırsam, sistem bana SİPARİŞ ile oldukça hızlı bir şekilde 10 satır döndürür.

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

Bir EXPLAIN sorgunun yararlı olacaktır. Bununla birlikte, ilike'nin sizi burada darboğaz ettiğini sanmıyorum, aksine işlemler tablosundaki bir tarama. Ardından potansiyel olarak büyük ORDER BY sıralama işlemi

Her ne kadar (bence) sistem bunu otomatik olarak yapıyor olsa da, işlevleri bölelim ve (endeksli) bir temptable kullanarak işlemlerin sırasını manuel olarak zorlayalım.

Bu çizgide bir şey:

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;

Çok az sayıda şirketin 777 gereksinimiyle eşleştiğini varsayarsak, bu, company_id'deki işlemler tablosundaki bir endeksin burada kullanışlı olması gerektiğini ortaya çıkarır.

Dizin eklemek, orijinal sorgunuzu zaten hızlandırabilirse çok şaşırmam çünkü IMHO sistem sorguyu hemen hemen aynı işlemlere böler (eksi idx_matching_companies_id belki)

SOL dış birleştirme ilişkilerinde İLİKE sorgusunun optimizasyonu

Soru

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride