[PGSQL] Jak efektywnie odpytać serwer [bardzo długie]
Robi - 24-12-2006 00:37
[PGSQL] Jak efektywnie odpytać serwer [bardzo długie]
Witam,
SQL: PostgreSQL 8.1.4
Problem dotyczy kilku tabel i na nich chciałbym się skupić.
Jest Tabela CV - przechowująca pewne dane z CV osoby (nazwę je brzydko
jednowierszone)
oraz kilka tabeli pomocniczych przechowujace dane wielowierszone np - znane
przez osobę języki, umiejętności, ukończone szkoły etc etc etc (Niżej
załączam definicje tabel)
Mam widok po ktorym chciałbym wyszukiwać konkretne grupy i tu pojawia się
problem - gdy szukanie ograniczam do danych z CV to nie ma problemu, jednak
gdy zaczynam szukac po tych tabelach pomocniczych to czas odpowiedzi jest -
po prostu za dlugi - idzie w godziny (nie doczekalem się końca).
Teraz konkrety:
Tabela CV - ilosc rekordów ~ 50.000
Tabele pomocnicze - kazda posiada 0-5 wierszy dla kazdego wiersza w tabeli
CV;
############Tabele : ###############
CREATE TABLE "CV"
(
"IDCV" int4 NOT NULL DEFAULT nextval('"CV_IDCV_seq"'::regclass),
"NAZWA_CV" text NOT NULL,
"IDOS" int4 NOT NULL,
"IDB" int4 NOT NULL,
"IDH" int4 NOT NULL,
"IDMWP" int4 NOT NULL,
"IDS" int4 NOT NULL,
"OBSLUGA_KOMPUTERA" text,
"POSIADA_SAMOCHOD" bool,
"WAZNE_DO" date NOT NULL,
"WYNAGRODZENIE" int4 NOT NULL DEFAULT 0,
"IDRU" int4 NOT NULL DEFAULT 0,
CONSTRAINT "CV_pkey" PRIMARY KEY ("IDCV"),
CONSTRAINT "CV_IDOS_fkey" FOREIGN KEY ("IDOS")
REFERENCES "OSOBY" ("IDOS") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
CREATE TABLE "CV_FIRMY"
(
"IDCVFI" int4 NOT NULL DEFAULT nextval('"CV_FIRMY_IDCVFI_seq"'::regclass),
"IDCV" int4 NOT NULL,
"IDFI" int4 NOT NULL,
"OD" date,
"DO" date,
"IDST" int4 NOT NULL,
CONSTRAINT "CV_FIRMY_pkey" PRIMARY KEY ("IDCVFI"),
CONSTRAINT "CV_FIRMY_IDCV_fkey" FOREIGN KEY ("IDCV")
REFERENCES "CV" ("IDCV") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
CREATE TABLE "CV_JEZYKI"
(
"IDCVJE" int4 NOT NULL DEFAULT
nextval('"CV_JEZYKI_IDCVJE_seq"'::regclass),
"IDCV" int4 NOT NULL,
"IDJ" int4 NOT NULL,
"ILE" int2 DEFAULT 0,
"OSTATNIO" int2 DEFAULT 0,
"IDO" int4 NOT NULL,
CONSTRAINT "CV_JEZYKI_pkey" PRIMARY KEY ("IDCVJE"),
CONSTRAINT "CV_JEZYKI_IDCV_fkey" FOREIGN KEY ("IDCV")
REFERENCES "CV" ("IDCV") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
CREATE TABLE "CV_SZKOLY"
(
"IDCVSZ" int4 NOT NULL DEFAULT
nextval('"CV_SZKOLY_IDCVSZ_seq"'::regclass),
"IDCV" int4 NOT NULL,
"IDSK" int4 NOT NULL,
"OD" date,
"DO" date,
"IDWY" int4 NOT NULL,
CONSTRAINT "CV_SZKOLY_pkey" PRIMARY KEY ("IDCVSZ"),
CONSTRAINT "CV_SZKOLY_IDCV_fkey" FOREIGN KEY ("IDCV")
REFERENCES "CV" ("IDCV") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
########### WIDOKI i TESTY ##########
#####WERSJA 1
CREATE OR REPLACE VIEW "V_CV" AS
SELECT "CV"."IDCV", "CV"."NAZWA_CV", "OSOBY"."IMIE", "OSOBY"."NAZWISKO",
"_BRANZE"."NAZWA_B", "_HIERARCHIE"."NAZWA_H", "_MIASTA"."NAZWA_M",
"_STATUSY"."NAZWA_S", "OSOBY"."PESEL", "CV"."WYNAGRODZENIE",
"GetTAKNIE"("CV"."POSIADA_SAMOCHOD") AS "GetTAKNIE", ("CV"."WAZNE_DO" >
now())::integer AS "WAZNE", "_UMIEJETNOSCI"."NAZWA_U", "_JEZYKI"."NAZWA_J",
"_SZKOLY"."NAZWA_SK", "_FIRMY"."NAZWA_FI", "_STANOWISKA"."NAZWA_ST",
"CV"."IDOS"
FROM "CV"
LEFT JOIN "OSOBY" ON "OSOBY"."IDOS" = "CV"."IDOS"
LEFT JOIN "_MIASTA" ON "_MIASTA"."IDM" = "OSOBY"."MIASTO"
LEFT JOIN "_BRANZE" ON "_BRANZE"."IDB" = "CV"."IDB"
LEFT JOIN "_HIERARCHIE" ON "_HIERARCHIE"."IDH" = "CV"."IDH"
LEFT JOIN "_STATUSY" ON "_STATUSY"."IDS" = "CV"."IDS"
LEFT JOIN "CV_UMIEJETNOSCI" ON "CV_UMIEJETNOSCI"."IDCV" = "CV"."IDCV"
LEFT JOIN "_UMIEJETNOSCI" ON "_UMIEJETNOSCI"."IDU" =
"CV_UMIEJETNOSCI"."IDU"
LEFT JOIN "CV_JEZYKI" ON "CV_JEZYKI"."IDCV" = "CV"."IDCV"
LEFT JOIN "_JEZYKI" ON "_JEZYKI"."IDJ" = "CV_JEZYKI"."IDJ"
LEFT JOIN "CV_SZKOLY" ON "CV_SZKOLY"."IDCV" = "CV"."IDCV"
LEFT JOIN "_SZKOLY" ON "_SZKOLY"."IDSK" = "CV_SZKOLY"."IDSK"
LEFT JOIN "CV_FIRMY" ON "CV_FIRMY"."IDCV" = "CV"."IDCV"
LEFT JOIN "_FIRMY" ON "_FIRMY"."IDFI" = "CV_FIRMY"."IDFI"
LEFT JOIN "_STANOWISKA" ON "_STANOWISKA"."IDST" = "CV_FIRMY"."IDST";
####i ZAPYTANIE 1 - calkowty dramat - widok robi sie gigantycznie wielki
;-)
SELECT * from \"V_CV\"
WHERE
\"NAZWA_B\" IN ('Budownictwo','Sektor publiczny') AND
\"NAZWA_H\" IN ('Kierownik','Specjalista')AND
\"NAZWA_U\" IN ('Prawo jazdy kat B')AND
\"NAZWA_J\" IN ('Angielski','Niemiecki') ";
####WERSJA 2
CREATE OR REPLACE VIEW "V_CV2" AS
SELECT "CV"."IDCV", "CV"."NAZWA_CV", "OSOBY"."IMIE", "OSOBY"."NAZWISKO",
"_BRANZE"."NAZWA_B", "_HIERARCHIE"."NAZWA_H", "_MIASTA"."NAZWA_M",
"_STATUSY"."NAZWA_S", "OSOBY"."PESEL", "CV"."WYNAGRODZENIE",
"GetTAKNIE"("CV"."POSIADA_SAMOCHOD") AS "GetTAKNIE", ("CV"."WAZNE_DO" >
now())::integer AS "WAZNE", "CV"."IDOS"
FROM "CV"
LEFT JOIN "OSOBY" ON "OSOBY"."IDOS" = "CV"."IDOS"
LEFT JOIN "_MIASTA" ON "_MIASTA"."IDM" = "OSOBY"."MIASTO"
LEFT JOIN "_BRANZE" ON "_BRANZE"."IDB" = "CV"."IDB"
LEFT JOIN "_HIERARCHIE" ON "_HIERARCHIE"."IDH" = "CV"."IDH"
LEFT JOIN "_STATUSY" ON "_STATUSY"."IDS" = "CV"."IDS";
###TO CHODZI DOBRZE
SELECT * from \"V_CV2\"
WHERE
\"NAZWA_B\" IN ('Budownictwo','Sektor publiczny') AND
\"NAZWA_H\" IN ('Kierownik','Specjalista');
###ALE GDY W GRE WCHODZI SZUKANIE PO TABELACH POMOCNICZYCH to DRAMAT :
###Zrobilem jeszcze funkcje ktora zwraca bool i sprawdza czy dane CV posiada
jeden z podanych języków
###ograniczyłem do MAX 4 parametrów oraz już po ID (języka, czy
umiejętności)
SELECT * from \"V_CV2\"
WHERE
\"NAZWA_B\" IN ('Budownictwo','Sektor publiczny') AND
\"NAZWA_H\" IN ('Kierownik','Specjalista') AND "CheckCVUM"("IDCV",
2,3,0,0");
####A FUNKCJA TUTAJ
CREATE OR REPLACE FUNCTION "CheckCVUM"(v0 int8, v1 int4, v2 int4, v3 int4,
v4 int4)
RETURNS bool AS
$BODY$
DECLARE
i integer;
BEGIN
i = "IDCV" from "CV_UMIEJETNOSCI" where "IDCV"=v0 and ( ("IDU" = v1) or
("IDU" = v2) or("IDU" = v3) or("IDU" = v4) ) LIMIT 1 ;
if (i is null) then
return false;
end if;
return true;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Indexy oczywiście są jednak ich dodawanie lub zmienianie nie pomaga.
Proszę o naprowadzenie w którym kierunku optymizować aby otrzymać w miarę
sensowe rezultaty.
Może użyć innego języka w funkcji sprawdzającej ?
Dzięki za informacje.
Paweł Matejski - 24-12-2006 00:37
Robi wrote:
> Witam,
>
> SQL: PostgreSQL 8.1.4
>
>
> Indexy oczywiście są jednak ich dodawanie lub zmienianie nie pomaga.
> Proszę o naprowadzenie w którym kierunku optymizować aby otrzymać w miarę
> sensowe rezultaty.
Zacząć używać explain analyze (a tam gdzie nie da się doczekać wyniku, to samo
explain).
--
P.M.
Antoni Jakubiak - 24-12-2006 00:37
> Jest Tabela CV - przechowująca pewne dane z CV osoby (nazwę je brzydko
> jednowierszone)
> oraz kilka tabeli pomocniczych przechowujace dane wielowierszone np - znane
> przez osobę języki, umiejętności, ukończone szkoły etc etc etc (Niżej
> załączam definicje tabel)
Spropoj nie joinowac z tabelkami wielowieszone :) .
Czyli realcje jeden do wielu i wiele do wielu obsluzyc w osobnym zapytaniu.
Funkcje pgsql oznacz jako "stable".
Antoni Jakubiak
http://www.jaukubiak.eu/
Michał Zaborowski - 24-12-2006 00:37
Witam,
Mam jedno pytanie - po co jest ten widok?
Warto podać wyniki explain analyze - bez tego ewentualna pomoc
ogranicza się do gdybania.
Zamiast robić "in ('Ala', 'ma', 'kota')" proponuję zrobić "= any
('{1,2,3}')". Czyli zamiast filtrować po labelach słownika lepiej
jest po identyfikatorach.
--
Pozdrawiam,
Michał Zaborowski (TeXXaS)
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
=?ISO-8859-2?Q?Narz=EAdzie_do_budowania_zapyta=F1_SQL=2C?==?I SO-8859-2?Q?_PL/PgSQL=2C_PL/SQL=2C_T-SQL?=
[pgsql] Akcja w =?iso-8859-2?b?emFsZb9ub7ZjaQ==?= od liczby zmienionych =?iso-8859-1?q?rekord=F3w?=
[sql][pgsql] zapytanie sql
[pgsql] Wykonanie triggera po =?ISO-8859-2?Q?zako=F1czeniu_tra?==?ISO-8859-2?Q?nsakcji?=
[pgsql] =?ISO-8859-2?Q?Prawid=B3owe_post=EApowanie_w_przyp?==?ISO-8859-2?Q?adku_awarii?=
[PGSQL] Funkcje =?ISO-8859-2?Q?zwracaj=B1ce_=27rowset=27_i_?==?ISO-8859-2?Q?tabele_tymczasowe?=
[pgsql] 7.4 =?ISO-8859-2?Q?og=B3upia=B3_przy_zak=B3adaniu_?==?ISO-8859-2?Q?indeksu?=
[pgsql] Zmiana schematu do =?ISO-8859-2?Q?kt=F3rego_nalezy_o?==?ISO-8859-2?Q?biekt=2E?=
Centralny serwer =?ISO-8859-2?Q?log=F3w_MS_Windows_na_Po?==?ISO-8859-2?Q?stgreSQL?=
[MSSQL 2k] - jak =?ISO-8859-2?Q?pod=B3=B1czy=E6_serwer_na_?==?ISO-8859-2?Q?porcie_innym_ni=BF_1433=3F?=
zanotowane.pldoc.pisz.plpdf.pisz.pllubiatowo.xlx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
