Powolne zapytanie przy dużym stole z GROUP BY i ORDER BY

Mam tabelę z 7,2 milionami krotek, która wygląda następująco:

                               table public.methods
 column |          type         |                      attributes
--------+-----------------------+----------------------------------------------------
 id     | integer               | not null DEFAULT nextval('methodkey'::regclass)
 hash   | character varying(32) | not null
 string | character varying     | not null
 method | character varying     | not null
 file   | character varying     | not null
 type   | character varying     | not null
Indexes:
    "methods_pkey" PRIMARY KEY, btree (id)
    "methodhash" btree (hash)

Teraz chcę wybrać niektóre wartości, ale zapytanie jest niewiarygodnie wolne:

db=# explain 
    select hash, string, count(method) 
    from methods 
    where hash not in 
          (select hash from nostring) 
    group by hash, string 
    order by count(method) desc;
                                            QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost=160245190041.10..160245190962.07 rows=368391 width=182)
   Sort Key: (count(methods.method))
   ->  GroupAggregate  (cost=160245017241.77..160245057764.73 rows=368391 width=182)
       ->  Sort  (cost=160245017241.77..160245026451.53 rows=3683905 width=182)
             Sort Key: methods.hash, methods.string
             ->  Seq Scan on methods  (cost=0.00..160243305942.27 rows=3683905 width=182)
                   Filter: (NOT (SubPlan 1))
                   SubPlan 1
                   ->  Materialize  (cost=0.00..41071.54 rows=970636 width=33)
                     ->  Seq Scan on nostring  (cost=0.00..28634.36 rows=970636 width=33)

hashKolumna MD5 z stringi ma indeks. Myślę więc, że moim problemem jest to, że cała tabela jest posortowana według identyfikatora, a nie według wartości skrótu, więc najpierw trzeba trochę posortować, a następnie pogrupować?

Tabela nostringzawiera tylko listę skrótów, których nie chcę mieć. Ale potrzebuję obu tabel, aby mieć wszystkie wartości. Nie ma więc możliwości ich usunięcia.

dodatkowe informacje: żadna z kolumn nie może mieć wartości null (naprawiono to w definicji tabeli) i używam postgresql 9.2.

Odpowiedź LEFT JOINin @ dezso powinna być dobra. Indeks jednak nie będzie przydatny (jako taki), ponieważ zapytanie i tak musi odczytać całą tabelę - wyjątek stanowią skany tylko do indeksu w Postgresie 9.2+ i sprzyjające warunki, patrz poniżej.

SELECT m.hash, m.string, count(m.method) AS method_ct
FROM   methods m
LEFT   JOIN nostring n USING (hash)
WHERE  n.hash IS NULL
GROUP  BY m.hash, m.string 
ORDER  BY count(m.method) DESC;

Uruchom EXPLAIN ANALYZEzapytanie. Kilka razy, aby wykluczyć efekty pieniężne i hałas. Porównaj najlepsze wyniki.

Utwórz indeks wielokolumnowy pasujący do zapytania:

CREATE INDEX methods_cluster_idx ON methods (hash, string, method);

Czekać? Po tym, jak powiedziałem, że indeks nie pomoże? Cóż, potrzebujemy tego do CLUSTERstołu:

CLUSTER methods USING methods_cluster_idx;
ANALYZE methods;

Uruchom ponownie EXPLAIN ANALYZE. Jakiś szybciej? Powinno być.

CLUSTERto jednorazowa operacja polegająca na przepisaniu całej tabeli w kolejności stosowanego indeksu. Jest to również skutecznie VACUUM FULL. Jeśli chcesz mieć pewność, przeprowadź test wstępny VACUUM FULLsam, aby zobaczyć, co można z tym przypisać.

Jeśli twoja tabela widzi wiele operacji zapisu, efekt z czasem się pogorszy. Zaplanuj CLUSTERpoza godzinami pracy, aby przywrócić efekt. Dokładne dostrojenie zależy od konkretnego przypadku użycia. Podręcznik o CLUSTER.

CLUSTERjest dość prymitywnym narzędziem, wymaga wyłącznego blokady na stole. Jeśli nie możesz sobie na to pozwolić, zastanów się, pg_repackco może zrobić to samo bez wyłącznej blokady. Więcej w tej późniejszej odpowiedzi:

• Konfigurowanie PostgreSQL pod kątem wydajności odczytu

Jeśli odsetek NULLwartości w kolumnie methodjest wysoki (ponad ~ 20 procent, w zależności od rzeczywistych rozmiarów wierszy), indeks częściowy powinien pomóc:

CREATE INDEX methods_foo_idx ON methods (hash, string)
WHERE method IS NOT NULL;

_{(Twoja późniejsza aktualizacja pokazuje, że masz kolumny NOT NULL, więc nie dotyczy.)}

Jeśli korzystasz z PostgreSQL 9.2 lub nowszego (jak skomentował @deszo ), przedstawione indeksy mogą być przydatne bez tego, CLUSTERczy planista może korzystać ze skanów tylko do indeksu . Ma zastosowanie tylko w sprzyjających warunkach: Żadnych operacji zapisu, które VACUUMwpłynęłyby na mapę widoczności, ponieważ indeks musi obejmować wszystkie kolumny w zapytaniu. Zasadniczo tabele tylko do odczytu mogą tego używać w dowolnym momencie, podczas gdy mocno napisane tabele są ograniczone. Więcej informacji na Wiki Postgres.

W takim przypadku wyżej wymieniony indeks częściowy może być jeszcze bardziej użyteczny.

Jeśli z drugiej strony nie ma żadnych NULL wartości w kolumnie method, powinieneś
1.) zdefiniować to NOT NULLi
2.) użyć count(*)zamiast count(method), to jest nieco szybsze i robi to samo przy braku NULLwartości.

Jeśli musisz często wywoływać to zapytanie, a tabela jest tylko do odczytu, utwórz plik MATERIALIZED VIEW.

Egzotyczna drobna uwaga: Twoja tabela ma nazwę nostring, ale wydaje się, że zawiera skróty. Wykluczając skróty zamiast ciągów, istnieje szansa, że ​​wykluczysz więcej ciągów niż zamierzone. Niezwykle mało prawdopodobne, ale możliwe.

Witamy w DBA.SE!

Możesz spróbować sformułować swoje zapytanie w następujący sposób:

SELECT m.hash, string, count(method) 
FROM 
    methods m
    LEFT JOIN nostring n ON m.hash = n.hash
WHERE n.hash IS NULL
GROUP BY hash, string 
ORDER BY count(method) DESC;

lub inna możliwość:

SELECT m.hash, string, count(method) 
FROM 
    methods m
WHERE NOT EXISTS (SELECT hash FROM nostring WHERE hash = m.hash)
GROUP BY hash, string 
ORDER BY count(method) DESC;

NOT IN jest typowym zlewem pod względem wydajności, ponieważ trudno jest używać z nim indeksu.

Można to dodatkowo poprawić za pomocą indeksów. nostring.hashPrzydaje się indeks na . Ale najpierw: co teraz dostajesz? (Lepiej byłoby zobaczyć wynik, EXPLAIN ANALYZEponieważ same koszty nie mówią o czasie operacji).

Ponieważ skrót jest md5, prawdopodobnie możesz spróbować przekonwertować go na liczbę: możesz zapisać go jako liczbę lub po prostu utworzyć indeks funkcjonalny, który oblicza tę liczbę w niezmiennej funkcji.

Inne osoby już utworzyły funkcję pl / pgsql, która konwertuje (część) wartość md5 z tekstu na ciąg. Zobacz /programming/9809381/hashing-a-string-to-a-numeric-value-in-postgressql na przykład

Uważam, że tak naprawdę spędzasz dużo czasu na porównywaniu ciągów podczas skanowania indeksu. Jeśli uda ci się zapisać tę wartość jako liczbę, powinna być naprawdę naprawdę szybsza.

Często napotykałem ten problem i odkryłem prostą 2-częściową sztuczkę.

1. Utwórz indeks podciągów na wartości skrótu: (7 to zwykle dobra długość)

   create index methods_idx_hash_substring ON methods(substring(hash,1,7))

2. Niech twoje wyszukiwania / złączenia zawierają dopasowanie podłańcucha, więc planista zapytań powinien skorzystać z indeksu:

   stary: WHERE hash = :kwarg

   Nowy: WHERE (hash = :kwarg) AND (substring(hash,1,7) = substring(:kwarg,1,7))

Powinieneś również mieć indeks na raw hash.

wynik (zwykle) jest taki, że planista najpierw sprawdzi indeks podciągu i usunie większość wierszy. następnie dopasowuje pełny 32-znakowy skrót do odpowiedniego indeksu (lub tabeli). takie podejście spadło 800 ms zapytań do 4 dla mnie.