Analiza Wykorzystania Indeksu PostgreSQL
Czy istnieje narzędzie lub metoda do analizy Postgres i określenia, które brakujące indeksy powinny zostać utworzone, a które nieużywane indeksy powinny zostać usunięte? Mam małe doświadczenie w robieniu tego z narzędziem "profiler" dla SQLServer, ale nie jestem świadomy podobnego narzędzia dołączonego do Postgres.
8 answers
Lubię to znaleźć brakujące indeksy:
SELECT
relname AS TableName,
to_char(seq_scan, '999,999,999,999') AS TotalSeqScan,
to_char(idx_scan, '999,999,999,999') AS TotalIndexScan,
to_char(n_live_tup, '999,999,999,999') AS TableRows,
pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
AND 50 * seq_scan > idx_scan -- more then 2%
AND n_live_tup > 10000
AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;
Sprawdza, czy jest więcej skanowania sekwencyjnego niż skanowania indeksowego. Jeśli tabela jest mała, zostanie zignorowana, ponieważ Postgres wydaje się preferować skanowanie sekwencyjne dla nich.
Powyższe zapytanie ujawnia brakujące indeksy.
Następnym krokiem będzie wykrycie brakujących połączonych indeksów. To chyba nie jest łatwe, ale wykonalne. Może analizując powolne zapytania ... Słyszałem, że pg_stat_statements może pomóc...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-05 08:48:12
Sprawdź statystyki. pg_stat_user_tables
i pg_stat_user_indexes
to te, od których warto zacząć.
Patrz "The Statistics Collector ".
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-10 17:15:36
W sprawie metody określania brakujących indeksów....Nie. Ale są pewne plany, aby to ułatwić w przyszłym wydaniu, jak pseudo-indeksy i czytelne maszynowo wyjaśnić.
Obecnie musisz EXPLAIN ANALYZE
źle wykonywać zapytania, a następnie ręcznie określić najlepszą trasę. Niektóre analizatory logów, takie jak pgFouine, mogą pomóc w określeniu zapytań.
Jeśli chodzi o nieużywany indeks, możesz użyć czegoś podobnego do poniższego, aby pomóc je zidentyfikować:
select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';
To pomoże zidentyfikuj krotki odczytane, zeskanowane, pobrane.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-10 17:11:28
Kolejnym nowym i ciekawym narzędziem do analizy PostgreSQL jest PgHero . Jest bardziej skoncentrowany na dostrajaniu bazy danych i dokonuje licznych analiz i sugestii.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-30 12:08:19
Istnieje wiele linków do skryptów, które pomogą Ci znaleźć nieużywane indeksy na PostgreSQL wiki . Podstawową techniką jest patrzenie na pg_stat_user_indexes
i szukanie tych, gdzie idx_scan
, Liczba ile razy indeks został użyty do odpowiedzi na zapytania, wynosi zero, lub przynajmniej bardzo niska. Jeśli aplikacja się zmieniła, a wcześniej używany indeks prawdopodobnie nie jest teraz, czasami musisz uruchomić pg_stat_reset()
, aby przywrócić Wszystkie Statystyki do 0, a następnie zebrać nowe dane; możesz zapisać bieżące wartości za wszystko i Oblicz delta zamiast tego, aby to rozgryźć.
Nie ma jeszcze dobrych narzędzi do sugerowania brakujących indeksów. Jednym z podejść jest rejestrowanie zapytań, które uruchamiasz i analizowanie, które z nich zajmują dużo czasu, używając narzędzia do analizy dziennika zapytań, takiego jak pgFouine lub pqa. Zobacz " rejestrowanie trudnych zapytań " aby uzyskać więcej informacji.
Innym podejściem jest spojrzenie na pg_stat_user_tables
i poszukiwanie tabel, które mają dużą liczbę sekwencyjnych skanów przeciwko nim, gdzie seq_tup_fetch
jest duża. Gdy indeks jest używany, liczba idx_fetch_tup
jest zwiększana. To może podpowiedzieć, kiedy tabela nie jest wystarczająco dobrze indeksowana, aby odpowiedzieć na zapytania.
Zastanawiając się, na których kolumnach powinieneś się indeksować? To zwykle prowadzi z powrotem do analizy dziennika zapytań.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-10 17:09:50
Możesz użyć poniższego zapytania, aby znaleźć użycie indeksu i rozmiar indeksu:
Odniesienie pochodzi z tego bloga.
SELECT
pt.tablename AS TableName
,t.indexname AS IndexName
,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc
ON pt.tablename=pc.relname
LEFT OUTER JOIN
(
SELECT
pc.relname AS TableName
,pc2.relname AS IndexName
,psai.idx_scan
,psai.idx_tup_read
,psai.idx_tup_fetch
,psai.indexrelname
FROM pg_index AS pi
JOIN pg_class AS pc
ON pc.oid = pi.indrelid
JOIN pg_class AS pc2
ON pc2.oid = pi.indexrelid
JOIN pg_stat_all_indexes AS psai
ON pi.indexrelid = psai.indexrelid
)AS T
ON pt.tablename = T.TableName
WHERE pt.schemaname='public'
ORDER BY 1;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-11 16:42:41
PoWA wydaje się być ciekawym narzędziem dla PostgreSQL 9.4+. Gromadzi statystyki, wizualizuje je i sugeruje indeksy. Wykorzystuje on pg_stat_statements
przedłużenie.
PoWA jest analizatorem obciążenia PostgreSQL, który gromadzi statystyki wydajności i dostarcza wykresy i wykresy w czasie rzeczywistym, aby pomóc monitorować i dostrajać serwery PostgreSQL. Jest podobny do Oracle AWR lub SQL Server MDW.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-30 11:56:34
To powinno pomóc: praktyczna Analiza zapytań
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-10 17:11:41