Optymalizacja zapytań w zakresie znaczników czasu (dwie kolumny)

Używam PostgreSQL 9.1 na Ubuntu 12.04.

Muszę wybrać rekordy w określonym przedziale czasu: moja tabela time_limitsma dwa timestamppola i jedną integerwłaściwość. W mojej rzeczywistej tabeli znajdują się dodatkowe kolumny, które nie są związane z tym zapytaniem.

create table (
   start_date_time timestamp,
   end_date_time timestamp, 
   id_phi integer, 
   primary key(start_date_time, end_date_time,id_phi);

Ta tabela zawiera około 2 mln rekordów.

Zapytania takie jak poniższe zajmowały dużo czasu:

select * from time_limits as t 
where t.id_phi=0 
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

Próbowałem więc dodać kolejny indeks - odwrotność PK:

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

Mam wrażenie, że poprawiła się wydajność: Czas na dostęp do rekordów na środku tabeli wydaje się bardziej rozsądny: gdzieś pomiędzy 40 a 90 sekund.

Ale wciąż jest kilkadziesiąt sekund dla wartości w środku zakresu czasu. I jeszcze dwa razy, gdy celujesz w koniec tabeli (chronologicznie).

Po explain analyzeraz pierwszy próbowałem uzyskać ten plan zapytań:

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

Zobacz wyniki na depesz.com.

Co mogę zrobić, aby zoptymalizować wyszukiwanie? Widać cały czas spędzony na skanowaniu dwóch kolumn znaczników czasu po id_phiustawieniu na 0. I nie rozumiem dużego skanu (60 000 wierszy!) Znaczników czasu. Czy nie są one indeksowane według klucza podstawowego, a idx_inversedja dodałem?

Czy powinienem zmienić typy znaczników czasu na coś innego?

Przeczytałem trochę o indeksach GIST i GIN. Rozumiem, że mogą być bardziej wydajne w określonych warunkach dla niestandardowych typów. Czy jest to opłacalna opcja dla mojego przypadku użycia?

W przypadku Postgres 9.1 lub nowszego:

CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);

W większości przypadków kolejność sortowania indeksu jest mało istotna. Postgres może skanować wstecz praktycznie tak szybko. Ale w przypadku zapytań o zakres w wielu kolumnach może to mieć ogromną różnicę. Blisko związane:

• Indeks PostgreSQL nie jest używany dla zapytania dotyczącego zakresu

Rozważ swoje zapytanie:

SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

Kolejność sortowania pierwszej kolumny id_phiw indeksie jest nieistotna. Ponieważ jest sprawdzane pod kątem równości ( =), powinno być na pierwszym miejscu. Dobrze rozumiesz. Więcej w tej pokrewnej odpowiedzi:

• Indeks i wydajność wielokolumna

Postgres może szybko wskoczyć id_phi = 0i rozważyć następujące dwie kolumny pasującego indeksu. Są one pytane o warunki zakresu odwróconej kolejności sortowania ( <=, >=). W moim indeksie wiersze kwalifikujące są na pierwszym miejscu. Powinno być najszybszym możliwym sposobem z indeksem B-Tree ¹ :

• Chcesz start_date_time <= something: indeks ma najwcześniejszy znacznik czasu.
  • Jeśli się kwalifikuje, sprawdź także kolumnę 3.
    Powtarzaj, dopóki pierwszy rząd nie zakwalifikuje się (superszybko).
• Chcesz end_date_time >= something: indeks ma najpierw najnowszą sygnaturę czasową.
  • Jeśli się kwalifikuje, pobieraj wiersze, dopóki pierwszy nie (superszybko).
    Kontynuuj od następnej wartości dla kolumny 2 ..

Postgres może skanować do przodu lub do tyłu. Sposób, w jaki miałeś indeks, musi odczytać wszystkie wiersze pasujące do pierwszych dwóch kolumn, a następnie odfiltrować trzeci. Przeczytaj rozdział Indeksy iORDER BY instrukcję. Całkiem dobrze pasuje do twojego pytania.

Ile wierszy pasuje do pierwszych dwóch kolumn?
Tylko nieliczne z start_date_timepoczątkiem przedziału czasowego tabeli. Ale prawie wszystkie rzędy id_phi = 0na chronologicznym końcu tabeli! Tak więc wydajność pogarsza się z późniejszymi czasami uruchamiania.

Szacunki planisty

Planista szacuje rows=62682dla przykładowego zapytania. Spośród nich żaden się nie kwalifikuje ( rows=0). Możesz uzyskać lepsze oszacowania, jeśli zwiększysz docelowy poziom statystyki dla tabeli. Dla 2.000.000 wierszy ...

ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

... może zapłacić. Lub nawet wyżej. Więcej w tej pokrewnej odpowiedzi:

• Sprawdź docelowe statystyki w PostgreSQL

Myślę, że nie potrzebujesz tego id_phi(tylko dla kilku odrębnych wartości, równomiernie rozmieszczonych), ale dla znaczników czasu (wiele różnych wartości, nierównomiernie rozmieszczonych).
Nie sądzę też, żeby miało to znaczenie dla ulepszonego indeksu.

CLUSTER / pg_repack

Jeśli chcesz tego szybciej, możesz usprawnić fizyczną kolejność wierszy w tabeli. Jeśli możesz sobie pozwolić na zablokowanie tabeli wyłącznie na krótki czas (na przykład poza godzinami pracy), aby przepisać tabelę i uporządkować wiersze zgodnie z indeksem:

ALTER TABLE time_limits CLUSTER ON idx_time_limits_inversed;

Przy równoczesnym dostępie rozważ pg_repack , który może zrobić to samo bez wyłącznej blokady.

Tak czy inaczej, efektem jest to, że mniej tabel musi być odczytanych z tabeli i wszystko jest wstępnie posortowane. Jest to jednorazowy efekt pogarszający się z czasem, gdy zapisy na stole fragmentują fizyczny porządek sortowania.

Indeks GiST w Postgresie 9.2+

¹ W przypadku pg 9.2+ istnieje inna, prawdopodobnie szybsza opcja: indeks GiST dla kolumny zakresu.

• Istnieją wbudowane typy zakresów dla timestampi timestamp with time zone: tsrange,tstzrange . Indeks btree jest zwykle szybszy dla dodatkowej integerkolumny, takiej jak id_phi. Mniejszy i tańszy w utrzymaniu. Ale zapytanie będzie prawdopodobnie ogólnie szybsze z połączonym indeksem.

• Zmień definicję tabeli lub użyj indeksu wyrażeń .

• W przypadku dostępnego wielokolumnowego indeksu GiST należy również btree_gistzainstalować dodatkowy moduł (jeden raz na bazę danych), który zapewnia klasy operatorów do włączenia integer.

Trifecta! Wielokolumnowa funkcjonalnego indeksu Gist- :

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

W zapytaniu użyj operatora „zawiera zakres”@> :

SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

Indeks SP-GiST w Postgres 9.3+

SP-GIST wskaźnik może być jeszcze szybciej do tego rodzaju zapytania - chyba że, cytując instrukcję :

Obecnie tylko typy indeksów B-drzewa, GiST, GIN i BRIN obsługują indeksy wielokolumnowe.

Nadal jest to prawda w Postgres 12.
Musisz połączyć spgistindeks tylko (tsrange(...))z drugim btreeindeksem (id_phi). Po dodaniu kosztów ogólnych nie jestem pewien, czy to może konkurować.
Powiązana odpowiedź z testem porównawczym tylko dla tsrangekolumny:

• Wykonaj te godziny zapytania operacji w PostgreSQL

Odpowiedź Erwina jest już wyczerpująca, jednak:

Typy zakresów znaczników czasu są dostępne w PostgreSQL 9.1 z rozszerzeniem Temporal autorstwa Jeffa Davisa: https://github.com/jeff-davis/PostgreSQL-Temporal

Uwaga: ma ograniczone funkcje (używa Timestamptz, a afaik może nakładać się tylko na styl „[)”). Ponadto istnieje wiele innych wspaniałych powodów do uaktualnienia do PostgreSQL 9.2.

Możesz spróbować utworzyć indeks wielokolumnowy w innej kolejności:

primary key(id_phi, start_date_time,end_date_time);

Kiedyś zamieściłem podobne pytanie związane również z porządkowaniem indeksów w indeksie wielokolumnowym. Klucz próbuje użyć najbardziej restrykcyjnych warunków w celu zmniejszenia przestrzeni wyszukiwania.

Edycja : Mój błąd. Teraz widzę, że masz już ten indeks zdefiniowany.

Udało mi się szybko zwiększyć (z 1 sekundy do 70 ms)

Mam tabelę z agregacjami wielu pomiarów i wielu poziomów ( lkolumna) (30s, 1m, 1h itp.) Istnieją dwie kolumny związane z zakresem: $sna początek i $ena koniec.

Utworzyłem dwa indeksy wielokolumnowe: jeden dla początku i jeden dla końca.

Dostosowałem zapytanie: wybierz zakresy, w których ich początkowa granica znajduje się w danym zakresie. dodatkowo wybierz zakresy, w których ich koniec jest w danym zakresie.

Wyjaśnienie pokazuje dwa strumienie wierszy skutecznie wykorzystujących nasze indeksy.

Indeksy:

drop index if exists agg_search_a;
CREATE INDEX agg_search_a
ON agg (measurement_id, l, "$s");

drop index if exists agg_search_b;
CREATE INDEX agg_search_b
ON agg (measurement_id, l, "$e");

Wybierz zapytanie:

select "$s", "$e", a, t, b, c from agg
where 
    measurement_id=0 
    and l =  '30s'
    and (
        (
            "$s" > '2013-05-01 02:05:05'
            and "$s" < '2013-05-01 02:18:15'
        )
        or 
        (
             "$e" > '2013-05-01 02:00:05'
            and "$e" < '2013-05-01 02:18:05'
        )
    )

;

Wyjaśnić:

[
  {
    "Execution Time": 0.058,
    "Planning Time": 0.112,
    "Plan": {
      "Startup Cost": 10.18,
      "Rows Removed by Index Recheck": 0,
      "Actual Rows": 37,
      "Plans": [
    {
      "Startup Cost": 10.18,
      "Actual Rows": 0,
      "Plans": [
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 26,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone))",
          "Plan Rows": 29,
          "Parallel Aware": false,
          "Actual Total Time": 0.016,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.016,
          "Total Cost": 5,
          "Actual Loops": 1,
          "Index Name": "agg_search_a"
        },
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 36,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone))",
          "Plan Rows": 39,
          "Parallel Aware": false,
          "Actual Total Time": 0.011,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.011,
          "Total Cost": 5.15,
          "Actual Loops": 1,
          "Index Name": "agg_search_b"
        }
      ],
      "Node Type": "BitmapOr",
      "Plan Rows": 68,
      "Parallel Aware": false,
      "Actual Total Time": 0.027,
      "Parent Relationship": "Outer",
      "Actual Startup Time": 0.027,
      "Plan Width": 0,
      "Actual Loops": 1,
      "Total Cost": 10.18
    }
      ],
      "Exact Heap Blocks": 1,
      "Node Type": "Bitmap Heap Scan",
      "Plan Rows": 68,
      "Relation Name": "agg",
      "Alias": "agg",
      "Parallel Aware": false,
      "Actual Total Time": 0.037,
      "Recheck Cond": "(((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone)) OR ((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone)))",
      "Lossy Heap Blocks": 0,
      "Actual Startup Time": 0.033,
      "Plan Width": 44,
      "Actual Loops": 1,
      "Total Cost": 280.95
    },
    "Triggers": []
  }
]

Sztuka polega na tym, że twoje węzły planu zawierają tylko pożądane wiersze. Wcześniej mieliśmy tysiące wierszy w węźle planu, ponieważ został on wybrany all points from some point in time to the very end, a następnie następny węzeł usunął niepotrzebne wiersze.