[postgreSQL]Wyszukiwanie po wielu keywordach..
mechcinski@tlen.pl - 10-09-2006 00:15
[postgreSQL]Wyszukiwanie po wielu keywordach..
Witam, po kilku latach powracam do zagadnien zwiazanych z bazami..idzie dosc dobrze.. ale sa i krytyczne momenty...;) Jestem na etapie planowania zapytania i nie chce zeby moje bledy odbily sie na wydajnosci... Moja tabele w uproszczeniu:
Zjawisko( id_zjawiska; priorytet; //wartosc 1-5;
Paweł Matejski - 10-09-2006 00:15
mechcinski@tlen.pl wrote: > Witam, po kilku latach powracam do zagadnien zwiazanych z bazami..idzie > dosc dobrze.. ale sa i krytyczne momenty...;) > Jestem na etapie planowania zapytania i nie chce zeby moje bledy odbily > sie na wydajnosci... > Moja tabele w uproszczeniu: > > Zjawisko( > id_zjawiska; > priorytet; //wartosc 1-5; > . > . > . > } > 380tys rekordow > > > ZajwiskoSlowo( > id_zjawiska; > id_slowa; > ) > 15mln rekordow > > > Slowo( > id_slowa; > slowo; > ) > 350tys rekordow > > Na tabeli ZjawiskoSlowo jest zalozony dwukolumnowy index btree. > > Opisuje tu zjawiska za pomoca slow kluczowych. > Chcac wyszukac zjawisko opisane slowem kluczowym "wysoki" - robie to > przez zapytanie : > > SELECT * FROM Zjawisko z WHERE z.id_zjawiska IN( > SELECT id_zjawiska FROM ZjawiskoSlowo zs WHERE zs.id_slowo =( > SELECT id FROM Slwo s WHERE s.slowo = 'wysoki')) limit 50; > > 1 Pytanie- Mam wrazenie ze to jedyny sposob na zaleznosci > wiele-do-wiele(podzapytania)..
A skąd Ci to przyszło do głowy? Robi sie to normalnie. :)
SELECT z.* FROM Zjawisko z, ZjawiskoSlowo zs, Slowo s WHERE z.id = zs.id_zjawisko AND zs.id_slowa = s.id AND s.slowo = 'wysoki';
Potrzebne indexy to najprawdopodobniej: Slowa (slowo), ZjawiskoSlowo (id_slowa), Zjawisko (id).
Jeśli masz index wielokolumnowy, to wymienione kolumny MUSZĄ być pierwsze.
> 2 Pytanie- Jak ugryzc zapytanie po - kilku slowach - tak zeby wynikami > byly zjawiska opisane kazdym z tych slow? > Szukac po jednym slowie, pozniej w wynikach po nastepnym, itd ? - Mam > wrazenie ze inaczej....
I słusznie: SELECT z.* FROM Zjawisko z, ZjawiskoSlowo zs, (SELECT id FROM Slowo s WHERE s.slowo IN ('wysoki','jasny','czerwony) GROUP BY id,slowo HAVING count(*) = 3) ss WHERE z.id = zs.id_zjawisko AND zs.id_slowa = ss.id
3 - bo tyle słów wpisałem w przykładzie i trzeba zmienić przy innej liczbie.
> 3 Pytanie - Musze wyniki posortowac po "priorytecie" - odpytujac baze > przez: > > SELECT * FROM Zjawisko z WHERE z.id_zjawiska IN( > SELECT id_zjawiska FROM ZjawiskoSlowo zs WHERE zs.id_slowo =( > SELECT id FROM Slwo s WHERE s.slowo = 'wysoki')) order by z.priorytet > limit 50; > mam czasy odpowiedzi rzedu 1 minuty -tu jakby rozumiem dlaczego - > silnik musi posortowac te 45tys wynikow- jak w takim ukladzie to > przyspieszyc. Jakies indexy ?
Możesz spróbować index (priorytet,id) ale nie wiem, czy pomoże. Spróbuj moją wersję, ma szansę być szybsza.
> Bede szczerze wdzieczny za wszelekie sugestie...
Jak masz problemy z wydajnością to sprawdzaj zapytania przy pomocy: EXPLAIN ANALYZE SELECT ......
A jak pytasz na grupie o taki problemy, to załącz wynik powyższej instrukcji.
-- P.M.
mechcinski@tlen.pl - 10-09-2006 00:16
Paweł Matejski napisał(a): > A skąd Ci to przyszło do głowy? Robi sie to normalnie. :) > > SELECT z.* > FROM Zjawisko z, ZjawiskoSlowo zs, Slowo s > WHERE z.id = zs.id_zjawisko > AND zs.id_slowa = s.id > AND s.slowo = 'wysoki'; > > Potrzebne indexy to najprawdopodobniej: Slowa (slowo), ZjawiskoSlowo (id_slowa), > Zjawisko (id). > > Jeśli masz index wielokolumnowy, to wymienione kolumny MUSZĄ być pierwsze. Dzila szybciej :)
> SELECT z.* > FROM Zjawisko z, ZjawiskoSlowo zs, > (SELECT id FROM Slowo s > WHERE s.slowo IN ('wysoki','jasny','czerwony) > GROUP BY id,slowo > HAVING count(*) = 3) ss > WHERE z.id = zs.id_zjawisko > AND zs.id_slowa = ss.id To zapytanie - nie zwraca zadnych wynikow...a to podzapytanie nie powinno byc na kolumnie ZjawiskoSlowo?
> Możesz spróbować index (priorytet,id) ale nie wiem, czy pomoże. Spróbuj moją > wersję, ma szansę być szybsza. Z indexem jest szybciej o sredniio 10 sek...nadal jest to kolo 30-40....duzo.Tak na prawde rekordy bedzie dodawal tylko "admin" i bedzie robil to niekoniecznie czesto- wiec mysle ze po dodawaniu wierszy mozna by bylo przeindeksowac baze ? Moze postgres wspiera tego typu dzialania ? A moze jednaka da sie to znacznie przyspieszyc jakas bardziej elegancja metoda ?
Paweł Matejski - 10-09-2006 00:16
mechcinski@tlen.pl wrote: > Paweł Matejski napisał(a): > >> SELECT z.* >> FROM Zjawisko z, ZjawiskoSlowo zs, >> (SELECT id FROM Slowo s >> WHERE s.slowo IN ('wysoki','jasny','czerwony) >> GROUP BY id,slowo >> HAVING count(*) = 3) ss >> WHERE z.id = zs.id_zjawisko >> AND zs.id_slowa = ss.id > To zapytanie - nie zwraca zadnych wynikow...a to podzapytanie nie > powinno byc na kolumnie ZjawiskoSlowo?
A racja. Znaczy się podzapytanie ma być na złączeniu ZjawiskoSlowo i Slowo. Grupowanie po id_zjawiska. Mam nadzieję, że sobie poradzisz. :)
>> Możesz spróbować index (priorytet,id) ale nie wiem, czy pomoże. Spróbuj moją >> wersję, ma szansę być szybsza. > Z indexem jest szybciej o sredniio 10 sek...nadal jest to kolo > 30-40....duzo.Tak na prawde rekordy bedzie dodawal tylko "admin" i > bedzie robil to niekoniecznie czesto- wiec mysle ze po dodawaniu > wierszy mozna by bylo przeindeksowac baze ? Moze postgres wspiera tego > typu dzialania ? A moze jednaka da sie to znacznie przyspieszyc jakas > bardziej elegancja metoda ?
A czytałeś co ja tam napisałem na końcu posta, a Ty nie zacytowałeś?
-- P.M.
mechcinski@tlen.pl - 10-09-2006 00:16
W rzeczywistosci w bazie tabele wygladaja w nastepujacy sposob(chcialem uproscic - a zamieszalem...)
foto( id . . priorytet //warosc 1-5.. )
fotokey( id_foto id_key )
keywords( id keyword // text )
indexy: na tabeli foto: (priorytet,id), (id) na tabeli fotokey : ( id_key, id_foto) na tabeli keywords (keyword)
> A racja. Znaczy się podzapytanie ma być na złączeniu ZjawiskoSlowo i Slowo. > Grupowanie po id_zjawiska. Mam nadzieję, że sobie poradzisz. :) Chyba mi sie udalo:
SELECT f.* FROM foto f, (SELECT id_foto FROM fotokey WHERE id_key in(9,1069) GROUP BY id_foto HAVING COUNT(id_key) = 2) ss WHERE f.id = ss.id_foto order by f.priorytet limit 50;
Pomijam tutaj wyciaganie id keyworda - ma to znikomy wplyw na czas wyszukiwania - zagmatwaloby zapytanie....Zapytanie wiec wyszukuje fotografie opisane slowami kluczowymi o id 9 i 1069 oraz sortuje po priorytecie
> >> Możesz spróbować index (priorytet,id) ale nie wiem, czy pomoże.. Spróbuj moją > >> wersję, ma szansę być szybsza. dziala duuuzo szybciej z takim indexem...:) Ale nadal jest to do kilkunastu sekund :|......
> A czytałeś co ja tam napisałem na końcu posta, a Ty nie zacytowałeś?
Rozumiem ze chodzilo Ci o QUERY PLAN ? :) tutaj w przystepniejszej formie: http://www.sourcecube.pl/grupa/
Limit (cost=22767.19..22767.20 rows=6 width=141) (actual time=14708.089..14708.247 rows=50 loops=1) -> Sort (cost=22767.19..22767.20 rows=6 width=141) (actual time=14708.085..14708.173 rows=50 loops=1) Sort Key: f.priorytet -> Nested Loop (cost=22748.80..22767.11 rows=6 width=141) (actual time=13358.661..13977.003 rows=5480 loops=1) -> HashAggregate (cost=22748.80..22748.89 rows=6 width=8) (actual time=13358.553..13411.723 rows=5480 loops=1) Filter: (count(id_key) = 2) -> Bitmap Heap Scan on fotokey (cost=51.24..22709.90 rows=7781 width=8) (actual time=1027.033..12490.225 rows=78061 loops=1) Recheck Cond: ((id_key = 100) OR (id_key = 466)) -> BitmapOr (cost=51.24..51.24 rows=7782 width=0) (actual time=964.896..964.896 rows=0 loops=1) -> Bitmap Index Scan on fotokey_index1 (cost=0.00..25.62 rows=3891 width=0) (actual time=21.814..21.814 rows=31060 loops=1) Index Cond: (id_key = 100) -> Bitmap Index Scan on fotokey_index1 (cost=0.00..25.62 rows=3891 width=0) (actual time=943.069..943.069 rows=47001 loops=1) Index Cond: (id_key = 466) -> Index Scan using foto_index on foto f (cost=0.00..3.01 rows=1 width=141) (actual time=0.084..0.086 rows=1 loops=5480) Index Cond: (f.id = "outer".id_foto) Total runtime: 14710.987 ms
16 wiersz(y)
Całkowity czas pracy: 14,717.453 ms.
Czy to zapytanie da sie jakos poprawic albo sama baze ? jakis inne indexy ? Rece mi opadaja .. .
Pozdrawiam
mechcinski@tlen.pl - 10-09-2006 00:59
hubert depesz lubaczewski wrote: > podniesie wydajność tam gdzie robisz sort po priority. > jeśli chodzi o kolumny. > jeśli jest to postgres 8.1, to raczej załóż 2 oddzielne indeksy. ogólnie > - poeksperymentuj trochę - zobaczysz co lepiej zadziała i nauczysz się > sporo o tym co można i jak :) Duze dzieki za pomoc - bede kombinowal...
Mam jescze kilka pytan...
Mam wrazenie ze dochodze do granicy wydajnosciowej mojej bazy.
Czy taka baza gdzie tabela wiele-do-wiele ma kilkanascie,do kilkudziesieciu milinow rekordow, a zapyatnia sa wlasnie po kilku keywordach, dojda penie jescze jakies extra bonusy w tych zapytanich - ma szanse chodzic sprawnie na serwerze wspoldzielonym czolowych ;) polskich hostingodawcow (wiekszosc ma oczywiscie postgresa 7.4)? Piszac "sprawnie" mam na mysli czasy rzedu 1 sekundy...Czy powinienem zbierac sie do kupna maszyny ?
Jesli znacie hostingodawcow :dobrych, solidnych, z super maszynami, suppertem 24h/dobe, bez doplat za nadmiarowa przestrzen na bazy,z postgresem 8.x, za 1000-1500zl rocznie.....Chetnie sie o takich dowiem,..jak wam przychodzi do glowy wieksza liczba takich firm - to prosze zawezic ja do powiedzmy 100 ;>
A moze znacie miejsce gdzie mozna dosyc tanio wstawic swoja maszyne ?
Pozdrawiam
hubert depesz lubaczewski - 10-09-2006 00:59
On 2006-09-07, mechcinski@tlen.pl <mechcinski@tlen.pl> wrote: > "sprawnie" mam na mysli czasy rzedu 1 sekundy...Czy powinienem zbierac > sie do kupna maszyny ?
może przestań kombinować tylko postaw tsearcha?
depesz
-- http://www.depesz.com/index.php/2006...udzi-do-pracy/
mechcinski@tlen.pl - 14-09-2006 00:36
hubert depesz lubaczewski wrote: > On 2006-09-07, mechcinski@tlen.pl <mechcinski@tlen.pl> wrote: > > "sprawnie" mam na mysli czasy rzedu 1 sekundy...Czy powinienem zbierac > > sie do kupna maszyny ? > > może przestań kombinować tylko postaw tsearcha?
Wychodzi na to, ze czasami trzba przestac kombinowac i po prostu zalozyc tsearcha..;)
Ogolnie czasy sa duuzo lepsze, ale dla slow czeto sie powtarzajacych jescze nie jest najlepiej...do kilkunasu sekund - moze sprobuje poruszyc ten temat w nowym watku...
Duuuuze dzieki za pomoc!
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[PostgreSQL] - jak =?ISO-8859-2?Q?zabezpieczy=E6_interesy_tw?==?ISO-8859-2?Q?=F3rcy_systemu_=3F=3F=3F?=
postgresql - int/int
postgresql Select count(*) czy raczej Select count(ID)
[PostgreSQL] jak =?ISO-8859-2?Q?pobra=E6_warto=B6=E6_zwracan?==?ISO-8859-2?Q?=B1_przez_funkcj=EA=3F?=
[postgresql] INSERT OR UPDATE - jak =?ISO-8859-2?Q?b=EAdzie_na?==?ISO-8859-2?Q?jlepiej=3F?=
[postgresql] kilka =?ISO-8859-2?Q?rekord=F3w_subquery_jako_?==?ISO-8859-2?Q?string?=
[PostgreSQL] Jak =?ISO-8859-2?Q?po=B3=B1czy=E6_funkcje_z_w?==?ISO-8859-2?Q?idokiem?=
Postgres - replikcja master-master
Dopasowanie do "najlepszego" dopasowania :) [ PostgreSQL]
Problemy z =?ISO-8859-2?Q?instalacj=B1_PostgreSQL_na_syste?==?ISO-8859-2?Q?mach_Windows?=
zanotowane.pldoc.pisz.plpdf.pisz.planette.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 |
|