Bir PostgreSQL veritabanı için bir sorgu optimize yardım

3 Cevap php

Ben büyük bir veri grubunu almak için kullanıyorum bir sorgu optimize bazı önerilerde bulmaya çalışıyorum.

Ben üzerinde çalışıyorum orijinal kod kullanıcıların büyük bir set üzerinden döngüye, ve bunların her biri için bir tarih aralığı hesaplandı. Daha sonra bu tarih aralığını almak ve onlar yanıtladı ve kaç o tarih aralığında doğru var kaç soru sorgulamak istiyorum. Bu sonuçlar yukarı sayımı, ve ihtiyacımız olan son tallies olduğunu edildi.

Aksine tek tek her kullanıcı sorgulama yerine, komut artık sadece onlara geçerli olacaktır tarih aralığını (diğer tüm yönlerini hesaplar her kullanıcı aracılığıyla döngüsü: Ben zaten (birkaç dakika alıyor çünkü) bu hızlandırmak için ne yaptık bu sorgu) her kullanıcı için aynıdır. Bu veri bir 3d dizi [startDate] [endDate] [userid], ve tüm kullanıcılar o operasyomo yapmak için inşa edilmiş tek bir sorgu toplanır. İşte çıkışını alır sorgu bir örnek:


SELECT COUNT(uapl.id) AS numAnswered,
SUM(CASE WHEN (a.correct OR q.survey OR uapl.answersId IS NULL) THEN 1 ELSE 0 END) AS numCorrect
FROM usersAnswersProgramsLink uapl
JOIN questions q ON uapl.questionsId=q.id
LEFT JOIN answers a ON uapl.answersId=a.id
WHERE
programsId=123
AND
(
  (
    CAST(timestamp AS date) >= '2009-09-01'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('123','234','345','465','567')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-10'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('321','432','543')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-16'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('987','876')
  )
)

Bu kod hızlandırmak oldukça iyi çalışır. Ben bu çalıştırmak testlerin çoğu, şimdi sürece% 20 ve% 10 arasında sürer. Ama benim kötü durumda olan, sadece% 50, ve ben o geliştirmek istiyorum.

Ben kullanıcı kimliği (on-thousandish) karşılaştırmak için büyük bir dizi var o zaman en kötü durum olur. Sorun şimdi dışarı bu sorguları çıkardı algoritması üzerinde yapılması gereken optimize artık olmasıdır. Şimdi milisaniye gider. Bu uzun zaman alır, bu sorgu var.

Yani benim bilmece. Ben daha hızlandırmak istiyorum. Herhangi bir öneriniz hoş olurdu. Burada ilgili olan bilgi parçalarının bir çift:

1) There is a 1 to many relationship between the date ranges and the users. none of those user id's will show up in multiple date ranges. 2) The end result we're looking for is just those tallies, but the date ranges need to be calculated on a per-user basis, hence the array of id's per date range.

Ben ~ ~ kudreti daha hızlı bir sandık için tarih aralığı için bir sütun ve kullanıcı id adlı bir sütun ile bir geçici tablo olacağını yapmak düşündüğüm bir şey. Sonra bu tabloya JOIN kullanarak yerine sorguda kendisini bu numaraları koyarak bu sorguyu yeniden yazın. Bunun işe yarayacağını biliyor mu?

Herhangi bir öneriniz için teşekkür ederiz!

3 Cevap

önce de belirttiğim gibi: yardım etmek için zor olacağını olmadan, EXPLAIN ANALYZE <query> yanı sıra tablo yapıları ve yaratılan indeksler sonucu verin

timestamp::date yardımcı olabilecek bir indeks (zaman damgası üzerine bir dizin çünkü döküm kullanılan olmaz)

Ayrıca yürütme planında sorunlu yerleri vurgulamak hangi http://explain.depesz.com/ içine explain analyze çıkışı sonrası olabilir

One thing that I thought ~might~ make it faster would be to crate a temporary table with a column for the date range and a column for the user id's. Then rewrite that query using a JOIN to that table rather than putting those numbers in the query itself. Does anyone know if that would work?

That would be the approach I would take. It will also make the query clearer. You can add indexes to the temp table too, though you should do this after filling it with data. Don't assume you need an index though - test.

Oh - damgaları yerine tarihleri ​​(bu döküm tasarruf edeceğiz) ve cevaplar tabloda "timestamp" sütununda belki bir dizin saklamak isteyebilirsiniz.

PS - genellikle sütunlar gibi aynı isim değil daha iyi kabul yerleşik türleri. Veritabanı insan okuyucu kutu karışık almaz bile.

Öncelikle, ben seni usercontextid ve timestamp üzerindeki dizinleri kullanmak bir kaba filtre eklerim öneririm:

SELECT  COUNT(uapl.id) AS numAnswered,
        SUM(CASE WHEN (a.correct OR q.survey OR uapl.answersId IS NULL) THEN 1 ELSE 0 END) AS numCorrect
FROM    questions q
JOIN    usersAnswersProgramsLink uapl
ON      uapl.questionsId = q.id
LEFT JOIN
        answers a
ON      a.id = uapl.answersId
WHERE   programsId=123
        AND timestamp >= '2009-09-01'
        AND timestamp < '2009-09-22'
        AND usercontextid IN (/* all possible values here */)
        AND 
(
  (
    CAST(timestamp AS date) >= '2009-09-01'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('123','234','345','465','567')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-10'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('321','432','543')
  )
  OR
  (
    CAST(timestamp AS date) >= '2009-09-16'
    AND CAST(timestamp AS date) <= '2009-09-21'
    AND usercontextid in('987','876')
  )
)

Ayrıca tüm bu saha ait yapmak hangi tabloları açıklamak gerekir.

etiketler