Jak przyspieszyć DISTINCT ON w PostgreSQL?

Mam tabelę station_logsw bazie danych PostgreSQL 9.6:

    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 submitted_at  | timestamp without time zone | 
 level_sensor  | double precision            | 
Indexes:
    "station_logs_pkey" PRIMARY KEY, btree (id)
    "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

Próbuję uzyskać ostatnią level_sensorwartość na podstawiesubmitted_at dla każdego station_id. Istnieje około 400 unikalnych station_idwartości i około 20 000 wierszy dziennie station_id.

Przed utworzeniem indeksu:

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

 Unikalny (koszt = 4347852.14..4450301.72 wierszy = 89 szerokość = 20) (czas rzeczywisty = 22202.080..27619.167 wierszy = 98 pętli = 1)
   -> Sortuj (koszt = 4347852.14..4399076.93 wierszy = 20489916 szerokość = 20) (rzeczywisty czas = 22202.077..26540.827 wierszy = 20489812 pętli = 1)
         Klucz sortowania: identyfikator stacji, przesłany na DESC
         Metoda sortowania: scalanie zewnętrzne Dysk: 681040kB
         -> Seq Scan na logach stacji (koszt = 0,00..598895.16 wierszy = 20489916 szerokość = 20) (rzeczywisty czas = 0,023..3443,587 wierszy = 20489812 pętli = $
 Czas planowania: 0,072 ms
 Czas wykonania: 27690,644 ms

Tworzenie indeksu:

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

Po utworzeniu indeksu dla tego samego zapytania:

 Unikalny (koszt = 0,56..2156367,51 wierszy = 89 szerokość = 20) (rzeczywisty czas = 0,184..16263,413 wierszy = 98 pętli = 1)
   -> Skanowanie indeksu za pomocą id_partycji_przesłane_ na logach stacji (koszt = 0,56..2105142,98 wierszy = 20489812 szerokość = 20) (czas rzeczywisty = 0,181..1 $
 Czas planowania: 0,206 ms
 Czas wykonania: 16263,490 ms

Czy istnieje sposób na przyspieszenie tego zapytania? Na przykład 1 sekunda, 16 sekund to wciąż za dużo.

W przypadku tylko 400 stacji zapytanie to będzie znacznie szybsze:

SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
   SELECT submitted_at, level_sensor
   FROM   station_logs
   WHERE  station_id = s.station_id
   ORDER  BY submitted_at DESC NULLS LAST
   LIMIT  1
   ) l;

dbfiddle tutaj
_{(porównując plany dla tego zapytania, alternatywa Abelisto i twoje oryginalne)}

Wynikające z EXPLAIN ANALYZEdostarczonych przez PO:

 Zagnieżdżona pętla (koszt = 0,56..356,65 wierszy = 102 szerokość = 20) (rzeczywisty czas = 0,034..0,979 wierszy = 98 pętli = 1)
   -> Seq Scan na stacjach s (koszt = 0,00..3,02 wierszy = 102 szerokość = 4) (czas rzeczywisty = 0,009..0,016 wierszy = 102 pętle = 1)
   -> Limit (koszt = 0,56..3,45 wiersza = 1 szerokość = 16) (rzeczywisty czas = 0,009..0,009 wierszy = 1 pętla = 102)
         -> Skanowanie indeksu za pomocą id_partycji_przesłane_ na logach stacji (koszt = 0,56..664062,38 wierszy = 230223 szerokość = 16) (czas rzeczywisty = 0,009 $
               Indeks Cond: (station_id = s.id)
 Czas planowania: 0,542 ms
 Czas wykonania: 1,013 ms   - !!

Jedyny wskaźnik potrzebne jest utworzony jeden: station_id__submitted_at. Zasadniczo UNIQUEograniczenie uniq_sid_satrównież działa. Utrzymanie obu wydaje się marnowaniem miejsca na dysku i wydajnością zapisu.

Dodałem NULLS LASTdo ORDER BYzapytania, ponieważ submitted_atnie jest zdefiniowane NOT NULL. Idealnie, jeśli ma to zastosowanie !, dodaj NOT NULLograniczenie do kolumny submitted_at, usuń dodatkowy indeks i usuń NULLS LASTz zapytania.

Jeśli submitted_atto możliwe NULL, utwórz ten UNIQUEindeks, aby zastąpić zarówno bieżący indeks, jak i unikalne ograniczenie:

CREATE UNIQUE INDEX station_logs_uni ON station_logs(station_id, submitted_at DESC NULLS LAST);

Rozważać:

• Czy utworzenie unikalnego ograniczenia dla kolumny Postgres eliminuje potrzebę indeksowania?
• W jaki sposób PostgreSQL wymusza ograniczenie UNIQUE / jakiego typu indeks używa?

Zakłada się oddzielną tabelęstation z jednym wierszem na odpowiedni station_id(zwykle PK) - co powinieneś mieć w obu przypadkach. Jeśli go nie masz, utwórz go. Ponownie, bardzo szybko dzięki tej technice rCTE:

CREATE TABLE station AS
WITH RECURSIVE cte AS (
   (
   SELECT station_id
   FROM   station_logs
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT l.station_id
   FROM   cte c
   ,      LATERAL (   
      SELECT station_id
      FROM   station_logs
      WHERE  station_id > c.station_id
      ORDER  BY station_id
      LIMIT  1
      ) l
   )
TABLE cte;

Używam tego również w skrzypcach. Możesz użyć podobnego zapytania, aby rozwiązać swoje zadanie bezpośrednio, bez stationtabeli - jeśli nie możesz przekonać się do jego utworzenia.

Szczegółowe instrukcje, wyjaśnienia i alternatywy:

• Zoptymalizuj zapytanie GROUP BY, aby pobrać najnowszy rekord na użytkownika
• Wybierz pierwszy wiersz w każdej grupie GROUP BY?

Zoptymalizuj indeks

Twoje zapytanie powinno być teraz bardzo szybkie. Tylko jeśli nadal musisz zoptymalizować wydajność odczytu ...

Warto dodać level_sensorjako ostatnią kolumnę do indeksu, aby umożliwić skanowanie tylko indeksu , tak jak komentował joanolo .
Przeciw: zwiększa indeks - co powoduje niewielkie koszty dla wszystkich zapytań, które go wykorzystują.
Pro: Jeśli faktycznie skanujesz z niego tylko indeksy, zapytanie w ogóle nie musi odwiedzać stron sterty, co czyni je około dwa razy szybszym. Ale może to być nieistotna korzyść dla bardzo szybkiego zapytania.

Jednak nie oczekuję, że zadziała w twojej sprawie. Wspomniałeś:

... około 20 000 wierszy dziennie station_id.

Zazwyczaj oznaczałoby to ciągłe obciążenie zapisu (1 na station_id5 sekund). I jesteś zainteresowany najnowszym wierszem. Skanowania tylko za pomocą indeksu działają tylko w przypadku stron sterty, które są widoczne dla wszystkich transakcji (bit w mapie widoczności jest ustawiony). Trzeba będzie uruchomić bardzo agresywne VACUUMustawienia, aby tabela nadążyła za obciążeniem zapisu, i nadal nie będzie działać przez większość czasu. Jeśli moje założenia są prawidłowe, skanowanie tylko do indeksu jest wyłączone, nie dodawaj level_sensordo indeksu.

OTOH, jeśli moje założenia przytrzymaj i tabela rośnie bardzo duży , a indeks BRIN może pomóc. Związane z:

• Przyspiesz tworzenie częściowego indeksu Postgres

Lub jeszcze bardziej wyspecjalizowany i bardziej wydajny: częściowy indeks tylko najnowszych dodatków, aby odciąć większość niepotrzebnych wierszy:

CREATE INDEX station_id__submitted_at_recent_idx ON station_logs(station_id, submitted_at DESC NULLS LAST)
WHERE submitted_at > '2017-06-24 00:00';

Wybierz znacznik czasu, dla którego wiesz, że muszą istnieć młodsze wiersze. Musisz dodać pasujący WHEREwarunek do wszystkich zapytań, na przykład:

...
WHERE  station_id = s.station_id
AND    submitted_at > '2017-06-24 00:00'
...

Od czasu do czasu musisz dostosowywać indeks i zapytania.
Powiązane odpowiedzi z dodatkowymi szczegółami:

• Optymalizacja indeksu z datami
• Dodaj ograniczenie daty i godziny do wielokolumnowego indeksu częściowego PostgreSQL
• Zindeksowane ORDER BY z LIMITEM 1

Wypróbuj klasyczny sposób:

create index idx_station_logs__station_id on station_logs(station_id);
create index idx_station_logs__submitted_at on station_logs(submitted_at);

analyse station_logs;

with t as (
  select station_id, max(submitted_at) submitted_at 
  from station_logs 
  group by station_id)
select * 
from t join station_logs l on (
  l.station_id = t.station_id and l.submitted_at = t.submitted_at);

dbfiddle

WYJAŚNIJ ANALIZĘ od ThreadStarter

 Nested Loop  (cost=701344.63..702110.58 rows=4 width=155) (actual time=6253.062..6253.544 rows=98 loops=1)
   CTE t
     ->  HashAggregate  (cost=701343.18..701344.07 rows=89 width=12) (actual time=6253.042..6253.069 rows=98 loops=1)
           Group Key: station_logs.station_id
           ->  Seq Scan on station_logs  (cost=0.00..598894.12 rows=20489812 width=12) (actual time=0.034..1841.848 rows=20489812 loop$
   ->  CTE Scan on t  (cost=0.00..1.78 rows=89 width=12) (actual time=6253.047..6253.085 rows=98 loops=1)
   ->  Index Scan using station_id__submitted_at on station_logs l  (cost=0.56..8.58 rows=1 width=143) (actual time=0.004..0.004 rows=$
         Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
 Planning time: 0.542 ms
 Execution time: 6253.701 ms