Czy można jakoś przyspieszyć wykonanie tego typu zapytania?
select distinct
c.*, u.*
from
cars as c
join
users as u
on
c.id_user = u.id
Pozdrawiam
0
1
W niektórych przypadkach można użyć EXISTS zamiast DISTINCT:
http://www.toadworld.com/platforms/oracle/w/wiki/4773.exists-versus-distinct
0
Można założyć indeksy.
0
lub hinty jeśli to np oracle
0
Plan zapytania - wygeneruj, przeanalizuj, wyciągnij wnioski, zrealizuj je, sprawdź nowy plan zapytania, przeanalizuj go i tak dalej - aż uzyskasz zadowalającą wydajność, albo przekopaniu połowy internetu stwierdzisz, że nie da się lepiej.
0
Dziękuję za odpowiedzi.
Distinct musi być, ponieważ dodaje go do zapytania plugin nie mojego
autorstwa. Pomysł z indeksami chyba jest dobry, ale nie wiem czy
bazy potrafią go wykorzystać, a gdy wynik jest z wielu tabel, to
by musiał być indeks 'wielotabelowy', a takich chyba na razie
bazy nie obsługują:
http://stackoverflow.com/questions/16402225/index-spanning-multiple-tables-in-postgresql
Sytuacja wydaje się beznadziejna.
Pozdrawiam
0
Piszesz o postgresql? On znakomicie radzi sobie z indeksami. Co więcej - musisz się "nagimnastykować", by ich nie użyć, bądż użyć innego, niż sobie system wymyślił...
0
artur_bredzki napisał(a):
Dziękuję za odpowiedzi.
a gdy wynik jest z wielu tabel, to by musiał być indeks 'wielotabelowy', a takich chyba na razie
bazy nie obsługują:
Ech ... chyba nie do końca rozumiesz jak działają indeksy - pomijając ten fakt to uwierz, że postgresql sobie z tym radzi i to całkiem dobrze jak poprzednik napisał.
Natomiast jeszcze nawiązując do indeksów "wielotabelowych" jak to określiłeś to np Oracle posiada coś takiego jak statystyki systemowe, które (w mega uproszczeniu) na podstawie preferencji użytkownika "zapamiętują" z których indeksów na poszczególnych zapytaniach baza ma skorzystać.
Dlaczego o tym piszę? Sytuacja z życia wzięta:
Jest tabla jej rozmiar to około liczony w setkach giga danych ilość rekordów w mln lub nawet mld (kto by to liczył) i w tabli dwa istotne pola z indeksem. Pierwsze to DataSprzedazy, drugie nazwijmy je IDTowaru.
Klient potrzebuje zestawienie sprzedaży danego towaru w określonym przedziale czasowym. W przypadku gdy zakres dat jest "niewielki" np z danego miesiąca to do zapytania lepiej był wskazać indeks (poprzez hint) z DataSprzedazy. Jeżeli natomiast zakres dat wprowadzony przez klienta jest np 20 lat to w takim wypadku konieczne jest wskazanie indeksu po IDTowaru bo po DataSprzedazy trwało to w nieskończoność.
Sytuacja miała miejsce w jednej z większych hurtowni farmaceutycznych w kraju i tutaj z pomocą właśnie przyszły statystyki oracle gdyż określanie "na oko" jaki zakres dat jest "za duży" mijało się z celem. Ludzie, którzy się znają na hurtowniach danych powiedzą ... co za głupota trzymać rekordy na bazie produkcyjnej z przed 20 lat ... Zgadzam się ale taka specyfika systemu pisanego od 20 lat ... W korpo niewiele idzie zmienić (i taki mały żuczek programista jak ja nie wiele może zrobić), a przynajmniej można faktury za support i utrzymanie wystawiać klientowi miesiąc w miesiąc ;)
0
woolfik napisał(a):
artur_bredzki napisał(a):
Dziękuję za odpowiedzi.
a gdy wynik jest z wielu tabel, to by musiał być indeks 'wielotabelowy', a takich chyba na razie
bazy nie obsługują:Ech ... chyba nie do końca rozumiesz jak działają indeksy - pomijając ten fakt to uwierz, że postgresql sobie z tym radzi i to całkiem dobrze jak poprzednik napisał.
Hmmm nie pozostawiłeś na mnie suchej nitki, ale to dobrze, może czegoś się dowiem. Na pewno nie wiem wszystkiego o indeksach.
Pokaż mi jakie mam założyć indeksy, aby przyspieszyć zapytania z distinct.
Wykonuję 8 zapytań przed założeniem indeksów, a potem po założeniu. Oto ich czasy:
Bez indeksów
Time: 0,896 ms
Time: 947,169 ms
Time: 1242,373 ms distinct
Time: 38683,120 ms distinct
Time: 2534,140 ms
Time: 2413,927 ms
Time: 50939,324 ms distinct
Time: 89246,513 ms distinct
Z indeksami
Time: 0,917 ms
Time: 0,446 ms
Time: 1,945 ms distinct
Time: 38796,605 ms distinct
Time: 0,474 ms
Time: 2426,493 ms
Time: 50877,723 ms distinct
Time: 89308,702 ms distinct
Jak widać na 4 zapytania z distinct, tylko jedno przyspieszyło.
Jakie mam założyć indeksy, aby przyspieszyły wszystkie?
Poniżej przykładowy skrypt
--cars
\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;
DROP DATABASE IF EXISTS test1;
DROP ROLE IF EXISTS test1;
CREATE ROLE test1 PASSWORD 'test1' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test1 OWNER test1 ENCODING 'UTF-8';
\c test1;
set role test1;
CREATE TABLE countries (
id serial NOT NULL,
name character varying(10) NOT NULL
);
CREATE TABLE users (
id serial NOT NULL,
id_country integer NOT NULL,
name character varying(10) NOT NULL
);
CREATE TABLE cars (
id serial NOT NULL,
name character varying(10) NOT NULL
);
CREATE TABLE users_cars (
id serial NOT NULL,
id_user integer NOT NULL,
id_car integer NOT NULL
);
create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{A,B,C,D,E,F,G,H}';
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;
create or replace function mk_countries(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into countries (name) values( random_string(5) )' ;
end loop;
end;
$$ language plpgsql;
create or replace function mk_users(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into users (id_country,name) values( 1+random()*299 , random_string(4) )';
end loop;
end;
$$ language plpgsql;
begin;
select mk_countries(300);
select mk_users(3000000);
commit;
ALTER TABLE ONLY countries ADD CONSTRAINT countries_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_id_country_fkey FOREIGN KEY (id_country) REFERENCES countries(id);
ALTER TABLE ONLY cars ADD CONSTRAINT cars_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users_cars ADD CONSTRAINT users_cars_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users_cars ADD CONSTRAINT users_cars_id_user_fkey FOREIGN KEY (id_user) REFERENCES users(id);
ALTER TABLE ONLY users_cars ADD CONSTRAINT users_cars_id_car_fkey FOREIGN KEY (id_car) REFERENCES cars(id);
\timing on
select * from countries order by name limit 10;
select * from users order by name limit 10;
select distinct name from users order by name limit 10;
select count( distinct name ) from users;
select * from users as u join countries as c on c.id = u.id_country order by u.name limit 10;
select * from users as u join countries as c on c.id = u.id_country order by u.name,c.name limit 10;
select distinct u.name,c.name from users as u join countries as c on c.id = u.id_country order by u.name,c.name limit 10;
select count(distinct (u.name,c.name) ) from users as u join countries as c on c.id = u.id_country;
CREATE INDEX countries_name ON countries(name);
CREATE INDEX users_name ON users(name);
select * from countries order by name limit 10;
select * from users order by name limit 10;
select distinct name from users order by name limit 10;
select count( distinct name ) from users;
select * from users as u join countries as c on c.id = u.id_country order by u.name limit 10;
select * from users as u join countries as c on c.id = u.id_country order by u.name,c.name limit 10;
select distinct u.name,c.name from users as u join countries as c on c.id = u.id_country order by u.name,c.name limit 10;
select count(distinct (u.name,c.name) ) from users as u join countries as c on c.id = u.id_country;
2
Ale człowieku, nie zakładaj losowych indeksów na przypadkowe pola. Kompletnie nie rozumiesz co robisz. Przeczytaj jak działa indeks, jaki wpływ na użycie go mają obecne w nim pola i ich kolejność, czym różni się pole mające wpływ na kolejność danych w indeksie od pola dołączonego do indeksu...
To co zrobiłeś przypomina wklepanie przypadkowych poleceń i zdziwienie, że program nie działa.
Zakładając indeks tylko na pole nazwa utworzyłeś taką pseudotabelkę zawierającą kolumnę nazwa, odnośniki do pełnych rekordów z prawdziwej tabelki i fizycznie posortowaną po wartościach z jedynej kolumny. Jak wyobrażasz sobie użycie takiego indeksu, kiedy w zapytaniu złączasz tabelę z inną tabelą po polu nie uwzględnionym w indeksie? Optymalizator najprawdopodobniej nie użyje tego indeksu, a jeśli mu odbije i to zrobi, to i tak najpierw będzie musiał odwołać się do tabeli fizycznej celem złączenia - w mssql nazywa się to key lookup i jest bardzo wolne.
To, że select * from countries order by name limit 10; okazało się wolniejsze wynika z tego, że nie rozumiesz co robisz. Chcesz wyciągnąć wszystkie pola, ale w indeksie masz tylko jedno. Optymalizator njapewniej oleje indeks (wtedy podany przez Ciebie dłuższy czas to kwestia przypadku), albo użyje go, ale zrobi key lookup, bo przecież w indeksie nie ma co najmniej jednego pola potrzebnego do wykonania zapytania. Key lookup jest wolne i stąd nieco dłuższy czas.
Bardzo dużo też zależy od ilości danych i tego, co sobie postgresql zbuforował w statystykach. Mssql robi tak, że buduje plan zapytania i buforuje go na podstawie literalnie treści zapytania, jeśli wykonasz zapytanie, dodasz indeks i ponownie wykonasz identyczne zapytanie, to serwer użyje zbuforowanego planu ignorując stworzone indeksy.
Wszystko sprowadza się do użycia bardziej wyrafinowanego narzędzia diagnostycznego niż pomiar czasu - jest nim plan zapytania, o którym pisałem, a który kompletnie zignorowałeś. W postgresql do wyświetlenia planu służy polecenie explain. Mając plan zapytania widzisz jakie operacje były wykonane i ile zasobów zajęły, w ten sposób sprawdzisz też, czy Twój indeks w ogóle został użyty i w jaki sposób.
0
ŁF napisał(a):
Ale człowieku, nie zakładaj losowych indeksów na przypadkowe pola.
Ale człowieku, jak to na przypadkowe pola, jak rapotem poza id jest jedno pole w tabelach?
ŁF napisał(a):
Kompletnie nie rozumiesz co robisz.
Wydaje Ci się.
ŁF napisał(a):
Przeczytaj jak działa indeks, jaki wpływ na użycie go mają obecne w nim pola i ich kolejność, czym różni się pole mające wpływ na kolejność danych w indeksie od pola dołączonego do indeksu...
To co zrobiłeś przypomina wklepanie przypadkowych poleceń i zdziwienie, że program nie działa.
Jak to nie działa, przecież 5 zapytań przyspieszyło! Może najpierw przeczytaj ze zrozumieniem co zrobiłem, a potem krytykuj?
ŁF napisał(a):
Zakładając indeks tylko na pole
nazwautworzyłeś taką pseudotabelkę zawierającą kolumnęnazwa, odnośniki do pełnych rekordów z prawdziwej tabelki i fizycznie posortowaną po wartościach z jedynej kolumny.
No przecież
ŁF napisał(a):
Jak wyobrażasz sobie użycie takiego indeksu, kiedy w zapytaniu złączasz tabelę z inną tabelą po polu nie uwzględnionym w indeksie?
Opieprzasz mnie że nic nie rozumiem, a nie wiesz, że postgres sam zakłada indeksy na klucze główne :(
TO może ja Ciebie czegoś na uczę, zobacz:
test1=# SELECT i.relname as indname,
test1-# i.relowner as indowner,
test1-# idx.indrelid::regclass,
test1-# am.amname as indam,
test1-# idx.indkey,
test1-# ARRAY(
test1(# SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
test1(# FROM generate_subscripts(idx.indkey, 1) as k
test1(# ORDER BY k
test1(# ) as indkey_names,
test1-# idx.indexprs IS NOT NULL as indexprs,
test1-# idx.indpred IS NOT NULL as indpred
test1-# FROM pg_index as idx
test1-# JOIN pg_class as i
test1-# ON i.oid = idx.indexrelid
test1-# JOIN pg_am as am
test1-# ON i.relam = am.oid
test1-# JOIN pg_namespace as ns
test1-# ON ns.oid = i.relnamespace
test1-# AND ns.nspname = ANY(current_schemas(false))
test1-# ORDER BY i.relname;
indname | indowner | indrelid | indam | indkey | indkey_names | indexprs | indpred
-----------------+----------+------------+-------+--------+--------------+----------+---------
cars_pkey | 43021 | cars | btree | 1 | {id} | f | f
countries_name | 43021 | countries | btree | 2 | {name} | f | f
countries_pkey | 43021 | countries | btree | 1 | {id} | f | f
users_cars_pkey | 43021 | users_cars | btree | 1 | {id} | f | f
users_name | 43021 | users | btree | 3 | {name} | f | f
users_pkey | 43021 | users | btree | 1 | {id} | f | f
(6 rows)
Po jakich polach są złączenia? Na jakich polach są indeksy?
ŁF napisał(a):
Optymalizator najprawdopodobniej nie użyje tego indeksu, a jeśli mu odbije i to zrobi, to i tak najpierw będzie musiał odwołać się do tabeli fizycznej celem złączenia - w mssql nazywa się to key lookup i jest bardzo wolne.
Nie użyje, ale kilka zapytań przyspieszyło i to znacznie. Oczywiście że używa. Nie przyspieszyło to, o co pytałem, czyli distinct.
ŁF napisał(a):
To, że
select * from countries order by name limit 10;okazało się wolniejsze wynika z tego, że nie rozumiesz co robisz.
Może to Ty nie rozumiesz, czas tego zapytania jest krótki, może to błąd pomiaru, może to przypadek, może akurat dane nie były w cache, nie wiem
czemu na to zwróciłeś uwagę, bo wątek jest o optymalizacji distinct a w tym zapytaniu nie ma distinct.
ŁF napisał(a):
Chcesz wyciągnąć wszystkie pola, ale w indeksie masz tylko jedno.
Już wyżej pisałem, na klucze prywatne są indeksy z automatu.
ŁF napisał(a):
Optymalizator njapewniej oleje indeks (wtedy podany przez Ciebie dłuższy czas to kwestia przypadku),
Jeśli obaj wiemy że to kwestia przypadku, to czemu się uczepiłeś tego zapytania? Te zapytania były tylko po to, aby sprawdzić czy w ogóle indeksy
są poprawnie założone. Krajów jest raptem 300, to co tam indeks może przyspieszyć?
ŁF napisał(a):
albo użyje go, ale zrobi key lookup, bo przecież w indeksie nie ma co najmniej jednego pola potrzebnego do wykonania zapytania.
Już to pisałeś i odpowiadam to samo: nieprawda.
ŁF napisał(a):
Key lookup jest wolne i stąd nieco dłuższy czas.
Są różne potencjalne przyczyny tego że czas się wydlużył, nie wiem jaka była prawdziwa. Ja bym postawił na to, że indeks ma narzut liniowy i dla
300 rekordów zaobserwuje się spowolnienie a nie przyspieszenie. Ale ten wątek nie dotyczy tego przypadku, tylko distinct.
ŁF napisał(a):
Bardzo dużo też zależy od ilości danych i tego, co sobie postgresql zbuforował w statystykach. Mssql robi tak, że buduje plan zapytania i buforuje go na podstawie literalnie treści zapytania, jeśli wykonasz zapytanie, dodasz indeks i ponownie wykonasz identyczne zapytanie, to serwer użyje zbuforowanego planu ignorując stworzone indeksy.
Zależność od ilości danych jest zazwyczaj prosta: im więcej danych tym lepszy efekt daje założenie indeksu. No ale na distinct efek był tylko raz na cztery razy, nie wiem dlaczeg. Reszta mnie
nie interesuje bo jest oczywista lub nieistotna.
ŁF napisał(a):
Wszystko sprowadza się do użycia bardziej wyrafinowanego narzędzia diagnostycznego niż pomiar czasu - jest nim plan zapytania, o którym pisałem, a który kompletnie zignorowałeś. W postgresql do wyświetlenia planu służy polecenie explain. Mając plan zapytania widzisz jakie operacje były wykonane i ile zasobów zajęły, w ten sposób sprawdzisz też, czy Twój indeks w ogóle został użyty i w jaki sposób.
Dlatego wkleiłem cały skrypt, żeby ktoś kto rozumie w czym problem miał ułatwione zadanie i żeby pokazał konkretnie jakie indeksy trzeba założyć. A Ty żadnych konkretów nie podałeś, tylko mnie opieprzyleś i próbujesz wprowadzic mnie w błąd, że jest brak indeksów na niektóre pola. :(
0
artur_bredzki napisał(a):
ŁF napisał(a):
Ale człowieku, nie zakładaj losowych indeksów na przypadkowe pola.
Ale człowieku, jak to na przypadkowe pola, jak rapotem poza id jest jedno pole w tabelach?
Indeks powinien być na obydwa.
artur_bredzki napisał(a):
ŁF napisał(a):
Przeczytaj jak działa indeks, jaki wpływ na użycie go mają obecne w nim pola i ich kolejność, czym różni się pole mające wpływ na kolejność danych w indeksie od pola dołączonego do indeksu...
To co zrobiłeś przypomina wklepanie przypadkowych poleceń i zdziwienie, że program nie działa.Jak to nie działa, przecież 5 zapytań przyspieszyło! Może najpierw przeczytaj ze zrozumieniem co zrobiłem, a potem krytykuj?
Przyspieszyły nieznacznie i nie wszystkie, stąd mój wniosek, że nie osiągnąłeś oczekiwanego rezultatu.
artur_bredzki napisał(a):
ŁF napisał(a):
Zakładając indeks tylko na pole
nazwautworzyłeś taką pseudotabelkę zawierającą kolumnęnazwa, odnośniki do pełnych rekordów z prawdziwej tabelki i fizycznie posortowaną po wartościach z jedynej kolumny.No przecież
To cieszę się, że chociaż tutaj się zgadzamy :) Więc teraz jak wyobrażasz sobie wyciągnięcie z tego indeksu posiadającego jedno pole nazwa pole id potrzebne do złączeń z innymi tabelami? Odpowiem: optymalizator zignoruje indeks i sięgnie do tabeli, albo zrobi key lookup.
artur_bredzki napisał(a):
ŁF napisał(a):
Jak wyobrażasz sobie użycie takiego indeksu, kiedy w zapytaniu złączasz tabelę z inną tabelą po polu nie uwzględnionym w indeksie?
Opieprzasz mnie że nic nie rozumiem, a nie wiesz, że postgres sam zakłada indeksy na klucze główne :(
Ale Ty naprawdę nie rozumiesz detali. To są osobne indeksy! Różne byty! Co z tego, że masz indeks na klucz główny, skoro to jest osobny indeks. Słowo klucz: osobny. Teraz zakładasz sobie drugi indeks na któreś z pól, w indeksie - jak sam pokazałeś - nie będzie już dostępne pole z kluczem głównym. Będzie ono w osobnym indeksie. Wyobraź sobie każdy indeks i tabelę jako osobne "pliki", teraz masz plik tabelka posortowany losowo (chociaż IMHO dane powinny być fizycznie w takiej kolejności, jaką wyznacza primary key - w mssql nie ma dodatkowego indeksu na klucz główny, bo klucz główny jest clustered, a indeksy clustered są tożsame z fizyczną kolejnością danych w tabelce)... Wracając do tematu: osobno plik tabelka, osobny plik id, osobno plik nazwa. Teraz chcąc skorzystać z indeksu nazwa, ale jednocześnie wyświetlić pole id lub użyć go do złączenia z inną tabelką musisz odwołać się do innego pliku - tabelka albo id, przy czym żeby użyć id i tak musisz przejść przez plik tabelka. Każde takie przejście kosztuje, a wykonane tysiące lub miliony razy znacznie wydłuża czas wykonania zapytania.
To, że kolumna jest w innym indeksie nie spowoduje, że będzie dostępna dla wszystkich innych indeksów. Jeśli chcesz zrobić select * from a where a.b = c i chcesz efektywnie użyć do tego indeksu, to indeks musi być posortowany po b i musi zawierać WSZYSTKIE pola z tabelki, nawet jeśli masz inne indeksy zawierające sumarycznie wszystkie pola tej tabelki.
artur_bredzki napisał(a):
TO może ja Ciebie czegoś na uczę, zobacz:
indname | indowner | indrelid | indam | indkey | indkey_names | indexprs | indpred
-----------------+----------+------------+-------+--------+--------------+----------+---------
cars_pkey | 43021 | cars | btree | 1 | {id} | f | f
countries_name | 43021 | countries | btree | 2 | {name} | f | f
countries_pkey | 43021 | countries | btree | 1 | {id} | f | f
users_cars_pkey | 43021 | users_cars | btree | 1 | {id} | f | f
users_name | 43021 | users | btree | 3 | {name} | f | f
users_pkey | 43021 | users | btree | 1 | {id} | f | f
(6 rows)
Po jakich polach są złączenia? Na jakich polach są indeksy?
Jestem chętny do nauki nowych rzeczy, ale tu nic takiego nie widzę - ot, sześć indeksów z pojedynczymi polami. Naprawdę nie widzisz różnicy pomiędzy jednym indeksem (id, name) a dwoma (id), (name)? Dodam też, że indeks (id, name) to co innego niż (name, id), a te dwa to kompletnie co innego niż (id + include na name) i jeszcze co innego niż (name + include na id).
artur_bredzki napisał(a):
ŁF napisał(a):
Optymalizator najprawdopodobniej nie użyje tego indeksu, a jeśli mu odbije i to zrobi, to i tak najpierw będzie musiał odwołać się do tabeli fizycznej celem złączenia - w mssql nazywa się to key lookup i jest bardzo wolne.
Nie użyje, ale kilka zapytań przyspieszyło i to znacznie. Oczywiście że używa. Nie przyspieszyło to, o co pytałem, czyli distinct.
Cieszę się, że coś zadziałało szybciej, ale z dobrze założonymi indeksami powinieneś mieć spadek czasu wykonania o kilka rzędów wielkości, a nie o kilka czy kilkadziesiąt procent.
Optymalizując świadomie będziesz rozumiał, że coś przegapiasz i nie zadowolisz się połowicznym rozwiązaniem. Strzelając w ciemno będziesz się zastanawiał, dlaczego część zapytań przyspieszyła, a część zwolniła.
artur_bredzki napisał(a):
ŁF napisał(a):
To, że
select * from countries order by name limit 10;okazało się wolniejsze wynika z tego, że nie rozumiesz co robisz.Może to Ty nie rozumiesz, czas tego zapytania jest krótki, może to błąd pomiaru, może to przypadek, może akurat dane nie były w cache, nie wiem
czemu na to zwróciłeś uwagę, bo wątek jest o optymalizacji distinct a w tym zapytaniu nie ma distinct.
To nie ma znaczenia. Jest top i sortowanie po polu z indeksu, zapytanie powinno pójść jak burza, a tymczasem zwolniło. Brak zrozumienia działania optymalizatora i niesprawdzenie planu zapytania oznaczają, że czy to distinct, czy order by, na obu polegniesz.
artur_bredzki napisał(a):
ŁF napisał(a):
Chcesz wyciągnąć wszystkie pola, ale w indeksie masz tylko jedno.
Już wyżej pisałem, na klucze prywatne są indeksy z automatu.
Nie rozumiemy się. W indeksie (liczba pojedyncza) masz JEDNO pole, a potrzebujesz mieć w indeksie (liczba pojedyncza) pola DWA. BTW klucz prywatny to chyba w kryptografii w szyfrowaniu asymetrycznym, tu miałeś na myśli klucz główny?
artur_bredzki napisał(a):
ŁF napisał(a):
Optymalizator njapewniej oleje indeks (wtedy podany przez Ciebie dłuższy czas to kwestia przypadku),
Jeśli obaj wiemy że to kwestia przypadku, to czemu się uczepiłeś tego zapytania? Te zapytania były tylko po to, aby sprawdzić czy w ogóle indeksy
są poprawnie założone. Krajów jest raptem 300, to co tam indeks może przyspieszyć?
ja tego nie wiem. Napisałem, że to być może kwestia przypadku. "Uczepiłem się" tego zapytania, bo jest pierwsze z brzegu, jest proste i łatwo na nim przetestować podstawy indeksów i planów zapytań. Co indeks może przyspieszyć w takim wypadku? Index seek (ewentualnie index scan) zastąpi table scan, co przy iloczynie kartezjańskim przy złączeniu z kilkoma innymi tabelkami może pomóc zdeptać koszt zapytania o rząd wielkości lub więcej. Nie sprawdzisz - nie dowiesz się.
artur_bredzki napisał(a):
ŁF napisał(a):
albo użyje go, ale zrobi key lookup, bo przecież w indeksie nie ma co najmniej jednego pola potrzebnego do wykonania zapytania.
Już to pisałeś i odpowiadam to samo: nieprawda.
Zakład?
artur_bredzki napisał(a):
ŁF napisał(a):
Key lookup jest wolne i stąd nieco dłuższy czas.
Są różne potencjalne przyczyny tego że czas się wydlużył, nie wiem jaka była prawdziwa. Ja bym postawił na to, że indeks ma narzut liniowy i dla
300 rekordów zaobserwuje się spowolnienie a nie przyspieszenie. Ale ten wątek nie dotyczy tego przypadku, tylko distinct.
Sam jesteś narzut liniowy ;-) Prawda jest taka, że nie spojrzałeś na plan zapytania, więc obaj nie wiemy co tam się stało, ale ja wiem co na pewno się nie stało: indeks nie ma narzutu liniowego. Niewłaściwy indeks użyty przez bazę danych zostanie zignorowany, albo wymusi key lookup. W pewnych sytuacjach key lookup jest złem koniecznym i mimo zajścia tego zjawiska zapytanie i tak zostanie przyspieszone, często jednak key lookup degraduje wartość indeksu.
artur_bredzki napisał(a):
Zależność od ilości danych jest zazwyczaj prosta: im więcej danych tym lepszy efekt daje założenie indeksu. No ale na distinct efek był tylko raz na cztery razy, nie wiem dlaczeg. Reszta mnie
nie interesuje bo jest oczywista lub nieistotna.
Nie wiesz dlaczego, bo Cię to nie interesuje, wydaje Ci się oczywiste (moim zdaniem błędnie) albo nieistotne (również moim zdaniem błędnie). Skoro masz skromną wiedzę w temacie, to dlaczego uważasz, że pewne rzeczy mogę być nieistotne - przecież brakuje Ci wiedzy do ocenienia tego.
artur_bredzki napisał(a):
ŁF napisał(a):
Wszystko sprowadza się do użycia bardziej wyrafinowanego narzędzia diagnostycznego niż pomiar czasu - jest nim plan zapytania, o którym pisałem, a który kompletnie zignorowałeś. W postgresql do wyświetlenia planu służy polecenie explain. Mając plan zapytania widzisz jakie operacje były wykonane i ile zasobów zajęły, w ten sposób sprawdzisz też, czy Twój indeks w ogóle został użyty i w jaki sposób.
Dlatego wkleiłem cały skrypt, żeby ktoś kto rozumie w czym problem miał ułatwione zadanie i żeby pokazał konkretnie jakie indeksy trzeba założyć. A Ty żadnych konkretów nie podałeś, tylko mnie opieprzyleś i próbujesz wprowadzic mnie w błąd, że jest brak indeksów na niektóre pola. :(
Nie wprowadzam Cię w błąd. Podałem konkrety - w indeksach brakuje pól, ale Ty wiesz lepiej albo czytasz nieuważnie - brakuje nie indeksów na pola, a pól w indeksach.
Skąd taka pewność siebie, skoro przychodzisz na forum z prośbą o pomoc w temacie, z którym sobie nie radzisz? Przychodzi ktoś bardziej doświadczony, a Ty z nim polemizujesz zamiast starać się zrozumieć. Owszem, nie jestem alfą i omegą, jednak mam pewne doświadczenie z kilkoma serwerami baz danych i zwykle wiem, o czym piszę, nawet jeśli robię to przez analogię z innego serwera bd.
Inna sprawa, że liczysz najwyraźniej na gotowca. Nic z tego. My na tym forum rozdajemy wędki, a nie ryby. Nikt nie będzie ślęczeć za Ciebie nad Twoją bazą danych, chyba że za to zapłacisz. Jeśli wyślesz mi sql tworzący bazę danych i dodający do niej dane, to zrobię Ci to za jedyne 100zł/h netto.
0
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Ale człowieku, nie zakładaj losowych indeksów na przypadkowe pola.
Ale człowieku, jak to na przypadkowe pola, jak rapotem poza id jest jedno pole w tabelach?
Indeks powinien być na obydwa.
Widzisz, jakbyś napisał normalnym tonem, że trzeba założyć jeden indeks obejmujący dwie kolumny tabeli, to
pewnie bym zrozumiał co masz na myśli. A gdy piszesz że zakładam losowe indeksy to jak to można zrozumieć?
Nie ma problemu, można zrobić eksperyment z takim indeksem. Moim zdaniem nie pomogą, poniżej napiszę
dlaczego.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Przeczytaj jak działa indeks, jaki wpływ na użycie go mają obecne w nim pola i ich kolejność, czym różni się pole mające wpływ na kolejność danych w indeksie od pola dołączonego do indeksu...
To co zrobiłeś przypomina wklepanie przypadkowych poleceń i zdziwienie, że program nie działa.Jak to nie działa, przecież 5 zapytań przyspieszyło! Może najpierw przeczytaj ze zrozumieniem co zrobiłem, a potem krytykuj?
Przyspieszyły nieznacznie i nie wszystkie, stąd mój wniosek, że nie osiągnąłeś oczekiwanego rezultatu.
Ten wątek jest o optymalizacji zapytań z distinct. Tylko jedno zapytanie na cztery istotnie przyspieszyło. Zapytania bez distinct były
tylko po to, aby się upewnić, czy jakiejś literówki nie zrobiłem i czy indeksy w ogóle są poprawnie założone. Jeśli przeczytałeś
skrypt, to zauważyłeś, że zapytania bez distinct znacznie przyspieszyły.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Zakładając indeks tylko na pole
nazwautworzyłeś taką pseudotabelkę zawierającą kolumnęnazwa, odnośniki do pełnych rekordów z prawdziwej tabelki i fizycznie posortowaną po wartościach z jedynej kolumny.No przecież
To cieszę się, że chociaż tutaj się zgadzamy :) Więc teraz jak wyobrażasz sobie wyciągnięcie z tego indeksu posiadającego jedno pole
nazwapoleidpotrzebne do złączeń z innymi tabelami? Odpowiem: optymalizator zignoruje indeks i sięgnie do tabeli, albo zrobi key lookup.
Wyobrażam sobie to w taki sposób: sortujemy po nazwie. Więc baza powinna użyć indeksu założonego na nazwę. Wyszukuje pierwszy rekord w kierunku
sortowania. W rekordzie obok nazwy ma także id, a jeśli silnik bazy danych rozbija tabelę na wiele tabeli (jedno) kolumnowych, to w innej
kolumnie pod tym samym wskaźnikiem ma rekord z id.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Jak wyobrażasz sobie użycie takiego indeksu, kiedy w zapytaniu złączasz tabelę z inną tabelą po polu nie uwzględnionym w indeksie?
Opieprzasz mnie że nic nie rozumiem, a nie wiesz, że postgres sam zakłada indeksy na klucze główne :(
Ale Ty naprawdę nie rozumiesz detali. To są osobne indeksy! Różne byty! Co z tego, że masz indeks na klucz główny, skoro to jest osobny indeks. Słowo klucz: osobny.
Na pewno nie rozumiem wszystkiego. I na pewno osobny indeks nie jest problem. No chyba że silnik bazy danych ma z tym problem.
Dlaczego osobny indeks nie jest problemem? Ano tak samo jak wyżej: Silniki na podstawie jednego indeksu robi sortowanie, na podstawie
drugiego złączenie. Dlaczego uważasz że musi być jeden indeks obejmujący dwa pola? Dlaczego Twoim zdaniem silnik
nie może zrobić tego na bazie dwóch osobnych indeksów?
ŁF napisał(a):
Teraz zakładasz sobie drugi indeks na któreś z pól, w indeksie - jak sam pokazałeś - nie będzie już dostępne pole z kluczem głównym.
Dlaczego uważasz, że silnik nie może id wyciągnąć na podstawie numeru rekordu? Moim zdaniem może i to robi.
ŁF napisał(a):
Będzie ono w osobnym indeksie. Wyobraź sobie każdy indeks i tabelę jako osobne "pliki",
O ile wiem, to nowe silniki nawet mogą każdą kolumnę jednej tabeli przechowywać w osobnym pliku. Zazwyczaj
rozbicie tabeli na N kolumnowej na N tabeli jednokolumnowych przyspiesza, ponieważ trzeba przeorać
mniejszą ilość danych - statystycznie częściej, nie zawsze. Ale to już jest straszny OT na inny wątek.
ŁF napisał(a):
teraz masz plik
tabelkaposortowany losowo (chociaż IMHO dane powinny być fizycznie w takiej kolejności, jaką wyznacza primary key - w mssql nie ma dodatkowego indeksu na klucz główny, bo klucz główny jest clustered, a indeksy clustered są tożsame z fizyczną kolejnością danych w tabelce)...
Tak samo uważam. Ale nie widzę związku z tym, że silnik musi mieć indeks obejmujący dwa pola.
ŁF napisał(a):
Wracając do tematu: osobno plik
tabelka, osobny plikid, osobno pliknazwa. Teraz chcąc skorzystać z indeksunazwa, ale jednocześnie wyświetlić poleidlub użyć go do złączenia z inną tabelką musisz odwołać się do innego pliku -tabelkaalboid, przy czym żeby użyćidi tak musisz przejść przez pliktabelka.
Moim zdaniem tak to działa: Mamy dwie tabele dwu kolumnowe czli cztery pliki z danymi. Silnik najpierw wyszuka na podstawie indeksu
nazwę. Okaże się, że nazwa jest w rekordzie o numerze N. Obliczy położenie id ze wzoru (rozmiar rekordu) * N.
Odczyta id. Następnie skorzysta z indeksu do obliczenia pozycji id klucza obcego w drugiej tabeli w
trzecim pliku. Powiedzmy że id klucza obcego jest w rekordzie numer M. Potem dociągnie nazwę z drugiej
tabeli z czwartego pliku. Położeni nazwy obliczy ze wzoru M * ( rozmiar rekordu ).
ŁF napisał(a):
Każde takie przejście kosztuje, a wykonane tysiące lub miliony razy znacznie wydłuża czas wykonania zapytania.
To, że kolumna jest w innym indeksie nie spowoduje, że będzie dostępna dla wszystkich innych indeksów. Jeśli chcesz zrobićselect * from a where a.b = ci chcesz efektywnie użyć do tego indeksu, to indeks musi być posortowany pobi musi zawierać WSZYSTKIE pola z tabelki, nawet jeśli masz inne indeksy zawierające sumarycznie wszystkie pola tej tabelki.
Może masz rację. Moim zdaniem nie masz, uzasadniałem powyżej 3 razy dlaczego. Ale zrobimy eksperyment, może
bazy nie działają zgodnie z moim wyobrażeniem.
ŁF napisał(a):
artur_bredzki napisał(a):
TO może ja Ciebie czegoś na uczę, zobacz:
indname | indowner | indrelid | indam | indkey | indkey_names | indexprs | indpred
-----------------+----------+------------+-------+--------+--------------+----------+---------
cars_pkey | 43021 | cars | btree | 1 | {id} | f | f
countries_name | 43021 | countries | btree | 2 | {name} | f | f
countries_pkey | 43021 | countries | btree | 1 | {id} | f | f
users_cars_pkey | 43021 | users_cars | btree | 1 | {id} | f | f
users_name | 43021 | users | btree | 3 | {name} | f | f
users_pkey | 43021 | users | btree | 1 | {id} | f | f
(6 rows)
Po jakich polach są złączenia? Na jakich polach są indeksy?
Jestem chętny do nauki nowych rzeczy, ale tu nic takiego nie widzę - ot, sześć indeksów z pojedynczymi polami. Naprawdę nie widzisz różnicy pomiędzy jednym indeksem (id, name) a dwoma (id), (name)? Dodam też, że indeks (id, name) to co innego niż (name, id), a te dwa to kompletnie co innego niż (id + include na name) i jeszcze co innego niż (name + include na id).
Widzę różnicę, ale inną niż Ty. Dla mnie indeks btree na jedno pole1 używa warunku sortowania
rekordY.pole1 < rekordX.pole1
Natomiast na dwa pola daje warunek:
rekordY.pole1 < rekordX.pole1 OR rekordY.pole1 = rekordX.pole1 AND rekordY.pole2 < rekordX.pole2
Jedyna tego zaleta może być taka, że w indeksie obok pola z nazwą jest pole id, ale dociągnięcie danych trwa
krótko, więc to nie jest duża zaleta, a i tak nie wiadomo czy bazy potrafią tę potencjalną zaletę wykorzystać.
Poza tym indeks który przechowuje dane ma większy rozmiar i jego przeglądanie wymaga odczytania większej
ilości danych - więc takie rozwiązanie ma także wady.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Optymalizator najprawdopodobniej nie użyje tego indeksu, a jeśli mu odbije i to zrobi, to i tak najpierw będzie musiał odwołać się do tabeli fizycznej celem złączenia - w mssql nazywa się to key lookup i jest bardzo wolne.
Nie użyje, ale kilka zapytań przyspieszyło i to znacznie. Oczywiście że używa. Nie przyspieszyło to, o co pytałem, czyli distinct.
Cieszę się, że coś zadziałało szybciej, ale z dobrze założonymi indeksami powinieneś mieć spadek czasu wykonania o kilka rzędów wielkości, a nie o kilka czy kilkadziesiąt procent.
Optymalizując świadomie będziesz rozumiał, że coś przegapiasz i nie zadowolisz się połowicznym rozwiązaniem. Strzelając w ciemno będziesz się zastanawiał, dlaczego część zapytań przyspieszyła, a część zwolniła.
Ale to jest banał. To tak samo jakbyś komuś powiedział, że optymalizując dobrze uzyska lepsze wyniki niż
optymalizując źle. Tak samo jak rada: zakładasz losowe indeksy nic nie wnosi, poza zamętem. Poza tym
niektóre zapytania właśnie przyspieszyły o kilka rzędów wielkości, czytasz chociaż to co napisałem?
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
To, że
select * from countries order by name limit 10;okazało się wolniejsze wynika z tego, że nie rozumiesz co robisz.Może to Ty nie rozumiesz, czas tego zapytania jest krótki, może to błąd pomiaru, może to przypadek, może akurat dane nie były w cache, nie wiem
czemu na to zwróciłeś uwagę, bo wątek jest o optymalizacji distinct a w tym zapytaniu nie ma distinct.To nie ma znaczenia. Jest top i sortowanie po polu z indeksu, zapytanie powinno pójść jak burza, a tymczasem zwolniło. Brak zrozumienia działania optymalizatora i niesprawdzenie planu zapytania oznaczają, że czy to distinct, czy order by, na obu polegniesz.
Czyli jednym słowem polecasz przeanalizowanie planu zapytania. Można przeanalizować, czemu nie.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Chcesz wyciągnąć wszystkie pola, ale w indeksie masz tylko jedno.
Już wyżej pisałem, na klucze prywatne są indeksy z automatu.
Nie rozumiemy się. W indeksie (liczba pojedyncza) masz JEDNO pole, a potrzebujesz mieć w indeksie (liczba pojedyncza) pola DWA. BTW klucz prywatny to chyba w kryptografii w szyfrowaniu asymetrycznym, tu miałeś na myśli klucz główny?
Tak, klucz główny, sorry za pomyłkę.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Optymalizator njapewniej oleje indeks (wtedy podany przez Ciebie dłuższy czas to kwestia przypadku),
Jeśli obaj wiemy że to kwestia przypadku, to czemu się uczepiłeś tego zapytania? Te zapytania były tylko po to, aby sprawdzić czy w ogóle indeksy
są poprawnie założone. Krajów jest raptem 300, to co tam indeks może przyspieszyć?ja tego nie wiem. Napisałem, że to być może kwestia przypadku. "Uczepiłem się" tego zapytania, bo jest pierwsze z brzegu, jest proste i łatwo na nim przetestować podstawy indeksów i planów zapytań. Co indeks może przyspieszyć w takim wypadku? Index seek (ewentualnie index scan) zastąpi table scan, co przy iloczynie kartezjańskim przy złączeniu z kilkoma innymi tabelkami może pomóc zdeptać koszt zapytania o rząd wielkości lub więcej. Nie sprawdzisz - nie dowiesz się.
W pierwszym zapytaniu nie ma joina, więc o jaki iloczyn kartezjański chodzi?
Przypomnę:
select * from countries order by name limit 10;
Sortuje po nazwie, id w ogóle nie używa, więc indeks jednokolumnowy na kolumnę name wydaje
się być idealny. A dociągnięcie pól ID powinno być szybkie, bo są w tym samym rekordzie co name.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
albo użyje go, ale zrobi key lookup, bo przecież w indeksie nie ma co najmniej jednego pola potrzebnego do wykonania zapytania.
Już to pisałeś i odpowiadam to samo: nieprawda.
Zakład?
Nie wiem o co chcesz się zakładać. Byś musiał precyzyjniej opisać, to może się założę.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Key lookup jest wolne i stąd nieco dłuższy czas.
Są różne potencjalne przyczyny tego że czas się wydlużył, nie wiem jaka była prawdziwa. Ja bym postawił na to, że indeks ma narzut liniowy i dla
300 rekordów zaobserwuje się spowolnienie a nie przyspieszenie. Ale ten wątek nie dotyczy tego przypadku, tylko distinct.Sam jesteś narzut liniowy ;-) Prawda jest taka, że nie spojrzałeś na plan zapytania, więc obaj nie wiemy co tam się stało, ale ja wiem co na pewno się nie stało: indeks nie ma narzutu liniowego. Niewłaściwy indeks użyty przez bazę danych zostanie zignorowany, albo wymusi key lookup. W pewnych sytuacjach key lookup jest złem koniecznym i mimo zajścia tego zjawiska zapytanie i tak zostanie przyspieszone, często jednak key lookup degraduje wartość indeksu.
Jeśli nie rozumiesz czym jest problem liniowego narzutu generalnie spotykany w algorytmach o mniejszej
złożoności asymptotycznej, to nie wiem czy nie szkoda czasu na dalszą rozmowę :(
ŁF napisał(a):
artur_bredzki napisał(a):
Zależność od ilości danych jest zazwyczaj prosta: im więcej danych tym lepszy efekt daje założenie indeksu. No ale na distinct efek był tylko raz na cztery razy, nie wiem dlaczeg. Reszta mnie
nie interesuje bo jest oczywista lub nieistotna.Nie wiesz dlaczego, bo Cię to nie interesuje, wydaje Ci się oczywiste (moim zdaniem błędnie) albo nieistotne (również moim zdaniem błędnie). Skoro masz skromną wiedzę w temacie, to dlaczego uważasz, że pewne rzeczy mogę być nieistotne - przecież brakuje Ci wiedzy do ocenienia tego.
Nie chodzi o to że w ogole uważam że pewne rzeczy są nieistotne, tylko w tym wątku są nieistotne. To miała być
rozmowa ściśle o optymalizacji distinct, a zaraz zaczniemy udowadniać kto ma dłuższego... Wszystko co napisałeś w
tym wątku można było sprowadzić do dwóch zdań:
- Spróbuj indeksów obejmujących dwa pola (nazwę i id)
- Sprawdź plan zapytania.
Reszta Twoich wypowiedzi albo jest nie na temat, albo wprowadza więcej zamętu niż wyjaśnia.
ŁF napisał(a):
artur_bredzki napisał(a):
ŁF napisał(a):
Wszystko sprowadza się do użycia bardziej wyrafinowanego narzędzia diagnostycznego niż pomiar czasu - jest nim plan zapytania, o którym pisałem, a który kompletnie zignorowałeś. W postgresql do wyświetlenia planu służy polecenie explain. Mając plan zapytania widzisz jakie operacje były wykonane i ile zasobów zajęły, w ten sposób sprawdzisz też, czy Twój indeks w ogóle został użyty i w jaki sposób.
Dlatego wkleiłem cały skrypt, żeby ktoś kto rozumie w czym problem miał ułatwione zadanie i żeby pokazał konkretnie jakie indeksy trzeba założyć. A Ty żadnych konkretów nie podałeś, tylko mnie opieprzyleś i próbujesz wprowadzic mnie w błąd, że jest brak indeksów na niektóre pola. :(
Nie wprowadzam Cię w błąd. Podałem konkrety - w indeksach brakuje pól, ale Ty wiesz lepiej albo czytasz nieuważnie - brakuje nie indeksów na pola, a pól w indeksach.
Skąd taka pewność siebie, skoro przychodzisz na forum z prośbą o pomoc w temacie, z którym sobie nie radzisz? Przychodzi ktoś bardziej doświadczony, a Ty z nim polemizujesz zamiast starać się zrozumieć. Owszem, nie jestem alfą i omegą, jednak mam pewne doświadczenie z kilkoma serwerami baz danych i zwykle wiem, o czym piszę, nawet jeśli robię to przez analogię z innego serwera bd.
Inna sprawa, że liczysz najwyraźniej na gotowca. Nic z tego. My na tym forum rozdajemy wędki, a nie ryby. Nikt nie będzie ślęczeć za Ciebie nad Twoją bazą danych, chyba że za to zapłacisz. Jeśli wyślesz mi sql tworzący bazę danych i dodający do niej dane, to zrobię Ci to za jedyne 100zł/h netto.
Odnośnie wprowadzenia w błąd, napisałeś:
Zakładając indeks tylko na pole nazwa
A tymczasem postgres założył także indeksy na id.
Pewność moja wynika stąd, że odpowiadasz nie na to, z czym sobie nie radzę.
Kolejna sprawa, nie liczę na żadnego gotowca, ani to nie jest moja baza. Napisałem właśnie po to cały
skrypt, aby nikt nie musiał ślęczeć i jest on tylko na potrzeby rozmowy, nie stanowi mojego oryginalnego
problemu. Na tym przykładzie precyzyjnie widać w czym tkwi problem, jakie są zapytania i jakie mają
czasy wykonania. Ten skrypt to gotowiec. Zakłada całą bazę, wpisuje dane i robi benchmark. Zapytania
zawierają jedną lub dwie tabelki mające raptem dwie kolumny! Jeśli ktoś musi długo ślęczeć nad
zapytaniem łączącym dwie tabele z dwiema kolumnami, to nie zatrudnię go za więcej niż 10zł na godzinę.
No dobra, trochę piany ubiliśmy, sam nie wiem czemu daję się wciągać w awantury, ale pora na jakieś konkrety.
| "indeksy jednokolumnowe na klucze główne" | "indeksy jednokolumnowe na wszystkie pola" | "indeksy jednokolumnowe na id plus cztery indeksy dwukolumnowe" | "indkesy jednokolumnowe na id, cztery indeksy dwukolumnowe, dwa indeksy jednokolumnowe na nazwy"
| 0,913 | 0,75 | 0,541 | 0,548
| 947,904 | 0,441 | 0,391 | 0,757
distinct | 1127,015 | 1,954 | 2,056 | 1,803
distinct | 39147,755 | 38784,654 | 38928,566 | 38804,814
| 2356,266 | 0,452 | 0,507 | 0,557
| 2411,407 | 2409,83 | 2414,411 | 2530,693
distinct | 50309,964 | 50074,538 | 50232,413 | 50284,76
distinct | 89523,647 | 89716,845 | 89098,895 | 89185,327
Jak widać samo założenie indeksów obejmujących dwa pola nie pomogło wyraźnie. Czyżbyś jednak nie miał racji?
Byś przegrał zakład, czy o coś innego chciałeś się zakładać?
Pozdrawiam
2
Widzisz, jakbyś napisał normalnym tonem
To nie przedszkole.
Jedyna tego zaleta może być taka, że w indeksie obok pola z nazwą jest pole id, ale dociągnięcie danych trwa
krótko, więc to nie jest duża zaleta, a i tak nie wiadomo czy bazy potrafią tę potencjalną zaletę wykorzystać.
Poza tym indeks który przechowuje dane ma większy rozmiar i jego przeglądanie wymaga odczytania większej
ilości danych - więc takie rozwiązanie ma także wady.
Nie rozumiesz działania indeksów pomimo wytłumaczenia. Być może moje tłumaczenie było kiepskie, w takim razie warto by doczytać o technicznych niuansach indeksów, a nie gdybać. "Nie wiadomo czy bazy potrafią tę potencjalną zaletę wykorzystać" - tu mnie rozbawiłeś, bo to Ty tego nie wiesz, ja akurat wiem. Lepiej, na podstawie swojej niewiedzy wyciągasz wnioski! "Nie wiadomo", więc pewnie nie korzystają. WTF?
"Dociągnięcie" danych może trwać krótko, ale nie musi. Zresztą skąd wiesz, że trwa krótko? Wiesz? A może sprawdziłeś? Key lookup to czasami i 80% kosztu zapytania. Nie wiesz tego, bo nie spojrzałeś do explain.
Tworzenie właściwych indeksów to sztuka, lecz nie z tego powodu, który podałeś. Większy ("szerszy") indeks wymaga wczytania większej ilości danych to bzdura, po pierwsze dysk i tak przeprowadza odczyt blokami po 512B (w SSD może być inaczej), po drugie znalezienie właściwej pozycji w jednym, dobrze założonym indeksie btree na wszystkie właściwe kolumny wymaga najpierw tylu operacji, ilu wymaga szukanie w indeksie na jedną kolumnę, dopiero po znalezieniu właściwego zakresu wartości pierwszej kolumny indeksu wyszukiwane są dalsze pola (bo na pewno wiesz jak działa binary tree?). Jeśli masz indeksy na pojedyncze kolumny, to tych dalszych pól nie ma, nie ma więc gdzie wyszukać rekordów pasujących pod warunki z zapytania, więc indeks był przydatny tylko częściowo i tu zaczyna się table scan, który jest najwolniejszym możliwym sposobem wyszukiwania danych.
Problem z tworzeniem indeksów jest zupełnie inny - wyobraź sobie co się dzieje w indeksie przy dodawaniu lub aktualizacji danych z zaindeksowanej tabeli - ot, indeks musi uwzględnić w swojej strukturze nowe dane. W pesymistycznym przypadku jeden prosty update może trwać wieki, bo doprowadzi do przebudowania całego dużego indeksu. Z tego powodu wypełnienie (fill factor) stron z indeksami powinno być w miarę niskie, w postgresql to bodajże 90%, czyli na stronie zostaje 10% miejsca na reorganizację danych i ich dopisanie, dzięki czemu zwykle reorganizacja indeksu to tylko reorganizacja pojedynczej strony danych. Dlatego właśnie tworzenie indeksów to sztuka, bo trzeba umieć przyspieszyć selecty jednocześnie jak najmniej zwalniając inserty, update'y i delete'y.
Dodam jeszcze, że czasem robi się indeksy, w których kolejność ustala tylko część kolumn, reszta jest zwykłym include'm, takimi śmieciami idąc Twoim tokiem rozumowania. Dlaczego tak się robi? Właśnie po to, żeby uniknąć kosztownych key lookupów.
Jak widać samo założenie indeksów obejmujących dwa pola nie pomogło wyraźnie. Czyżbyś jednak nie miał racji?
Spójrz ze zrozumieniem na plany zapytań, porównaj je, wklej tutaj, podyskutujemy. Na razie Twój warsztat sql to takie showmessage, a przecież masz dostępny debuger w postaci explain.
Podsumowując: widzę, że wszystko wiesz lepiej. Nie chce mi się przepychać z Tobą, może ktoś inny Ci pomoże.
0
Wkleiłem już zapytania, bazę i skrypt który wszystko budje...
No ale dobra, jeszcze raz.
Lista indeksów:
indname | indowner | indrelid | indam | indkey | indkey_names | indexprs | indpred
----------------+----------+-----------+-------+--------+--------------+----------+---------
countries_name | 43978 | countries | btree | 2 | {name} | f | f
countries_pkey | 43978 | countries | btree | 1 | {id} | f | f
users_name | 43978 | users | btree | 3 | {name} | f | f
users_pkey | 43978 | users | btree | 1 | {id} | f | f
Pierwsze zapytanie. Łączymy użytkowików z krajami i sortujemy.
select u.name, c.name from countries as c join users as u on u.id_country = c.id order by u.name offset 10000 limit 30;
Time: 43,428 ms
Czas 43ms, długi, ale akceptowalny.
Teraz to samo zapytanie, ale sortujemy po nazwie kraju:
select u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name offset 10000 limit 30;
Time: 1262,788 ms
Długo jak diabli. Sprawdźmy czas bez offsetu:
select u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name limit 30;
Time: 6,061 ms
Moja obawa że problemem jest offset była słuszna, dlatego w zapytaniach mojego autorstwa unikam offsetów.
Niestety teraz używam pluginu który używa offsetów i jestem na offsety skazany. Czyli już jest pierwszy
problem z wydajnością.
Prosiłeś o explain, proszę:
explain (analyze true, verbose true) select u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name offset 10000 limit 30;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000059828.27..10000060007.76 rows=30 width=11) (actual time=1673.312..1675.597 rows=30 loops=1)
Output: u.name, c.name
-> Nested Loop (cost=10000000000.15..10017948437.65 rows=3000000 width=11) (actual time=0.813..1674.992 rows=10030 loops=1)
Output: u.name, c.name
Join Filter: (c.id = u.id_country)
Rows Removed by Join Filter: 3004362
-> Index Scan using countries_name on public.countries c (cost=0.15..20.65 rows=300 width=10) (actual time=0.012..0.015 rows=2 loops=1)
Output: c.id, c.name
-> Materialize (cost=10000000000.00..10000075866.00 rows=3000000 width=9) (actual time=0.013..662.701 rows=1507196 loops=2)
Output: u.name, u.id_country
-> Seq Scan on public.users u (cost=10000000000.00..10000046217.00 rows=3000000 width=9) (actual time=0.008..545.188 rows=3000000 loops=1)
Output: u.name, u.id_country
Total runtime: 1690.656 ms
Nie wiem co tu można przyspieszyć. Jeśli mi doradzisz, będe wdzięczny.
No ale to właściwie dopiero początek zmagań, bo jeszcze nie było distinct, ani sortowania po polach z dwóch tabel :/
select u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
Time: 63005,119 ms
Masakra, czas 63 sekundy. Prosiłeś o explani, więc masz:
test1=> explain (analyze true, verbose true) select u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000301893.91..10000301893.98 rows=30 width=11) (actual time=63983.482..63983.624 rows=30 loops=1)
Output: u.name, c.name
-> Sort (cost=10000301868.91..10000309368.91 rows=3000000 width=11) (actual time=63934.358..63983.139 rows=10030 loops=1)
Output: u.name, c.name
Sort Key: c.name, u.name
Sort Method: external merge Disk: 61600kB
-> Hash Join (cost=10000000021.40..10000087488.40 rows=3000000 width=11) (actual time=0.328..1584.465 rows=3000000 loops=1)
Output: u.name, c.name
Hash Cond: (u.id_country = c.id)
-> Seq Scan on public.users u (cost=10000000000.00..10000046217.00 rows=3000000 width=9) (actual time=0.010..362.560 rows=3000000 loops=1)
Output: u.id, u.id_country, u.name
-> Hash (cost=17.65..17.65 rows=300 width=10) (actual time=0.303..0.303 rows=300 loops=1)
Output: c.name, c.id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Index Scan using countries_pkey on public.countries c (cost=0.15..17.65 rows=300 width=10) (actual time=0.015..0.182 rows=300 loops=1)
Output: c.name, c.id
Total runtime: 63995.425 ms
Teraz tak z ciekawości, tylko zmienię kolejność sortowania:
select u.name, c.name from countries as c join users as u on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
Time: 50728,334 ms
Czas minimalnie krótszy, zróbmy explain i tego zapytania:
explain (analyze true, verbose true) select u.name, c.name from countries as c join users as u on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000301893.91..10000301893.98 rows=30 width=11) (actual time=52831.213..52831.336 rows=30 loops=1)
Output: u.name, c.name
-> Sort (cost=10000301868.91..10000309368.91 rows=3000000 width=11) (actual time=52790.418..52830.845 rows=10030 loops=1)
Output: u.name, c.name
Sort Key: u.name, c.name
Sort Method: external merge Disk: 61600kB
-> Hash Join (cost=10000000021.40..10000087488.40 rows=3000000 width=11) (actual time=0.175..1633.824 rows=3000000 loops=1)
Output: u.name, c.name
Hash Cond: (u.id_country = c.id)
-> Seq Scan on public.users u (cost=10000000000.00..10000046217.00 rows=3000000 width=9) (actual time=0.005..371.223 rows=3000000 loops=1)
Output: u.id, u.id_country, u.name
-> Hash (cost=17.65..17.65 rows=300 width=10) (actual time=0.162..0.162 rows=300 loops=1)
Output: c.name, c.id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Index Scan using countries_pkey on public.countries c (cost=0.15..17.65 rows=300 width=10) (actual time=0.008..0.080 rows=300 loops=1)
Output: c.name, c.id
Total runtime: 52844.192 ms
I teraz z distinct:
select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by u.name offset 10000 limit 30;
Time: 51292,910 ms
Znowu masakra, 51 sekund, dajmy więc explain:
explain (analyze true, verbose true) select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by u.name offset 10000 limit 30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000564237.96..10000564238.52 rows=30 width=11) (actual time=51058.344..51058.512 rows=30 loops=1)
Output: u.name, c.name
-> Unique (cost=10000564050.86..10000586550.86 rows=1202578 width=11) (actual time=50992.061..51058.070 rows=10030 loops=1)
Output: u.name, c.name
-> Sort (cost=10000564050.86..10000571550.86 rows=3000000 width=11) (actual time=50992.060..51053.992 rows=15224 loops=1)
Output: u.name, c.name
Sort Key: u.name, c.name
Sort Method: external merge Disk: 61600kB
-> Hash Join (cost=10000000021.40..10000087488.40 rows=3000000 width=11) (actual time=0.328..1565.658 rows=3000000 loops=1)
Output: u.name, c.name
Hash Cond: (u.id_country = c.id)
-> Seq Scan on public.users u (cost=10000000000.00..10000046217.00 rows=3000000 width=9) (actual time=0.009..354.885 rows=3000000 loops=1)
Output: u.id, u.id_country, u.name
-> Hash (cost=17.65..17.65 rows=300 width=10) (actual time=0.303..0.303 rows=300 loops=1)
Output: c.name, c.id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Index Scan using countries_pkey on public.countries c (cost=0.15..17.65 rows=300 width=10) (actual time=0.016..0.182 rows=300 loops=1)
Output: c.name, c.id
Total runtime: 51071.474 ms
Jakbyś to zapytanie zoptymalizował, ja nie mam pomysłu, właśnie z tego typu problemem napisałem na forum.
Posortujmy po nazwie kraju:
select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name offset 10000 limit 30;
Time: 63006,057 ms
63 sekundy, czas znajomy z wersji bez distinct. Dajmy explain:
explain (analyze true, verbose true) select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name offset 10000 limit 30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000564237.96..10000564238.52 rows=30 width=11) (actual time=64399.271..64399.549 rows=30 loops=1)
Output: u.name, c.name
-> Unique (cost=10000564050.86..10000586550.86 rows=1202578 width=11) (actual time=64249.132..64399.077 rows=10030 loops=1)
Output: u.name, c.name
-> Sort (cost=10000564050.86..10000571550.86 rows=3000000 width=11) (actual time=64249.130..64391.924 rows=29496 loops=1)
Output: u.name, c.name
Sort Key: c.name, u.name
Sort Method: external merge Disk: 61600kB
-> Hash Join (cost=10000000021.40..10000087488.40 rows=3000000 width=11) (actual time=0.294..1594.908 rows=3000000 loops=1)
Output: u.name, c.name
Hash Cond: (u.id_country = c.id)
-> Seq Scan on public.users u (cost=10000000000.00..10000046217.00 rows=3000000 width=9) (actual time=0.009..360.019 rows=3000000 loops=1)
Output: u.id, u.id_country, u.name
-> Hash (cost=17.65..17.65 rows=300 width=10) (actual time=0.272..0.272 rows=300 loops=1)
Output: c.name, c.id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Index Scan using countries_pkey on public.countries c (cost=0.15..17.65 rows=300 width=10) (actual time=0.014..0.152 rows=300 loops=1)
Output: c.name, c.id
Total runtime: 64411.484 ms
Co tutaj proponujesz?
Ostatnie zapytanie, posortujemy po obu obu nazwach:
select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
Time: 62999,052 ms
Czas znowu 63s, absolutnie niedopuszczalny. Dajmy explain:
explain (analyze true, verbose true) select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10000564237.96..10000564238.52 rows=30 width=11) (actual time=64172.202..64172.479 rows=30 loops=1)
Output: u.name, c.name
-> Unique (cost=10000564050.86..10000586550.86 rows=1202578 width=11) (actual time=64022.221..64172.001 rows=10030 loops=1)
Output: u.name, c.name
-> Sort (cost=10000564050.86..10000571550.86 rows=3000000 width=11) (actual time=64022.220..64164.882 rows=29496 loops=1)
Output: u.name, c.name
Sort Key: c.name, u.name
Sort Method: external merge Disk: 61600kB
-> Hash Join (cost=10000000021.40..10000087488.40 rows=3000000 width=11) (actual time=0.175..1585.694 rows=3000000 loops=1)
Output: u.name, c.name
Hash Cond: (u.id_country = c.id)
-> Seq Scan on public.users u (cost=10000000000.00..10000046217.00 rows=3000000 width=9) (actual time=0.005..357.437 rows=3000000 loops=1)
Output: u.id, u.id_country, u.name
-> Hash (cost=17.65..17.65 rows=300 width=10) (actual time=0.162..0.162 rows=300 loops=1)
Output: c.name, c.id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Index Scan using countries_pkey on public.countries c (cost=0.15..17.65 rows=300 width=10) (actual time=0.008..0.103 rows=300 loops=1)
Output: c.name, c.id
Total runtime: 64185.406 ms
Pozdrawiam
0
Gościu, nie ogarniasz indexów...
Masz tutaj powiązanie dwóch tabel
u.id_country = c.id
index powinien być na tabele users pole id_country i poprawnie jest na countries pole id.
Zanim znów zaczniesz kogoś krytykować dowiedz się do czego stosuje się indexy, jakie są wady i zalety!
Powodzenia
0
Pierwsza sprawa: to nie ja zacząłem krytykowanie i rozmowę w tonie z lekka niegrzecznym.
Jeśli ktoś mi tak odpowiada, to nie dziw się że ja odpowiadam tak samo.
Druga sprawa: faktycznie zapomniałem w tej bazie o indeksie na id_country. W oryginalnej bazie
jest założony indeks na odpowiednik tego pola i działa koszmarnie wolno, więc wątpię aby
to rozwiązało problemy.
Trzecia sprawa, zobaczmy w praktyce co da założenie tego indexu, może masz rację.
Więc załóżmy to index na to pole:
create index users_id_country on users (id_country );
indname | indowner | indrelid | indam | indkey | indkey_names | indexprs | indpred
------------------+----------+-----------+-------+--------+--------------+----------+---------
countries_name | 43978 | countries | btree | 2 | {name} | f | f
countries_pkey | 43978 | countries | btree | 1 | {id} | f | f
users_id_country | 43978 | users | btree | 2 | {id_country} | f | f
users_name | 43978 | users | btree | 3 | {name} | f | f
users_pkey | 43978 | users | btree | 1 | {id} | f | f
Sprawdźmy czasy:
select distinct u.name, c.name from countries as c join users as u on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
Time: 62324,794 ms
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
Time: 62394,461 ms
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
Time: 62788,471 ms
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
Time: 53764,102 ms
No i co kolego? Znowu odpowiedź niezbyt grzeczna, a rozwiązanie nic nie dało.
0
Bo założyłeś osobny indeks na każde z pól... No ileż można powtarzać, że dwa indeksy na dwa pola to zupełnie co innego od jednego indeksu na dwa pola. Nic nie poczytałeś, dalej jesteś w tym samym miejscu co dwa dni temu i jeszcze jesteś bezczelny - "No i co kolego?", "Czyżbyś jednak nie miał racji?".
Jeśli będę mieć czas jutro albo pojutrze to zerknę na te plany zapytań, to nie jest taka trywialna sprawa, tym bardziej dla mnie, bo na co dzień używam mssql. W tym czasie wysil się i wystaw gdzieś spakowany sql, którym utworzę sobie bazę z tabelami i danymi.
0
ŁF napisał(a):
Bo założyłeś osobny indeks na każde z pól...
Nie widzę problemu, na jakie pola proponujesz indeksy i w jakiej kolejności - założę z miłą chęcią.
ŁF napisał(a):
No ileż można powtarzać, że dwa indeksy na dwa pola to zupełnie co innego od jednego indeksu na dwa pola.
Tak się głupio składa, że od jakiś 10 lat lub może dłużej, rozróżniam te dwie kwestie.
ŁF napisał(a):
Nic nie poczytałeś, dalej jesteś w tym samym miejscu co dwa dni temu
Kombinowałem z indeksami na 2-3 pola zanim założyłem ten wątek i nie działało. Albo nie umiem, albo to nic nie daje. Dlatego pytam o
konkret: na jakie Twoim zdaniem pola należy założyć indeks?
ŁF napisał(a):
i jeszcze jesteś bezczelny - "No i co kolego?", "Czyżbyś jednak nie miał racji?".
A to nie jest bezczelne, powiedzieć komuś że nie ogarnia, a samemu podać rozwiązanie które nic nie pomaga?
To chyba znowu nie ja zacząłem tę bezczelność?
ŁF napisał(a):
Jeśli będę mieć czas jutro albo pojutrze to zerknę na te plany zapytań, to nie jest taka trywialna sprawa, tym bardziej dla mnie, bo na co dzień używam mssql.
Miło, dziękuję z góry.
</quote>ŁF napisał(a):
W tym czasie wysil się i wystaw gdzieś spakowany sql, którym utworzę sobie bazę z tabelami i danymi.
Nie ma problemu, podaję całość niżej. Trzeba zainstalować postgresa.
Potem trzeba uruchomić klienta poleceniem psql.
Po odpaleniu klienta wydajemy polecenie:
\i skrypt.sql
[backlash i nazwa pliku ze skryptem]
Wykonanie skryptu będzie trwało około 10 minut. Skrypt założy
dwie tabele: kraje i użytkownicy. Ilość krajów 300, ilość użytkowników 3mln.
Na końcu skrypt założy kilka indeksów i wykona zapytania testowe.
Po każdym zapytaniu wyświetli się czas zapytania, zazwyczaj czas
wynosi około 50s. Pytanie kluczowe, czy bazy potrafią wykorzystać
indeksy do takich zapytań? Czy w ogóle można założyć takie indeksy, które
przyspieszą te zapytania?
Tutaj cały skrypt:
--cars
\timing off
\pset pager 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;
DROP DATABASE IF EXISTS test1;
DROP ROLE IF EXISTS test1;
CREATE ROLE test1 PASSWORD 'test1' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test1 OWNER test1 ENCODING 'UTF-8';
\c test1;
set role test1;
CREATE TABLE countries (
id serial NOT NULL,
name character varying(10) NOT NULL
);
CREATE TABLE users (
id serial NOT NULL,
id_country integer NOT NULL,
name character varying(10) NOT NULL
);
create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{A,B,C,D,E,F,G,H}';
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;
create or replace function mk_countries(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into countries (name) values( random_string(5) )' ;
end loop;
end;
$$ language plpgsql;
create or replace function mk_users(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into users (id_country,name) values( 1+random()*299 , random_string(4) )';
end loop;
end;
$$ language plpgsql;
begin;
select mk_countries(300);
select mk_users(3000000);
commit;
ALTER TABLE ONLY countries ADD CONSTRAINT countries_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users ADD CONSTRAINT users_id_country_fkey FOREIGN KEY (id_country) REFERENCES countries(id);
CREATE INDEX countries_name ON countries(name);
CREATE INDEX users_name ON users(name);
CREATE INDEX users_id_country ON users(id_country);
SELECT i.relname as indname,
i.relowner as indowner,
idx.indrelid::regclass,
am.amname as indam,
idx.indkey,
ARRAY(
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
FROM generate_subscripts(idx.indkey, 1) as k
ORDER BY k
) as indkey_names,
idx.indexprs IS NOT NULL as indexprs,
idx.indpred IS NOT NULL as indpred
FROM pg_index as idx
JOIN pg_class as i
ON i.oid = idx.indexrelid
JOIN pg_am as am
ON i.relam = am.oid
JOIN pg_namespace as ns
ON ns.oid = i.relnamespace
AND ns.nspname = ANY(current_schemas(false))
ORDER BY i.relname;
\timing on
--przykłady zapytań
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name offset 10000 limit 30;
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
select u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
select distinct u.name from users as u order by u.name offset 10000 limit 30;
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by c.name,u.name offset 10000 limit 30;
select distinct u.name, c.name from users as u join countries as c on u.id_country = c.id order by u.name,c.name offset 10000 limit 30;
Pozdrawiam
0
- Te zapytania powyżej są bezwarunkowe, więc nie ma co optymalizować - rozsądny optymalizator po prostu zrobi full table scan na którejś tabeli z dokręceniem z drugiej.
- distinct to z reguły dodatkowy spowalniacz, przy czym tutaj totalnie bez sensu
- offset/limit nie pomagają w wydajności zapytania, co najwyżej mniej danych jest przesyłanych
- jest cała masa patentów na optymalizowanie złączeń, kilka przykładowych dla tego przypadku:
a) denormalizacja: wstaw kod kraju w pole "user.country_code" i wyświetlaj w aplikacji po ludzku stosując dane z konfiguracji (np. XML)
b) wymagaj podania co najmniej jednej litery z nazwy użytkownika lub kraju przed odpaleniem zapytania
c) zmień plugin - plugin który wstawia automatem "distinct" to jakaś zabawka a nie narzędzie
0
vpiotr napisał(a):
- Te zapytania powyżej są bezwarunkowe, więc nie ma co optymalizować - rozsądny optymalizator po prostu zrobi full table scan na którejś tabeli z dokręceniem z drugiej.
Nie wiem dlaczego full scan można nazwać rozsądnym, zwłaszcza że takie zapytanie można wykonać w ułamku sekundy.
vpiotr napisał(a):
- distinct to z reguły dodatkowy spowalniacz, przy czym tutaj totalnie bez sensu
A z sensem jest oglądanie powtarzających się danych? Chyba nie zawsze. Proszę, nie patrz na przykład użytkowników i
krajów zbyt dosłownie. Nie mogę oryginalnej struktury bazy wrzucić na forum.
vpiotr napisał(a):
- offset/limit nie pomagają w wydajności zapytania, co najwyżej mniej danych jest przesyłanych
Ale przy odpowiednich indeksach (a takich indeksów dzisiejsze bazy danych (o ile wiem) nie wspierają) offset i limit też nie
musi spowalniać zapytania.
vpiotr napisał(a):
- jest cała masa patentów na optymalizowanie złączeń, kilka przykładowych dla tego przypadku:
a) denormalizacja: wstaw kod kraju w pole "user.country_code" i wyświetlaj w aplikacji po ludzku stosując dane z konfiguracji (np. XML)
b) wymagaj podania co najmniej jednej litery z nazwy użytkownika lub kraju przed odpaleniem zapytania
c) zmień plugin - plugin który wstawia automatem "distinct" to jakaś zabawka a nie narzędzie
Denormalizacja faktycznie pomoże, ale skomplikuje strukturę bazy - tego chciałem uniknąć. Pluginu nie można zmienić, nie ma
odpowiednika.
Pozdrawiam
0
artur_bredzki napisał(a):
vpiotr napisał(a):
- Te zapytania powyżej są bezwarunkowe, więc nie ma co optymalizować - rozsądny optymalizator po prostu zrobi full table scan na którejś tabeli z dokręceniem z drugiej.
Nie wiem dlaczego full scan można nazwać rozsądnym, zwłaszcza że takie zapytanie można wykonać w ułamku sekundy.
"The situation when the a full table scan is very likely to perform better than an index scan and table lookup is when you are retrieving 10% or more of the data in the table and it may perform better even when you are retrieving as little as 1% of the table data. Of course if you only want to retrieve one row in the table, then you would want to use an index."
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part1_1.htm
0
Ten cytat nie opisuje tej sytuacji, ponieważ to zapytanie można byłoby zrealizować w bardzo krótkim czasie.
1
Miałem na to spojrzeć, ale nie miałem ani czasu, ani ochoty zrobić tego porządnie. Wstępne przyjrzenie się temu sugeruje, że wydajności grupowania po kolumnach z różnych tabel nie rozwiąże indeks. W MSSQL można - przy spełnieniu pewnych warunków - robić zindeksowane view, może Twoja baza danych na to pozwoli. W przeciwnym wypadku pozostaje Ci albo zmiana struktury danych w taki sposób, żeby zapewnić unikalność danych, albo - jeśli spodziewasz się, że zduplikowanych danych będzie względnie niedużo - możesz próbować obrabiać dane po stronie klienta bazy, ale w taki przypadku stronicowanie będzie praktycznie niewykonalne.
Jest jeszcze jeden sposób. Zapewne da się zmusić (hintami - http://pghintplan.osdn.jp/pg_hint_plan.html) optymalizator do użycia informacji, że dane są już posortowane po kolumnach, na których chcesz zrobić group by/distinct i tym tropem bym szedł.
0
ŁF napisał(a):
Miałem na to spojrzeć, ale nie miałem ani czasu, ani ochoty zrobić tego porządnie. Wstępne przyjrzenie się temu sugeruje, że wydajności grupowania po kolumnach z różnych tabel nie rozwiąże indeks.
Pisałem już dawno że sytuacja wydaje się beznadziejna.
ŁF napisał(a):
Jest jeszcze jeden sposób. Zapewne da się zmusić (hintami - http://pghintplan.osdn.jp/pg_hint_plan.html) optymalizator do użycia informacji, że dane są już posortowane po kolumnach, na których chcesz zrobić group by/distinct i tym tropem bym szedł.
Może to jest dobra droga, ale nie ma tego w standardowej instalacji. Nie wiem czy bym się odważył na tym polegać. Nie wiem choćby tego, czy w kolejnych wersjach bazy to będzie rozwijane,
czy będzie kompatybilne.
Pozdrawiam
Zarejestruj się i dołącz do największej społeczności programistów w Polsce.
Otrzymaj wsparcie, dziel się wiedzą i rozwijaj swoje umiejętności z najlepszymi.