Indeksy dla zapytania SQL z warunkiem GDZIE i GROUP BY

Próbuję ustalić, które indeksy mają być używane w zapytaniu SQL z WHEREwarunkiem, a GROUP BYktóry działa obecnie bardzo wolno.

Moje zapytanie:

SELECT group_id
FROM counter
WHERE ts between timestamp '2014-03-02 00:00:00.0' and timestamp '2014-03-05 12:00:00.0'
GROUP BY group_id

Tabela ma obecnie 32 000 000 wierszy. Czas wykonania zapytania znacznie wzrasta, gdy zwiększam ramy czasowe.

Tabela, o której mowa, wygląda następująco:

CREATE TABLE counter (
    id bigserial PRIMARY KEY
  , ts timestamp NOT NULL
  , group_id bigint NOT NULL
);

Obecnie mam następujące indeksy, ale wydajność jest nadal niska:

CREATE INDEX ts_index
  ON counter
  USING btree
  (ts);

CREATE INDEX group_id_index
  ON counter
  USING btree
  (group_id);

CREATE INDEX comp_1_index
  ON counter
  USING btree
  (ts, group_id);

CREATE INDEX comp_2_index
  ON counter
  USING btree
  (group_id, ts);

Uruchomienie EXPLAIN dla zapytania daje następujący wynik:

"QUERY PLAN"
"HashAggregate  (cost=467958.16..467958.17 rows=1 width=4)"
"  ->  Index Scan using ts_index on counter  (cost=0.56..467470.93 rows=194892 width=4)"
"        Index Cond: ((ts >= '2014-02-26 00:00:00'::timestamp without time zone) AND (ts <= '2014-02-27 23:59:00'::timestamp without time zone))"

SQL Fiddle z przykładowymi danymi: http://sqlfiddle.com/#!15/7492b/1

Pytanie

Czy można poprawić wydajność tego zapytania, dodając lepsze indeksy, czy też muszę zwiększyć moc przetwarzania?

Edytuj 1

Używana jest wersja PostgreSQL 9.3.2.

Edytuj 2

Próbowałem @Erwin z EXISTS:

SELECT group_id
FROM   groups g
WHERE  EXISTS (
   SELECT 1
   FROM   counter c
   WHERE  c.group_id = g.group_id
   AND    ts BETWEEN timestamp '2014-03-02 00:00:00'
                 AND timestamp '2014-03-05 12:00:00'
   );

Niestety nie wydawało się to zwiększać wydajności. Plan zapytań:

"QUERY PLAN"
"Nested Loop Semi Join  (cost=1607.18..371680.60 rows=113 width=4)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Bitmap Heap Scan on counter c  (cost=1607.18..158895.53 rows=60641 width=4)"
"        Recheck Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        ->  Bitmap Index Scan on comp_2_index  (cost=0.00..1592.02 rows=60641 width=0)"
"              Index Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Edytuj 3

Plan zapytania dla zapytania LATERAL z ypercube:

"QUERY PLAN"
"Nested Loop  (cost=8.98..1200.42 rows=133 width=20)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Result  (cost=8.98..8.99 rows=1 width=0)"
"        One-Time Filter: ($1 IS NOT NULL)"
"        InitPlan 1 (returns $1)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan using comp_2_index on counter c  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        InitPlan 2 (returns $2)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan Backward using comp_2_index on counter c_1  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Kolejny pomysł, który wykorzystuje również groupstabelę i konstrukcję o nazwie LATERALjoin (dla fanów SQL-Server jest to prawie identyczne zOUTER APPLY ). Ma tę zaletę, że agregaty można obliczać w podzapytaniu:

SELECT group_id, min_ts, max_ts
FROM   groups g,                    -- notice the comma here, is required
  LATERAL 
       ( SELECT MIN(ts) AS min_ts,
                MAX(ts) AS max_ts
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2011-03-02 00:00:00'
                        AND timestamp '2013-03-05 12:00:00'
       ) x 
WHERE min_ts IS NOT NULL ;

Przetestuj w SQL-Fiddle pokazuje, że zapytanie wykonuje skanowanie (group_id, ts)indeksu w indeksie.

Podobne plany są tworzone przy użyciu 2 łączeń bocznych, jednego dla min i jednego dla maksimum, a także z 2 wbudowanymi skorelowanymi podkwerendami. Można ich również użyć, jeśli chcesz wyświetlić całe counterwiersze oprócz dat minimalnych i maksymalnych:

SELECT group_id, 
       min_ts, min_ts_id, 
       max_ts, max_ts_id 
FROM   groups g
  , LATERAL 
       ( SELECT ts AS min_ts, c.id AS min_ts_id
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2012-03-02 00:00:00'
                        AND timestamp '2014-03-05 12:00:00'
         ORDER BY ts ASC
         LIMIT 1
       ) xmin
  , LATERAL 
       ( SELECT ts AS max_ts, c.id AS max_ts_id
         FROM counter c
         WHERE c.group_id = g.group_id
           AND c.ts BETWEEN timestamp '2012-03-02 00:00:00'
                        AND timestamp '2014-03-05 12:00:00'
         ORDER BY ts DESC 
         LIMIT 1
       ) xmax
WHERE min_ts IS NOT NULL ;

Ponieważ nie masz agregatu na liście wyboru, group byjest to prawie to samo, co umieszczenie distinctna liście wyboru, prawda?

Jeśli tego właśnie chcesz, możesz uzyskać szybkie wyszukiwanie indeksu na comp_2_index, przepisując go, aby użyć zapytania rekurencyjnego, zgodnie z opisem na wiki PostgreSQL .

Zrób widok, aby skutecznie zwracać różne group_ids:

create or replace view groups as
WITH RECURSIVE t AS (
             SELECT min(counter.group_id) AS group_id
               FROM counter
    UNION ALL
             SELECT ( SELECT min(counter.group_id) AS min
                       FROM counter
                      WHERE counter.group_id > t.group_id) AS min
               FROM t
              WHERE t.group_id IS NOT NULL
    )
     SELECT t.group_id
       FROM t
      WHERE t.group_id IS NOT NULL
UNION ALL
     SELECT NULL::bigint AS col
      WHERE (EXISTS ( SELECT counter.id,
                counter.ts,
                counter.group_id
               FROM counter
              WHERE counter.group_id IS NULL));

A następnie użyj tego widoku zamiast tabeli odnośników w existsczęściowym połączeniu Erwina .

Ponieważ są tylko 133 different group_id's, możesz użyć integer(lub nawet smallint) dla id_grupy. Jednak niewiele ci to kupi, ponieważ wypełnienie do 8 bajtów zje resztę w tabeli i możliwe indeksy wielokolumnowe. Przetwarzanie zwykłego integerpowinno być jednak nieco szybsze. Więcej na intwersetachint2 .

CREATE TABLE counter (
    id bigserial PRIMARY KEY
  , ts timestamp NOT NULL
  , group_id int NOT NULL
);

@Leo: znaczniki czasu są przechowywane w 8-bajtowych liczbach całkowitych w nowoczesnych instalacjach i mogą być przetwarzane idealnie szybko. Detale.

@ypercube: Indeks włączony (group_id, ts)nie może pomóc, ponieważ group_idw zapytaniu nie ma żadnego warunku .

Twoim głównym problemem jest ogromna ilość danych, które muszą zostać przetworzone:

Indeksuj skanowanie za pomocą ts_index na liczniku (koszt = 0,56..467470.93 wierszy = szerokość 194892 = 4)

Widzę, że jesteś zainteresowany jedynie istnieniem group_id, a nie faktyczną liczbą. Ponadto istnieją tylko 133 różne group_ids. Dlatego zapytanie może być spełnione przy pierwszym trafieniu gorup_idw danym przedziale czasowym. Stąd ta sugestia dotycząca alternatywnego zapytania z połączeniem EXISTSczęściowym :

Zakładając tabelę przeglądową dla grup:

SELECT group_id
FROM   groups g
WHERE  EXISTS (
   SELECT 1
   FROM   counter c
   WHERE  c.group_id = g.group_id
   AND    ts BETWEEN timestamp '2014-03-02 00:00:00'
                 AND timestamp '2014-03-05 12:00:00'
   );

Indeksu comp_2_indexna (group_id, ts)teraz staje instrumentalny.

SQL Fiddle (w oparciu o skrzypce dostarczone przez @ypercube w komentarzach)

Tutaj zapytanie preferuje indeks (ts, group_id), ale myślę, że dzieje się tak ze względu na konfigurację testu z „klastrowanymi” znacznikami czasu. Jeśli usuniesz indeksy z wiodącym ts( więcej na ten temat ), planista z przyjemnością również użyje indeksu (group_id, ts)- szczególnie w przypadku skanowania tylko indeksu .

Jeśli to zadziała, możesz nie potrzebować tego innego możliwego ulepszenia: Wstępnie agreguj dane w zmaterializowanym widoku, aby drastycznie zmniejszyć liczbę wierszy. Ma to sens szczególnie, jeśli dodatkowo potrzebujesz rzeczywistych obliczeń . Następnie ponosisz koszty przetworzenia wielu wierszy jeden raz podczas aktualizacji mv. Możesz nawet łączyć agregaty dzienne i godzinowe (dwie osobne tabele) i dostosowywać do tego zapytanie.

Czy ramy czasowe w zapytaniach są arbitralne? A może głównie w pełnych minutach / godzinach / dniach?

CREATE MATERIALIZED VIEW counter_mv AS
SELECT date_trunc('hour', ts) AS hour
     , group_id
     , count(*) AS ct
GROUP BY 1,2
ORDER BY 1,2;

Utwórz niezbędne indeksy counter_mvi dostosuj zapytanie do pracy z nim ...