Bir SQL NOT IN sorgusunu daha hızlı yapmanın bir yolu var mı?

I want to get the number of unique mobile phone entries per day that have been logged to a database and have never appeared in the log. I thought it was a trivial query but shock when the query took 10 minutes on a table with about 900K entries. A sample Select is getting the number of unique mobile phones that were logged on the 9th of April 2015 and had never been logged before. Its like getting who are the truly new visitors to you site on a specific day. SQL Fiddle Link

SELECT COUNT(DISTINCT mobile_number)
FROM log_entries
WHERE created_at BETWEEN '2015-04-09 00:00:00'
    AND '2015-04-09 23:59:59'
    AND mobile_number NOT IN (
        SELECT mobile_number
        FROM log_entries
        WHERE created_at < '2015-04-09 00:00:00'
        )

created_at ve mobile_number 'da bireysel dizinlerim var.

Daha hızlı yapmanın bir yolu var mı? Ben çok benzer bir soru görüyorum burada SO ama bu iki tablo ile çalışıyordu.

3
@A_horse_with_no_name SQL için Açıkla, Analiz Et . Bu soruya daha önce gönderilenler için opens.depesz.com/s/tnWG 45 dakika içinde koşuyor!
katma yazar lukik, kaynak
katma yazar lukik, kaynak
@FrankHeikens Sorumumda sorduğum sorgunun açıklama bağlantılarını opens.depesz. com/s/eup9 ve a_horse_with_no_name tarafından opens.depesz.com/s/B64O , doğru olmadıkları takdirde afedersiniz. onu ilk kullandığımda
katma yazar lukik, kaynak
@CraigRinger Ben zaten yaptım? örnek koddan hemen önce
katma yazar lukik, kaynak
Tamam. Ve burada anonimleştirilmiyorlar: CraigRinger'dan: opens.depesz.com/s/NC3 Gönderen a_horse_with_no_name: announc.depesz.com/s/DcZw Orijinal Sorgum: open.depesz.com/s/WNE2 Lütfen bulguları açıklayın
katma yazar lukik, kaynak
@ a_horse_with_no_name ne? 0.5 ms'den daha az sonuç verdi. Şimdi bile doğru olup olmadığından şüpheliyim :-) Belki de çözümünüzü başkalarının veterinere vereceği cevaplara koyabilirsiniz ..
katma yazar lukik, kaynak
0.1ms cinsinden 641K + girişi döndürür
katma yazar lukik, kaynak
@unique_id Tek düzenlemenizin, daha zarif olduğunu kabul ettiğim sözdizimi olduğunu düşünüyorum. Ancak, sorgu çalışıyor ve 7. dakikasında ve sonuç alamadı, bu yüzden sorunu çözeceğini sanmayın
katma yazar lukik, kaynak
NOT EXISTS eş anlamlı alt sorgusunu deneyin
katma yazar a_horse_with_no_name, kaynak
Teşekkürler. Craig'in çözümü için plan ekler misiniz (lütfen "anonimleştirilmiş" seçeneğini işaretlemeyin)
katma yazar a_horse_with_no_name, kaynak
@lukik: her adımın gerçek çalışma zamanlarının görünmesi için yürütme planını oluşturmak için açıklamak yerine açıkla (analiz et, verbose) kullanırsanız iyi olur.
katma yazar a_horse_with_no_name, kaynak
@unique_id: Alt sorgudaki farklı gerekli değildir ve aslında sorguyu yavaşlatır.
katma yazar a_horse_with_no_name, kaynak
mevcut değil performansı artırıyor mu? sqlfiddle.com/#!15/9ee8e/24
katma yazar a_horse_with_no_name, kaynak
Bu gibi sorular için örnek şema ve veriler sağlarsanız gerçekten yardımcı olur. İdeal olarak sqlfiddle.com aracılığıyla
katma yazar Craig Ringer, kaynak
@lukik Gah. Körüm. Afedersiniz
katma yazar Craig Ringer, kaynak
Bir cep telefonu numarasının günlük tablosuna ilk girişinin, cep telefonu numaralarını içeren tabloda ayrı ayrı kaydedilmesi veya başka bir amaç için yeniden tasarlanması için bir durum olup olmadığını merak ediyorum.
katma yazar David Aldridge, kaynak
EXPLAIN ANALYZE'nin sonuçlarını (şimdiki ve yeni sorgunuz için) gösterir misiniz? Sonuçları göndermek için lütfen announc.depesz.com adresini kullanın.
katma yazar Frank Heikens, kaynak
@lukik: EXPLAIN ANALYZE kullanabilir misiniz? ANALYZE olmadan sorgu gerçekleştirilmez ve zamanlama yoktur. Zamanlama olmadan, herhangi bir gelişme göremezsiniz, sadece farklı bir plan. Ve "farklı", "daha iyi" anlamına gelmez
katma yazar Frank Heikens, kaynak
@ a_horse_with_no_name evet kesin !!
katma yazar w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴, kaynak
Bu log_entries'DEN mobile_number'DEN SELECT, ne zamandan beri oluşturulan_adı <'2015-04-09 00:00:00'
katma yazar w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴, kaynak
@lukik NOT IN içindeki SELECT mobile_number yerine SELECT DISTINCT mobile_number 'u da ekledim.
katma yazar w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴, kaynak
@lukik bunu deneyin
katma yazar w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴, kaynak
Not: WHERE created_at, '2015-04-09 00: 00: 00' ve '2015-04-09 23:59:59 arası' yerine WHERE created_at> = '2015- 04-09 00:00:00 'VE oluşturulan_at <=' 2015-04-09 23:59:59 '
katma yazar w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴, kaynak

6 cevap

Bir NOT IN , çok daha hızlı olan bir NOT EXISTS sorgusu olarak yeniden yazılabilir (ne yazık ki Postgres optimizatörü bunu tespit edecek kadar akıllı değildir).

SELECT COUNT(DISTINCT l1.mobile_number) 
FROM log_entries as l1
WHERE l1.created_at >= '2015-04-09 00:00:00' 
  AND l1.created_at <= '2015-04-09 23:59:59' 
  AND NOT EXISTS (SELECT * 
                  FROM log_entries l2
                  WHERE l2.created_at < '2015-04-09 00:00:00'
                    AND l2.mobile_number = l1.mobile_number);

(mobile_number, created_at) üzerindeki bir dizin performansı daha da geliştirmelidir.


A side note: created_at <= '2015-04-09 23:59:59' will not include rows with fractional seconds, e.g. 2015-04-09 23:59:59.789. When dealing with timestamps it's better to use a "lower than" with the "next day" instead of a "lower or equal" with the day in question.

So better use: created_at < '2015-04-10 00:00:00' instead to also "catch" rows on that day with fractional seconds.

4
katma
Bunu beğendim. VAR DEĞİL, anlamsal olarak doğrudur ve anti-semijoin çok hızlıdır.
katma yazar David Aldridge, kaynak

NOT IN 'ı sola birleştirme işlemine dönüştürmeyi önerme eğilimindeyim (yani yalnızca yapmayan sağdaki satırları tutan soldaki birleştirme). Bu durumda, aynı tablonun iki farklı aralığına karşı kendi kendine bir araya gelmesi gerçeği nedeniyle biraz karmaşıktır, bu yüzden gerçekten iki alt sorguya katılıyorsunuz:

SELECT COUNT(n.mobile_number)
FROM (
  SELECT DISTINCT mobile_number
  FROM log_entries
  WHERE created_at BETWEEN '2015-04-09 00:00:00' AND '2015-04-09 23:59:59'
) n
LEFT OUTER JOIN (
  SELECT DISTINCT mobile_number
  FROM log_entries
  WHERE created_at < '2015-04-09 00:00:00'
) o ON (n.mobile_number = o.mobile_number)
WHERE o.mobile_number IS NULL;

@A_horse_with_no_name tarafından sağlanan tipik NOT EXISTS formülasyonuyla karşılaştırıldığında bunun performansıyla ilgileniyorum.

Ayrıca DISTINCT kontrolünü alt sorguya ittiğimi unutmayın.

Your query seems to be "how many newly seen mobile numbers are there in

1
katma
Hata alma [Err] ERROR: "l" tablosu için FROM-yan tümcesi girdisi eksik. LINE 5: NEREDE. >
katma yazar lukik, kaynak
8,63 saniye sürdü. Yukarıdaki açıklamalarda istendiği gibi bir EXPLAIN bağlantısı ekleyerek neler olduğunu görebilirsiniz
katma yazar lukik, kaynak
@DavidAldridge Evet, aynı fikirdeyim - uygun bir dizin varsa, mevcut değil alt sorgusu muhtemelen daha iyi çalışacaktır. Merak ettim, genellikle benzer planlar üreterek ortaya çıktıkları için, ancak farklı burada bir parça kırışıklık yaratıyor.
katma yazar Craig Ringer, kaynak
@lukik Teşekkürler. İç sorgularda farklı ları kaldırmaya ve sayıma geri yüklemeye de değer.
katma yazar Craig Ringer, kaynak
@lukik Oops. Düzenleme hatası. Tamir ediyorum. Sabit. (Bu nedenle, bir sqlfiddle.com 'a sahip olmak sorulara iyi gelir.)
katma yazar Craig Ringer, kaynak
Bu yöntemde bir zayıflık varsa, ikinci alt sorguda erişilmesi muhtemel çok sayıda değerde yatması beklenir, bunun bir log geçmişi taraması gerekir (bunun sadece bir indeks olabilir). İlk alt sorguda birkaç bin satıra erişmek o kadar da kötü değil, ikincisinde milyonlarca/milyar olabilir. Eğer öyleyse ve eğer nispeten ilk alt sorgudan çok az sayıda kayıt döndürülürse, NOT EXISTS formülasyonunun daha iyi çalışmasını beklerdim. Gerçek verilerden kardinalite bilgisi olmadan söylemek çok zor.
katma yazar David Aldridge, kaynak

Böyle bir şey dene:

SELECT mobile_number, min(created_at)
FROM log_entries
GROUP BY mobile_number
HAVING min(created_at) between '2015-04-09 00:00:00' and '2015-04-09 23:59:59'

Hem mobile_number hem de create_at öğesini kapsayan tek bir dizin eklemek, yalnızca bu dizinin taranması gerekeceğinden, tabloda başka sütunlar bulunduğunu varsayarsak performansı biraz artıracaktır.

0
katma
Sorgunuz, seçilen zamanda giriş yapmış ancak 9 Nisan 2015 tarihinden önce masaya girilen cep telefonu numaralarını iade etmemeye dikkat etmekten nasıl geçiyor?
katma yazar lukik, kaynak
Bu 8,5 saniye sürer. Bir gelişme ancak sorunun yorumlarında verilen daha hızlı seçenekler var.
katma yazar lukik, kaynak
Bir cep telefonu numarası 2015-04-01 tarihinde ilk kez görülmüşse, min (created_at) o tarih olacak ve HAVING deyimi hariç tutulacak.
katma yazar TobyLL, kaynak

Isn't WHERE created_at >= '2015-04-09 00:00:00' AND created_at <= '2015-04-09 23:59:59' taking care of WHERE created_at < '2015-04-09 00:00:00'? Am I missing something here?

0
katma
Sorunu açıklık için düzenledim. Lütfen cevap veriyor mu bakın. Belirli bir tarihte giriş yapmış olan cep telefonu numaralarını, daha önce o günlüğe hiç girmemiş olarak almaya çalışıyorum.
katma yazar lukik, kaynak
Performans hakkında eminim, ancak şunu deneyebilirsiniz: SELECT COUNT (DISTINCT mobile_number) log_entries FROM x WHERE created_at> = '2015-04-09 00:00:00' AND created_at <= '2015-04 -09 23:59:59 'VE (log_entries FROM'DAN SELECT sayısı (mobile_number) y NEREDE oluşturulan_ <<2015-04-09 00:00:00' ve x.mobile_number = y.mobile_number) = 0;
katma yazar Phoenix, kaynak

NOT IN hiç hızlı değil. Ve alt sorgunuz birçok tekrar eden kayıt döndürüyor. Belki adanmış tabloya benzersiz numaralar koymalısınız (çünkü GROUP BY de yavaş olacaktır).

0
katma

Try use WITH(if your sql support it). Here is help(postgres):http://www.postgresql.org/docs/current/static/queries-with.html

Sorgunuz şöyle görünmeli:

WITH  b as
(SELECT distinct mobile_number
        FROM log_entries
        WHERE created_at < '2015-04-09 00:00:00') 
SELECT COUNT(DISTINCT a.mobile_number)
FROM log_entries a   
left join b using(mobile_number)
where created_at >= '2015-04-09 00:00:00'
   AND created_at <= '2015-04-09 23:59:59' and b.mobile_number is null;
0
katma
SQL'in NO sonuçları aldığım kadar doğru olduğunu düşünmeyin.
katma yazar lukik, kaynak
Bu sefer kaçtı ve 10.26 ms sürdü. Belki yardımcı olabilecek sorular için yorumlardaki diğer sorgular için açıklama analizini inceleyin
katma yazar lukik, kaynak
bir left join 'a join ' a (bu bir içsel birleşimdir) ihtiyacınız olmaz, aksi halde b.mobile_number'ın boş olması koşulu hiçbir zaman gerçek olmaz.
katma yazar a_horse_with_no_name, kaynak
Evet sol katılmak;) benim hatam :)
katma yazar Wiol, kaynak