Jak przyspieszyć wybór wyraźnego?

Mam prosty wybór odrębny dla niektórych danych szeregów czasowych:

SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';

I zajmuje to 112 sekund. Oto plan zapytań:

http://explain.depesz.com/s/NTyA

Moja aplikacja musi wykonać wiele różnych operacji i tak się liczy. Czy istnieje szybszy sposób na uzyskanie tego rodzaju danych?

Prawdopodobnie nie chcesz tego słyszeć, ale najlepszym sposobem na przyspieszenie SELECT DISTINCTjest unikanie go DISTINCT tego. W wielu przypadkach (nie wszystkie!) Można tego uniknąć dzięki lepszemu projektowaniu bazy danych lub lepszym zapytaniom.

Czasami GROUP BYjest szybszy, ponieważ wymaga innej ścieżki kodu.

W twoim szczególnym przypadku nie wydaje się, że możesz się go pozbyć DISTINCT. Ale możesz obsługiwać zapytanie za pomocą specjalistycznego indeksu, jeśli masz wiele tego rodzaju zapytań:

CREATE INDEX foo ON events (project_id, "time", user_id);

Dodawanie user_idjest przydatne tylko wtedy, gdy otrzymujesz z tego skany tylko indeksowe . Kliknij link, aby uzyskać szczegółowe informacje. Usunąłby kosztowny skan stosów bitmap ze swojego planu zapytań, który zajmuje 90% czasu zapytania.

Twój EXPLAIN wynik mówi mi, że zapytanie musi skondensować 2491 różnych użytkowników z pół miliona pasujących wierszy. Nie stanie się to superszybkie, bez względu na to, co robisz, ale może być znacznie szybsze.

Jeśli przedziały czasowe w twoich zapytaniach są zawsze takie same, MATERIALIIZED VIEWskładanie user_idna per (project_id, <fixed time intervall>)byłoby daleko. Nie ma tam jednak szansy w różnych odstępach czasu. Może mógłbyś co najmniej spasować użytkowników na godzinę lub inną minimalną jednostkę czasu, a to zapewniłoby wystarczającą wydajność, aby zagwarantować znaczne obciążenie.

Nitpick:
Najprawdopodobniej prognozy "time"powinny być naprawdę:

AND "time" >= '2015-01-11 8:00:00'
AND "time" <  '2015-02-10 8:00:00';

Poza:
nie używaj timejako identyfikatora. To słowo zastrzeżone w standardowym języku SQL i podstawowy typ w Postgres.

Oto mój test w sprawie Sama i odpowiedź Erwina

drop table t1
create table t1 (id int, user_id int, project_id int, date_time timestamp without time zone) ;

insert into t1 -- 10 million row - size="498 MB"
select row_number() over(), round(row_number() over()/1000), round(row_number() over()/100000) , date
from generate_series('2015-01-01'::date, '2016-12-01'::date,'6 seconds'::interval
) date 
limit 10000000

-- before indexing - 10000000 row - output=100 row - time=2900ms
SELECT DISTINCT user_id
FROM t1
WHERE project_id = 1
AND date_time > '2015-01-01 8:00:00'
AND date_time < '2016-12-01 8:00:00' ;

CREATE INDEX foo ON t1 (project_id, date_time, user_id); -- time process=51.2 secs -- size="387 MB"         

-- after indexing - 10000000 row - output=100 row - time= 75ms (reduce ~ 38 times)
SELECT DISTINCT user_id
FROM t1
WHERE project_id = 1
AND date_time > '2015-01-01 00:00:00'
AND date_time < '2016-12-01 00:00:00' ;

Erwin powiedział: „Prawdopodobnie nie chcesz tego słyszeć, ale najlepszą opcją na przyspieszenie SELECT DISTINCT jest unikanie DISTINCT na początek. W wielu przypadkach (nie wszystkie!) Można tego uniknąć dzięki lepszemu projektowaniu bazy danych lub lepszym zapytaniom „. Myślę, że ma rację, powinniśmy unikać używania „wyraźnego, grupowania według, sortowania według” (jeśli w ogóle).

Spotkałem sytuację jak w przypadku Sama i myślę, że Sam może użyć partycji na tabeli zdarzeń według miesiąca. Zmniejszy rozmiar danych podczas zapytania, ale potrzebujesz funkcji (pl / pgsql) do wykonania zamiast zapytania powyżej. Funkcja znajdzie odpowiednie partycje (w zależności od warunków) do wykonania zapytania.