Co działa wydajniej w postgresql do tagów: array czy relacja wiele do wielu z tabelą łączącą?
Pozdro.
0
0
Dzięki za linka.
Czy dysponuje ktoś obszerniejszym testem, np. zapytanie które wybiera posty
mające wszystkie tagi "ala","ola","kasia" i żadnego z tagów "basia","ewa".
Co w przypadku gdy tagi bardzo często, albo bardzo rzadko się
powtarzają, czy wtedy także rozwiązanie na arrays jest szybsze?
Pozdrawiam.
1
@abrakadaber Przeczytałem artykuł. Ciekawy, ale ... Autor testuje dwa zupełnie różne zapytania. Z tym, że pierwsze jakby nieudolnie zrobione. Niepotrzebne są tam LEFT JOINy, które obawiam się, że dość mocno obniżają efektywność zapytania. Po drugie, aby zapytania były tożsame, to w drugim przypadku powinien mieć tablicę INTów połączoną ze słownikiem tagów.
Oczywiście nie wspomnę już że nie liczy się tylko szybkość zapytań, ale właśnie np. więzy integralności, których w drugim przypadku ciężko upilnować.
1
jaś fasola napisał(a):
Dzięki za linka.
Czy dysponuje ktoś obszerniejszym testem
Po co czytasz idiotyczne materiały które nawet jakby były dobre nie
pozowoliłby wyciągnąć wniosków do Twojego problemu? Zrób
se sam test. Załóż bazę i wpisz zapytania, w czym masz problem?
Jakie masz zapytania?
--trochę dupereli
rollback;
\pset pager off
\timing off
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
\c postgres;
set role postgres;
--czyścimy stare śmieci
DROP DATABASE IF EXISTS test_adb;
DROP TABLESPACE IF EXISTS test_adb_dbspace;
DROP ROLE IF EXISTS test_adb;
--zakładamy nową bazę i usera
CREATE ROLE test_adb PASSWORD 'test_adb' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test_adb OWNER test_adb ENCODING 'UTF-8';
\c test_adb;
set role test_adb;
--kilka sekwencji
CREATE SEQUENCE gseq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1000;
CREATE SEQUENCE seq_tag START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1000;
CREATE SEQUENCE seq_data START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1000;
--jakieś dane bez arrays
CREATE TABLE data1 (
id bigint DEFAULT nextval('gseq') NOT NULL,
data1 int NOT NULL,
data2 int NOT NULL,
data3 int NOT NULL,
data4 int NOT NULL
);
ALTER TABLE ONLY data1 ADD CONSTRAINT data1_pkey PRIMARY KEY (id);
--tagi
CREATE TABLE tags (
id bigint DEFAULT nextval('seq_tag') NOT NULL,
name character varying(32) NOT NULL
);
ALTER TABLE ONLY tags ADD CONSTRAINT tags_pkey PRIMARY KEY (id);
CREATE UNIQUE INDEX tags_name ON tags(name);
--tabela łącząca
CREATE TABLE connect1 (
id bigint DEFAULT nextval('gseq') NOT NULL,
id_data1 bigint NOT NULL,
id_tag bigint NOT NULL
);
ALTER TABLE ONLY connect1 ADD CONSTRAINT connect1_pkey PRIMARY KEY (id);
ALTER TABLE ONLY connect1 ADD CONSTRAINT connect1_id_data1_fkey FOREIGN KEY (id_data1) REFERENCES data1(id);
ALTER TABLE ONLY connect1 ADD CONSTRAINT connect1_id_tag_fkey FOREIGN KEY (id_tag) REFERENCES tags(id);
create unique index connect1_idx on connect1 (id_data1,id_tag);
--jakieś dane z arrays
CREATE TABLE data2 (
id bigint DEFAULT nextval('gseq') NOT NULL,
data1 int NOT NULL,
data2 int NOT NULL,
data3 int NOT NULL,
data4 int NOT NULL,
tags bigint[]
);
ALTER TABLE ONLY data2 ADD CONSTRAINT data2_pkey PRIMARY KEY (id);
--losowy string
create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u,v,w,x,y,z,0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W,X,Y,Z}';
result text := '';
i integer := 0;
begin
for i in 1..length loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$$ language plpgsql;
--tworzymy tagi
create or replace function mk_tags(length integer) returns void as
$$
declare
i integer;
j integer;
cnt integer;
vname text;
begin
for i in 1..length loop
cnt = floor( random() * 3 + random() * 3 + random() * 3 + random() * 3 + random() * 3 + random() * 3 + random() * 3 + random() * 3 + 3 );
vname = random_string( cnt );
execute 'insert into tags (name) values($1)' using vname;
end loop;
end;
$$ language plpgsql;
--tworzymy dane bez arrays
create or replace function mk_data1(length integer) returns void as
$$
declare
i integer;
j integer;
vd1 integer;
vd2 integer;
vd3 integer;
vd4 integer;
cnt_tags integer;
max_tags integer;
cnt_exists integer;
vid bigint;
vid_tag bigint;
vname text;
begin
select count(*) into max_tags from tags;
for i in 1..length loop
vd1 = floor( random() * 10 + 1 );
vd2 = floor( random() * 100 + 1 );
vd3 = floor( random() * 1000 + 1 );
vd4 = floor( random() * 1000 + 1 );
select nextval('seq_data') into vid;
execute 'insert into data1 (id,data1,data2,data3,data4) values($1,$2,$3,$4,$5)' using vid, vd1, vd2, vd3, vd4;
-- ile średnio tagów?
cnt_tags = floor( random() * 4 + random() * 4 + random() * 4 + random() * 4 + 4 );
for j in 1..cnt_tags loop
-- jaki rozkład tagów? na pewno nigdy nie jest równomierny.
-- powiedzmy że 80% użyć tagów dotyczy 20% najczęstszych słów
if random() < 0.80 then
vid_tag = floor( random() * max_tags * 0.2 + 1 );
else
vid_tag = floor( random() * max_tags + 1 );
end if;
select count(*) into cnt_exists from connect1 where id_data1=vid and id_tag=vid_tag;
if cnt_exists = 0 then -- bez powtórzeń tagów
execute 'insert into connect1 (id_data1,id_tag) values($1,$2)' using vid, vid_tag;
end if;
end loop;
end loop;
end;
$$ language plpgsql;
--tworzymy dane z arrays
create or replace function mk_data2() returns void as
$$
declare
begin
insert into data2(id,data1,data2,data3,data4,tags) select id,data1,data2,data3,data4,(select array( select id_tag from connect1 where id_data1=d1.id) ) from data1 as d1;
end;
$$ language plpgsql;
select mk_tags(10000); --
select mk_data1(2000000); --
select mk_data2();
0
To ostatecznie jakie rozwiązanie jest lepsze?
0
Sprawdź sobie sam. Najlepiej na oczekiwanej (docelowej) ilości rekordów i tej samej maszynie... Wiesz co i jak masz sprawdzić...
1
jaś fasola napisał(a):
To ostatecznie jakie rozwiązanie jest lepsze?
Najlepiej na oryginalnej bazie przetestuj zapytania. Z mojego doświadczenia wynika, że
małe różnice w bazie mogą powodować duże rożnice w czasie wykonania zapytań. Nikomu nie
chce się robić w jeden sposób i potem przerabiać wiele razy i sprawdzać czasy. Dlatego
warto zrobić urposzczone środowisko testowe. Środowisko uproszczone zrobiłem za Ciebie
kilka postów wyżej, czego jeszcze oczekujesz? Mam Twoje zapytania za Ciebie wymyślić?
Najpierw upewnij się że testujesz to samo:
test_adb=# select count(*) from data1;
count
---------
2000000
(1 row)
Time: 267,804 ms
test_adb=# select count(*) from data2;
count
---------
2000000
(1 row)
Time: 294,286 ms
test_adb=# select count(*) from tags ;
count
-------
10000
(1 row)
Time: 2,669 ms
Ilość rekordów się zgadza.
test_adb=# select sum(id+data1+data2+data3+data4) from data1;
sum
---------------
2002115695350
(1 row)
Time: 1398,519 ms
test_adb=# select sum(id+data1+data2+data3+data4) from data2;
sum
---------------
2002115695350
(1 row)
Time: 1453,010 ms
Sumy kontrolne pozostałych pól zgadzają się.
Zobaczymy czy są indeksy:
indname | indowner | indrelid | indam | indkey | indkey_names | indexprs | indpred
--------------------+----------+----------+-------+--------+-------------------+----------+---------
connect1_idx | 1184977 | connect1 | btree | 2 3 | {id_data1,id_tag} | f | f
connect1_pkey | 1184977 | connect1 | btree | 1 | {id} | f | f
data1_idx_data1_id | 1184977 | data1 | btree | 2 1 | {data1,id} | f | f
data1_pkey | 1184977 | data1 | btree | 1 | {id} | f | f
data2_pkey | 1184977 | data2 | btree | 1 | {id} | f | f
data2_tags | 1184977 | data2 | gin | 6 | {tags} | f | f
tags_name | 1184977 | tags | btree | 2 | {name} | f | f
tags_pkey | 1184977 | tags | btree | 1 | {id} | f | f
Widać, że tabela connect1 ma indeks do wyszukiwania złączeń, a data2 ma indeks do wyszukiwania. Nie wiem czy da się założyć lepsze indeksy, może sie da, ale w tej chwili nie mam lepszego pomysłu.
Zobaczmy jak to wygląda pamięciowo, ile zajmują tabele miejsca (wraz z indeksami):
relation | total_size
-----------------+------------
public.connect1 | 2325 MB
public.data2 | 576 MB
public.data1 | 251 MB
public.tags | 1272 kB
public.gseq | 8192 bytes
public.seq_data | 8192 bytes
public.seq_tag | 8192 bytes
Upsssssss Rozwiązanie na tagach: 576+1.272=577MB. Rozwiązanie na tabeli łączącej: 251+1272+1.272=1524. Chyba
to już odpowiada na pytanie, co będzie szybciej działało. Musisz mieć 3 razy więcej pamięci RAM dla rozwiązania
opartego na tabeli lączącej - proszę, domyśl się jak używam słowa 'musisz'.
Zobaczymy czy obie wersje mieszczą się w cache bazy:
test_adb=# show shared_buffers ;
shared_buffers
----------------
4000MB
(1 row)
Time: 0,216 ms
Mieszczą się.
Trzeba wymyślić jakieś zapytania... Przypomnę, założyłem unikalność
tagów w jednym rekordzie.
Ilość rekordów zawierających tag o id=1.
select count(*) from connect1 where id_tag = 1;
count
-------
9525
(1 row)
Time: 726,962 ms
Ilość rekordów zawierających tag o id=1.
test_adb=# select count(*) from connect1 where id_tag = 1;
count
-------
9525
(1 row)
Time: 733,372 ms
test_adb=# select count(*) from data2 where '{1}' <@ tags;
count
-------
9525
(1 row)
Time: 22,645 ms
Wynik ten sam, a rozwiązanie na tagach szybsze z 30 razy.
Może brakuje odpowiedniego indeksu i dlatego na connect działa wolniej?
test_adb=# create index connect1_idx_id_tag on connect1 (id_tag);
CREATE INDEX
Time: 57650,616 ms
test_adb=# select count(*) from connect1 where id_tag = 1;
count
-------
9525
(1 row)
Time: 18,368 ms
Tak, taki był problem, teraz czasy są porównywalne, na connect1 jakby szybciej.
Dajmy na innych id:
test_adb=# select count(*) from connect1 where id_tag = 856;
count
-------
9666
(1 row)
Time: 19,188 ms
test_adb=# select count(*) from data2 where '{856}' <@ tags;
count
-------
9666
(1 row)
Time: 32,935 ms
Znowu wydaje się jakby na connect działąło szybciej.
Zróbmy sumę kontrolną, zapytanie mało przydatne w praktyce, ale dobrze sprawdzi, czy
tabele mają taką samą zawartość. To zapytanie można zrobić na arrays bez złączeń!
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 join connect1 as c1 on d1.id = c1.id_data1 where c1.id_tag=1;
sum
------------
9542672890
(1 row)
Time: 79,768 ms
test_adb=# select sum(d2.id+data1+data2+data3+data4) from data2 as d2 where '{1}' <@ tags;
sum
------------
9542672890
(1 row)
Time: 39,946 ms
Wyniki identyczne, a czas na arrays jakby krótszy. Zgaduję, że rozwiązanie na arrays
wygrywa, bo nie ma złączeń.
Zróbmy to samo co wyżej, ale na wybierając rekordy które mają oba tagi o id 15 i 25:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=15) and exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=25);
sum
----------
35787230
(1 row)
Time: 71,828 ms
test_adb=# select sum(d2.id+data1+data2+data3+data4) from data2 as d2 where '{15,25}' <@ tags;
sum
----------
35787230
(1 row)
Time: 6,803 ms
Wyniki identyczne, czas na arrays jakby 10 razy krótszy.
Może uda się zopytmalizować dla connect?
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where 2 = (select count(*) from connect1 as c1 where c1.id_data1 = d1.id and (c1.id_tag=15 or c1.id_tag=25));
sum
----------
35787230
(1 row)
Time: 13101,273 ms
Wynik ten sam, a czas bardzo długi - ten sposób na pewno nie przyspiesza.
Kolejna próba ratowania rozwiązania na tabeli łączącej:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where 2 = (select count(*) from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag in (15,25) );
sum
----------
35787230
(1 row)
Time: 11874,769 ms
Jak widać, próba totalnie nie udana. Wynik ten sam, ale czas bardzo długi.
Jeszcze jedna próba:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where exists (select count(*) from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag in (15,25) having count(*)=2 );
sum
----------
35787230
(1 row)
Time: 11575,569 ms
Znowu nie udana (wynik ten sam, czas długi).
Pora na inne zapytania:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=15) and exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=25) and not exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=34);
sum
----------
34687145
(1 row)
Time: 80,156 ms
test_adb=#
test_adb=# select sum(d2.id+data1+data2+data3+data4) from data2 as d2 where '{15,25}' <@ tags and not '{34}' && d2.tags;
sum
----------
34687145
(1 row)
Time: 7,886 ms
Wyniki znowu takie same, a czas z arrays jakby 10 razy krótszy.
Może jeszcze inne zapytanie:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=12) and not exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=13);
sum
------------
9519588317
(1 row)
Time: 91,648 ms
test_adb=# select sum(d2.id+data1+data2+data3+data4) from data2 as d2 where '{12}' <@ tags and not '{13}' && d2.tags;
sum
------------
9519588317
(1 row)
Time: 46,617 ms
Tutaj nie ma już porażającej różnicy w czasie wykonania.
A tu ciekawostka:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where not exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=13);
sum
---------------
1992398489677
(1 row)
Time: 1983,074 ms
test_adb=# select sum(d2.id+data1+data2+data3+data4) from data2 as d2 where not '{13}' && d2.tags;
sum
---------------
1992398489677
(1 row)
Rozwiązanie na connect działa szybciej.
Tutaj też sytuacja się odwraca:
test_adb=# select sum(d1.id+data1+data2+data3+data4) from data1 as d1 where not exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=33) and not exists (select * from connect1 as c1 where c1.id_data1 = d1.id and c1.id_tag=55);
sum
---------------
1982828610121
(1 row)
Time: 2252,289 ms
test_adb=# select sum(d2.id+data1+data2+data3+data4) from data2 as d2 where not '{33,55}' && d2.tags;
sum
---------------
1982828610121
(1 row)
Time: 2390,210 ms
Na arrays działa trochę wolniej.
W tych przykładach zwykle na arrays działa szybciej. Jakie Ty masz zapytania?
0
artur_bredzki napisał(a):
W tych przykładach zwykle na arrays działa szybciej. Jakie Ty masz zapytania?
NP. takie zapytanie które znajduje wszystkie rekordy które mają 7 lub więcej pasujących z dziesięciu zadanych
tagów i sortuje po ilości pasujących tagów. TO t ylko przykład, może być np. że znajduje te któ©e mają 2 tagi z
zadanych 4.
0
Jaś Fasola napisał(a):
artur_bredzki napisał(a):
W tych przykładach zwykle na arrays działa szybciej. Jakie Ty masz zapytania?
NP. takie zapytanie które znajduje wszystkie rekordy które mają 7 lub więcej pasujących z dziesięciu zadanych
tagów i sortuje po ilości pasujących tagów. TO t ylko przykład, może być np. że znajduje te któ©e mają 2 tagi z
zadanych 4.
Pierwsza sprawa: postgres nie potrafi zbudować indeksu do takiego zapytania.
Druga sprawa: jakbyś nawet znalazł wtyczke która umożliwi zbudowanie takiego
indeksu, to indeks będzie miał bardzo duży rozmiar - nie wiem czy to ma sens.
Trzecia sprawa: istnieje sposób (nie znam jego fachowej nazwy) na 'połowiczne
zaindeksowanie'. Sposób ten bardzo przyspiesza takie zapytania. Kiedyś
zaimplementowałem to w C++. Kodu nie mam już pod ręką, nie powiem Ci konkretnie
jakie były czasy. Dużo zależy od rozkładu tagów. Nigdy nie słyszałem aby
postgres coś takiego wspierał, ale nie mówię że słyszalem o wszystkim.
A tak swoją drogą, to nie widzę żadnej inwencji z Twojej strony, zbudowałeś
chociaż bazę z mojego skryptu żeby samemu zmierzyć czasy w swoim środowisku?
Masz przykład na arrays (wybiera 4 z 7):
test_adb=# select * from data2 where (select count(*) from (select distinct unnest( tags || '{1,2,3,408,417,6,7}') ) as cnt ) - array_length(tags,1) <= 3 limit 10;
id | data1 | data2 | data3 | data4 | tags
--------+-------+-------+-------+-------+---------------------------------------------------------------
235036 | 1 | 50 | 146 | 296 | {1,5,6,408,417,464,688,806,991,1735,1844,2069,3856,9181,9701}
(1 row)
Time: 23557,226 ms
Czas jak widzisz sam - długawy.
Tu masz drugie podejście na arrays:
test_adb=# select * from data2 where
test_adb-# (case when '{1}' <@ tags then 1 else 0 end) +
test_adb-# (case when '{2}' <@ tags then 1 else 0 end) +
test_adb-# (case when '{3}' <@ tags then 1 else 0 end) +
test_adb-# (case when '{6}' <@ tags then 1 else 0 end) +
test_adb-# (case when '{7}' <@ tags then 1 else 0 end) +
test_adb-# (case when '{408}' <@ tags then 1 else 0 end) +
test_adb-# (case when '{417}' <@ tags then 1 else 0 end)
test_adb-# >= 4;
id | data1 | data2 | data3 | data4 | tags
--------+-------+-------+-------+-------+---------------------------------------------------------------
235036 | 1 | 50 | 146 | 296 | {1,5,6,408,417,464,688,806,991,1735,1844,2069,3856,9181,9701}
(1 row)
Time: 5627,365 ms
Wynik ten sam, zapytanie bardziej zagmatwane, ale czas 4-5 razy krótszy.
Teraz rozwiązanie na tabeli lączącej:
test_adb=# select
test_adb-# *
test_adb-# from
test_adb-# data1 as d1
test_adb-# where
test_adb-# 4 <= ( select count(*) as cnt from connect1 where id_data1=d1.id and id_tag in (1,2,3,408,417,6,7) )
test_adb-# ;
id | data1 | data2 | data3 | data4
--------+-------+-------+-------+-------
235036 | 1 | 50 | 146 | 296
(1 row)
Time: 33510,505 ms
Wynik ten sam, a czas najdłuższy.
Ostatnie zapytanie najszybsze, a działa w oparciu o tabelę łączącą. Tutaj trzeba jeszcze dociągnąć
dane z tagów, ale powiedzmy że to będzie bardzo szybkie.
test_adb=# select
test_adb-# *
test_adb-# from
test_adb-# data1 as d1
test_adb-# where
test_adb-# 4 <=
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=1) then 1 else 0 end ) +
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=2) then 1 else 0 end ) +
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=3) then 1 else 0 end ) +
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=408) then 1 else 0 end ) +
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=417) then 1 else 0 end ) +
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=6) then 1 else 0 end ) +
test_adb-# (case when exists (select * from connect1 where id_data1=d1.id and id_tag=7) then 1 else 0 end )
test_adb-# ;
id | data1 | data2 | data3 | data4
--------+-------+-------+-------+-------
235036 | 1 | 50 | 146 | 296
(1 row)
Time: 3891,812 ms
Nie chce mi się sprawdzać tego na innych danych, ale na Twoich danych na pewno wyniki będą trochę inne.
Ile masz rekordów? Ile średnio tagów na rekord? Jaki rozkład tagów?
0
artur_bredzki napisał(a):
Druga sprawa: jakbyś nawet znalazł wtyczke która umożliwi zbudowanie takiego
indeksu, to indeks będzie miał bardzo duży rozmiar - nie wiem czy to ma sens.
Moim zdaniem to bzdura, indeksy nie mają bardzo dużych rozmiarów.
Czy mógłbyś napisać dlaczego będzie miał duży rozmiar?
0
jaś fasola napisał(a):
Moim zdaniem to bzdura, indeksy nie mają bardzo dużych rozmiarów.
Czy mógłbyś napisać dlaczego będzie miał duży rozmiar?
Nie mam motywacji do pisania referatów. Nic się nie nauczyłem z naszej
rozmowy, nawet nie pokazałeś czasów wykonania moich/twoich zapytań na
Twojej bazie danych i na Twojej konfiguracji sprzętowej. Zapytaj na algorytmach,
może ktomuś będzie chciało się tłumaczyć.