Temat: [postgresql] Problem z indeksami

Hejo,

probuje rozkminic indeksy, mam tabele z ogloszeniami, chce cachowac ilosc ogloszen dla roznych warunkow
np:

select count(a_id) from ads where a_category=1 and a_state is null and a_type is null and a_block=false and a_spam=false and a_active=true and a_delete=false and a_end=false

co bym nie zrobil nie uzywa indeksow, macie moze jakis podrecznik/ciekawe linki jak to ugryzc ? chcialbym to porzadnie zrozumiec :/

Temat: [postgresql] Problem z indeksami

Pytanie pomocnicze 1 - jakie indeksy masz na tej tabeli?

Pytanie pomocnicze 2 - Czy sa wystarczajaco selektywne?

Pytanie pomocnicze 3 - jak duza jest tabela?

Pytanie pomocnicze 4 - Kiedy byly odswiezane statystyki?

Mozliwe, ze optimizer uznaje ze nie oplaca sie uzywac indeksow i stad masz taki rezultat a nie inny.

Jesli mozliwe dorzuc wynik planu wykonania zapytania, wtedy bedzie mozna cos wiecej powiedziec. Inaczej to tu mozemy gdybac do jutra.

Temat: [postgresql] Problem z indeksami

1. troche sie bawilem, i zaczelo uzywac w zapytaniu:

select count(a_id) from ads where a_category=1 and a_state is null and a_type is null and a_block=false and a_spam=false and a_active=true and a_delete=false and a_end=false

indeksu three

Aggregate  (cost=8.28..8.29 rows=1 width=4) (actual time=0.045..0.046 rows=1 loops=1)

  ->  Index Scan using three_pkey on ads  (cost=0.00..8.27 rows=1 width=4) (actual time=0.035..0.035 rows=0 loops=1)

        Index Cond: ((a_state IS NULL) AND (a_type IS NULL) AND (a_category = 1))

        Filter: ((NOT a_block) AND (NOT a_spam) AND a_active AND (NOT a_delete) AND (NOT a_end))

Bawilem sie i utworzylem kilka indeksow, w zaleznosci po czym szukam. [eksperymentuje, wiec nie twierdze ze dziala tak jak chce]

ads_pkey	CREATE UNIQUE INDEX ads_pkey ON ads USING btree (a_id)	Klucz Główny

five_pkey	CREATE INDEX five_pkey ON ads USING btree (a_block, a_spam, a_active, a_end, a_delete)

four__pkey	CREATE INDEX four__pkey ON ads USING btree (a_block, a_spam, a_active, a_delete)

four_pkey	CREATE INDEX four_pkey ON ads USING btree (a_block, a_spam, a_end, a_active)

three_pkey	CREATE INDEX three_pkey ON ads USING btree (a_state, a_type, a_category)

2. Glownymi parametrami szukania są: a_block=false and a_spam=false and a_active=true and a_delete=false and a_active_time>now() , gdzie a_active_time, moge zamienic na a_end=false/

Przy zliczaniu ilosci ogloszen, dochodzi szukanie po (kategorii, wojewodztwie, typie kategorii):
a_category=1 and a_state is null and a_type is null

3. Ilosc rekordow, czy ilosc kolumn ? Rekordow 20k, a kolumn 30
4. Gdzie mozna sprawdzic kiedy byly odswiezane ?

Mam zapytanie wyswietlajace ostatnie 10 ogloszen dodanych:

select * from ads left join ads_photo on a_p_id=p_id where a_block=false and a_spam=false and a_active=true and a_delete=false and a_active_time>now() order by a_active_time desc,a_week desc limit 10

Limit  (cost=6883.85..6883.87 rows=10 width=1684) (actual time=66.484..66.513 rows=10 loops=1)

  ->  Sort  (cost=6883.85..6885.21 rows=544 width=1684) (actual time=66.480..66.490 rows=10 loops=1)

        Sort Key: ads.a_active_time, ads.a_week

        Sort Method:  top-N heapsort  Memory: 32kB

        ->  Nested Loop Left Join  (cost=0.00..6872.09 rows=544 width=1684) (actual time=0.062..59.163 rows=1478 loops=1)

              ->  Seq Scan on ads  (cost=0.00..3994.88 rows=544 width=1562) (actual time=0.023..39.919 rows=1478 loops=1)

                    Filter: ((NOT a_block) AND (NOT a_spam) AND a_active AND (NOT a_delete) AND (a_active_time > now()))

              ->  Index Scan using ads_photo_pkey on ads_photo  (cost=0.00..5.28 rows=1 width=122) (actual time=0.006..0.007 rows=1 loops=1478)

                    Index Cond: (ads.a_p_id = ads_photo.p_id)

Marcin Frątczak edytował(a) ten post dnia 30.12.10 o godzinie 09:44

Temat: [postgresql] Problem z indeksami

Indeksy typu btree na kolumnach z wartosciami typu true/false nie sa zbyt wydajne, poniewaz nie sa wystarczajaco selektywne.

dlatego w twoim zapytaniu indeks three_pk jest uzywany jako jedyny ktory potencjalnie opiera sie na bardziej selektywnych wartosciach niz 0/1.

Na polach typu bit czy bool zaloz indexy bitmapowe (http://en.wikipedia.org/wiki/Bitmap_index), wtedy powinny zostac wlaczone do optymalizacji zapytania.

20k rekordow to tyle co nic ale dobrze ze myslisz o wydajnosci na przyszlosc.

Wiecej o statystykach w postgresql mozesz poczytac tutaj

http://www.postgresql.org/docs/8.3/interactive/monitor...

Temat: [postgresql] Problem z indeksami

W tym wypadku powinno opłacać się użyć indeksu "cząstkowego", np:
create index idx_ads_costam on ads (a_active_time) where a_spam=false and a_active=true and a_delete=false;

BTW użycie nazw five_pkey, four_pkey itp sugeruje, że są to ineksy od kluczy głównych - może to być mylace w przyszłości.

Temat: [postgresql] Problem z indeksami

Pierwsza sprawa... Kolejność kolumn wymienionych w indeksie ma znaczenie. Powiem więcej - ma podstawowe znaczenie. Ogólna zasada jest taka, że zaczyna się od kolumn, których wartości mają największy rozrzut.
Druga sprawa. Do optymalizacji liczników najlepiej sprawdza się kolumna z zestawem triggerów. Podejrzewam, że zestaw warunków jest dość standardowy i 2,3 kolumny w tabelce z kategoriami rozwiążą problem. Można dodać tabelkę w relacji 1:1, żeby zmieniać, tego co już działa. W ten sposób można "obskoczyć" to co jest najczęściej używane. Jak to jest zewnętrzny soft można "instead of" wrzucić dla zapytań, które wpadają do bazy. No, ale z tym to trzeba uważać.

Temat: [postgresql] Problem z indeksami

Grzegorz L.:

Na polach typu bit czy bool zaloz indexy bitmapowe (http://en.wikipedia.org/wiki/Bitmap_index), wtedy powinny zostac wlaczone do optymalizacji zapytania.

20k rekordow to tyle co nic ale dobrze ze myslisz o wydajnosci na przyszlosc.

Nie widze indeksow bitmapowych w postgresie [probowalem znalezc w google informacje o nich, ale znalazlem ze nie ma ich. Chyba ze zle szukam :) ], utworzylem indeksy hash:

seven1_pkey	CREATE INDEX seven1_pkey ON ads USING hash (a_active)

seven2_pkey	CREATE INDEX seven2_pkey ON ads USING hash (a_delete)

seven3_pkey	CREATE INDEX seven3_pkey ON ads USING hash (a_spam)

seven4_pkey	CREATE INDEX seven4_pkey ON ads USING hash (a_block)

Przy zapytaniu:

select * from ads where
a_active= false - uzywa indeksu, a przy select * from ads where
a_active= true nie, czy indeksy wykorzystywane w zaleznosci od wartosci true / false ? Oraz czy trzeba poczekac jakis czas az indeksy zostaną stworzone ?

Faktycznie nazwy indeksow mam pomotane, z pkey na koncu. To jest tylko do testow, potem wszystko zostanie poprawione :)

Utworzylem indeks czastkowy jak Daniel polecil:

CREATE INDEX idx_ads_active_time ON ads USING btree (a_active_time) WHERE a_active = true AND a_delete = false AND a_spam = false AND a_block = false

niestety explain mowi ze nie uzywa on tego indeksu, przy zapytaniu:

explain analyze

select * from ads where 

a_active = true AND a_delete = false AND a_spam = false AND a_block = false



and a_active_time>now() order by a_active_time desc limit 10;



Limit  (cost=4007.36..4007.38 rows=10 width=1562) (actual time=479.355..479.385 rows=10 loops=1)

  ->  Sort  (cost=4007.36..4008.79 rows=572 width=1562) (actual time=479.349..479.358 rows=10 loops=1)

        Sort Key: a_active_time

        Sort Method:  top-N heapsort  Memory: 33kB

        ->  Seq Scan on ads  (cost=0.00..3994.99 rows=572 width=1562) (actual time=0.024..476.098 rows=1481 loops=1)

              Filter: (a_active AND (NOT a_delete) AND (NOT a_spam) AND (NOT a_block) AND (a_active_time > now()))

Temat: [postgresql] Problem z indeksami

Michał Z.:
Pierwsza sprawa... Kolejność kolumn wymienionych w indeksie ma znaczenie. Powiem więcej - ma podstawowe znaczenie. Ogólna zasada jest taka, że zaczyna się od kolumn, których wartości mają największy rozrzut.
Druga sprawa. Do optymalizacji liczników najlepiej sprawdza się kolumna z zestawem triggerów. Podejrzewam, że zestaw warunków jest dość standardowy i 2,3 kolumny w tabelce z kategoriami rozwiążą problem. Można dodać tabelkę w relacji 1:1, żeby zmieniać, tego co już działa. W ten sposób można "obskoczyć" to co jest najczęściej używane. Jak to jest zewnętrzny soft można "instead of" wrzucić dla zapytań, które wpadają do bazy. No, ale z tym to trzeba uważać.

Kolejnosc ktorych wartosci maja najwiekszy rozrzut czyli nie np wartosci boolean, tylko np kategorie typu integer gdzie kolumna moze miec wiele wartosci, tak ?

Ja wszystkie mozliwe kombinacje ile ogloszen jest wg danych warunkow zapisuje w innej tabeli. Przechowywana jest tam ilosc ogloszen wg kryteriow: state, type, category
Jak to ugryzc trigerem ? Aktualnie tabele z cachem licznika ma 18k rekordow. (Tyle kombinacji wyszlo)

Grzegorz.:
20k rekordow to tyle co nic ale dobrze ze myslisz o wydajnosci na przyszlosc.

Mysle o optymalizacji, bo juz dla tych 20k rekordow, jakos wolno to dziala :) a chce zrozumiec indeksy i optymalizacje bazy :)Marcin Frątczak edytował(a) ten post dnia 30.12.10 o godzinie 13:26

Temat: [postgresql] Problem z indeksami

No tak, moja wina, z rozpedu polecilem indexy bitmapowe, bo tu by sie idealnie sprawdzily :)

A te zdaje sie sa tylko w Oracle i Sybase

Mea culpa

Temat: [postgresql] Problem z indeksami

Marcin Frątczak:

Michał Z.:
Pierwsza sprawa... Kolejność kolumn wymienionych w indeksie ma znaczenie. Powiem więcej - ma podstawowe znaczenie. Ogólna zasada jest taka, że zaczyna się od kolumn, których wartości mają największy rozrzut.
Druga sprawa. Do optymalizacji liczników najlepiej sprawdza się kolumna z zestawem triggerów. Podejrzewam, że zestaw warunków jest dość standardowy i 2,3 kolumny w tabelce z kategoriami rozwiążą problem. Można dodać tabelkę w relacji 1:1, żeby zmieniać, tego co już działa. W ten sposób można "obskoczyć" to co jest najczęściej używane. Jak to jest zewnętrzny soft można "instead of" wrzucić dla zapytań, które wpadają do bazy. No, ale z tym to trzeba uważać.

Kolejnosc ktorych wartosci maja najwiekszy rozrzut czyli nie np wartosci boolean, tylko np kategorie typu integer gdzie kolumna moze miec wiele wartosci, tak?

Celem jest to, żeby zamiast bobrować po tabelce wyciągnąć potrzebne
dane - po to powstały indeksy. Problem jest taki, że wyciągnięcie
30% tabelki przez indeks jest o wiele droższe niż proste jej
przejrzenie seqscanem. PG ma tak, że jak uzna, że wyciągnie mniej
jak 2% danych to używa indeksu - jak nie - jedzie seqscanem. Dlatego
właśnie stawianie indeksu na kolumnie z True/False nie bardzo ma
sens. W ogólności... Jak indeks ma wiele kolumn to PG indeksuje je
jedna po drugiej. Czyli najpierw buduje indeks dla pierwszej kolumny,
potem dla danych, które odpowiadają poszczególnym wartościom z
pierwszej budowane są kolejne indeksy... Na przykładzie - mam klucz
obcy do kategorii i parę kolumn true/false - dla ustalenia uwagi:
active, block, stick. Jak dam boole najpierw zbuduje kolosalne
indeksy na każdym poziomie... i na koniec zrobi indeks dla
kategorii. Z grubsza podzieli tabelę na dwie części. Każdą wartość
z obu części musi pamiętać, bo przecież o to chodzi. Dalej dla
każdej części zrobi indeks dla kolejnego boola... Masakra. :) Jak
najpierw dam kategorię - pierwszy indeks potnie dane na drobne
kawałki, które potem będzie można w prosty sposób dodatkowo
poindeksować tymi boolami. Po co w ogóle robić indeks na boolach?
No bo skoro i tak jedzie po indeksie to szybciej mu będzie zrobić
ten krok, niż przeglądać dane sekwencyjnie. Tak, w ogólności :)
Inne rozwiązanie to indeks częściowy. Czyli indeksujemy tylko część
danych, spełniających warunek - np. active=true and blocked=false.
W ten sposób indeks będzie mniejszy, co jest plusem, poindeksowane
dane z założenia będą spełniać w/w warunek. Mam nadzieję, że nie
zanudziłem ;)

Ja wszystkie mozliwe kombinacje ile ogloszen jest wg danych warunkow zapisuje w innej tabeli. Przechowywana jest tam ilosc ogloszen wg kryteriow: state, type, category
Jak to ugryzc trigerem ? Aktualnie tabele z cachem licznika ma 18k rekordow. (Tyle kombinacji wyszlo)

Memcache to jest rozwiązanie. No, ale po restarcie system trochę
muli :) Triggerów robi się 3 - insert, update, delete. Są pola old
i new... Jest tylko jeden myk. Ważne, żeby w update zmieniać dane
w ściśle określonej kolejności. Najpierw zmieniany ma być rekord o
mniejszym id. Inaczej może być dead-lock i to taki parszywy, ciężki
do wytropienia. Ja bym zrobił osobną tabelę na te dane. Wrzuciłbym
te wszystkie rzeczy po których ma być wyliczany count i niech
jedzie. Triggery byłyby na ogłoszeniach. No, chyba że coś jeszcze
się zmienia. Nie wiem tylko, czy takie pełne liczniki są niezbędne.
Z doświadczenia wiem, że jest spora szansa, że ktoś tak sobie
powiedział tylko. W rzeczywistości liczba aktywnych, nie
skasowanych wiadomości w kategorii ma znaczenie. Pewnie jest coś,
czego nie wiem... chodzi mi o to, żeby się dobrze temu przyjrzeć,
bo koszty są spore, a znaczenie biznesowe - raczej niewielkie.

Jest jeszcze jedna rzecz. Jak baza liczy county to wtedy plus jest
taki, że jest jedno miejsce w systemie, gdzie są poprawne dane.
Minus jest taki, że to zwykle najbardziej obciążone miejsce i
wyciąganie jest kosztowne. Można aktualizować co jakiś czas. Typy,
kategorie raczej się nie zmieniają za często. Mogą siedzieć w
memcache. Aplikacja i tak wie kiedy dodaje, kasuje kategorię, więc
można sobie ze zmianami poradzić. Do tego coś co raz na 5 minut
aktualizuje ostatnio zmienione kategorie. Te triggery mogą wpisać
czas ostatniej zmiany - potem wystarczy wyciągnąć to co się zmieniło
od ostatniej aktualizacji. Można też zrobić wyjątek dla małych
kategorii - dla nich wyciągać co i raz. Zależy od założeń...

No to chyba tyle.

Temat: [postgresql] Problem z indeksami

Utworzylem indeks czastkowy jak Daniel polecil:

CREATE INDEX idx_ads_active_time ON ads USING btree (a_active_time) WHERE a_active = true AND a_delete = false AND a_spam = false AND a_block = false

niestety explain mowi ze nie uzywa on tego indeksu, przy zapytaniu:

Po dodaniu indeksu warto puścić analyze na tabeli, do której indeks dokładałeś, co prawda po pewnym czasie autovacuum zrobi to sam, ale jeśli badasz na bieżąco wpływ indeksów na zapytania, to tak będzie szybciej. Dla większych tabel domyślny statisitics target (100) jest zbyt mały - warto go zwiększyć.

http://www.postgresql.org/docs/8.4/interactive/planner...

Temat: [postgresql] Problem z indeksami

Michał Z.:
Memcache to jest rozwiązanie. No, ale po restarcie system trochę

Dlatego do pewnych zastosowań warto zamienić memcache na redisa.

Temat: [postgresql] Problem z indeksami

O indeksach to może Ci się też przydać: http://www.depesz.com/index.php/2010/09/09/why-is-my-i...

Temat: [postgresql] Problem z indeksami

A właśnie - bardziej ogólna lektura w odcinach: http://use-the-index-luke.com/

Temat: [postgresql] Problem z indeksami

wielkie dzieki,

Michal, dzieki tobie wiecej zrozumialem , cos zaczyna powoli wychodzic :) jeszcze napewno tu napisze, testuje to wszystko, zobaczymy co wyjdzie :)

dzieki za linki, napewno przejrze i poczytamMarcin Frątczak edytował(a) ten post dnia 30.12.10 o godzinie 22:27

Temat: [postgresql] Problem z indeksami

Daniel Podlejski:

Michał Z.:
Memcache to jest rozwiązanie. No, ale po restarcie system trochę

Dlatego do pewnych zastosowań warto zamienić memcache na redisa.

Wszystko można - np. całość postawić na redisie, couchdb, mongodb itp. - do tego Compass, Lucene, Sphinx i też da radę.
Zrobienie na triggerach daje największą szansę na spójność danych - z dokładnością
do błędu w kodzie. ;) Buforowanie z zapisem danych obok - brzmi fajnie, ale... Są dwie wersje danych, które wcale nie muszą być tożsame. Jeżeli synchronizację będzie robił DBA - no, ok. On odpowiada za spójność danych - wiadomo co komu urwać jak się kiełbasi. Jeżeli jednak będzie tak, że ktoś czasem coś sobie doklepie, jak będzie wolno to wrzuci do bufora... Te same dane pincet razy, część odświeżona, część nie... Nawet jak cały system będzie robiła jedna osoba - wcale nie oznacza, że zapanuje nad tym wszystkim i nie zrobi się śmietnik w buforze.
Wybór w zasadzie powinien wynikać ze względów biznesowych. To, na co ja osobiście bym nalegał to precyzyjne i sensowne określenie kompetencji poszczególnych "warstw"/komponentów. Projekt jakiś, testy, continous integration - zawsze testy wyłapią jak coś się rozjedzie. Jak się rozjedzie mimo testów - można dorobić testy, żeby wyłapać błąd w przyszłości. Inaczej - kołdra robi się krótka :-)