case study: join, subselect, indeksy + pomysł na “aliasy” wyliczanych stałych

kolega pokazał mi pewną sytuację. miał taką tabelę:

                 TABLE "public.staty"
COLUMN  |              Type              | Modifiers
---------+--------------------------------+-----------
DATA    | timestamp(0) without time zone | NOT NULL
size    | integer                        | NOT NULL
proto   | integer                        | NOT NULL
macfrom | integer                        | NOT NULL
macto   | integer                        | NOT NULL
Indexes:
"blah123" PRIMARY KEY, btree (macto, DATA, proto, macfrom)
"dupa" btree (DATA)
"f1" btree (macfrom)
"f2" btree (macto)
"fs" btree (size)
CHECK constraints:
"proto check" CHECK (proto < 65536 AND proto > -1)
Foreign-KEY constraints:
"staty_fk" FOREIGN KEY (macto) REFERENCES macs(id)
"staty_fk1" FOREIGN KEY (macfrom) REFERENCES macs(id)

z 3 milionami rekordów. i na tym zapytanie:

SELECT a.i AS dupa, SUM(COALESCE(b.size, 0)) AS to_, SUM(COALESCE(f.size, 0)) AS FROM
FROM (SELECT
((current_timestamp-'1 month'::INTERVAL) + INTERVAL '1 second'*c.it) AS inter,
i
FROM(
SELECT i, i * ((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)
AS it FROM generate_series(0,599) i
) AS c
) AS a
LEFT JOIN
(SELECT  DATA, SIZE FROM staty WHERE macto='$mac' ) b
ON (
b.DATA > (a.inter-(((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)||' second')::INTERVAL )
AND
b.DATA < a.inter
)
LEFT JOIN
(SELECT  DATA, SIZE FROM staty WHERE macfrom='$mac' ) f
ON(
f.DATA > (a.inter-(((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)||' second')::INTERVAL )
AND
f.DATA < a.inter
)
GROUP BY a.i ORDER BY a.i

iiiś. poza “urodą" zapytania powalał czas: 120-200 sekund!

no muszę przyznać, że miałem bardzo duży problem by zrozumieć.

pierwsza rzecz – stwierdziłem, że przepiszę to zapytanie do postaci czytelnej.

i tu pojawił sie problem – w kodzie wielokrotnie występują pewne stałe wyliczane – now() – ‘1 month' czy to samo dzielone na 600.

jak będę je powtarzał za każdym razem to czytelność szlag trafi. na szczęście przypomniało mi się, że takie rzeczy można łatwo obejść aliasami.

chwila pisania i mamy zapytanie:

SELECT
g.i,
SUM(COALESCE(f.size, 0)) AS from_,
SUM(COALESCE(t.size, 0)) AS to_
FROM
generate_series(0,599) g(i),
staty f,
staty t,
(
SELECT
now() - '1 month'::INTERVAL AS START,
'1 second'::INTERVAL * EXTRACT(epoch FROM (now() - (now() - '1 month'::INTERVAL)))/600 AS div
) x
WHERE
f.macfrom = '$mac'
AND t.macto = '$mac'
AND f.DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
AND t.DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
GROUP BY
g.i

(tu zwracam uwagę na subselect () x – to on robi za aliasy do stałych wyliczanych.

odtrąbiłem sukces. ale przedwcześnie. zapytanie wykonywało się w zasadniczo tym samym czasie = 128 – 160 sekund.

powiedziałem koledze by pozakładał indeksy wielopolowe:

CREATE INDEX some_name ON staty (macfrom, DATA);
CREATE INDEX some_other_name ON staty (macto, DATA);

te dwa indeksy i vacuum później (oj, dużo później) mamy efekt. zerowy. moje zapytanie nadal jest wolne. co ciekawe – oryginalne zapytanie nagle przyspieszyło do około 3.7 sekundy.

zastanowiłem się czemu. i nagle – olśnienie. zapytanie (w całości) operuje na dokładnie wszystkich danych z tabeli – tzn. ten miesiąc dla którego agregujemy dane to więcej niż danych jest w bazie – podobno koło tygodnia.

co oznacza, że musimy wyselectować całość. a że maszyna ma mało ramu, to szybciej sobie radzi z liczeniem popaczkowanym w małe części niż całości na raz.

tak więc kolejny rewrite:

SELECT
g.i,
(
SELECT SUM(SIZE) FROM staty WHERE macfrom = '$mac' AND DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
) AS from_,
(
SELECT SUM(SIZE) FROM staty WHERE macto = '$mac' AND DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
) AS to_
FROM
generate_series(0,599) g(i),
(
SELECT
now() - '1 month'::INTERVAL AS START,
'1 second'::INTERVAL * EXTRACT(epoch FROM (now() - (now() - '1 month'::INTERVAL)))/600 AS div
) x

i mamy czas – 170ms.

refleksje na przyszłość:

  • joiny są szybkie, chyba, że operujemy na dużych zbiorach danych na maszynach z małą ilością ramu
  • subselecty nie zawsze są wolne. zazwyczaj tak. ale w tym przypadku – gdzie tak naprawdę nic nie łączy danych z dwóch kopii tabeli staty – działają super
  • subselect jako metoda aliasowania wartości – czad. upraszcza pisanie, trochę przyspiesza i ułatwia zrozumienie kodu 🙂

2 thoughts on “case study: join, subselect, indeksy + pomysł na “aliasy” wyliczanych stałych”

  1. Musisz dbac o kolejnosc wybieranych danych. Najpierw selectujesz tylko tyle na ilu trzeba operowac, a potem zmniejszac to kolejno. MySQL kiedys potrafil dobrze korzystac tylko z jednego indeksu, teraz w nowszych wersjach tez mu to lepiej nie wychodzi.

  2. Tyle ze to zapytanie nie dzialalo by mi w mysqlu. Nie jestem pewien czy mysql ma nawet odpowiednik psqlowego ‘generate_series()’

Comments are closed.