Wolne wyszukiwanie pełnotekstowe z powodu bardzo niedokładnych oszacowań wierszy

Wydaje się, że wykonywanie pełnotekstowych zapytań w tej bazie danych (przechowywanie biletów RT ( Request Tracker )) trwa bardzo długo. Tabela załączników (zawierająca dane pełnotekstowe) ma około 15 GB.

Schemat bazy danych jest następujący, ma około 2 milionów wierszy:

rt4 = # \ d + załączniki
                                                    Tabela „public.attachments”
     Kolumna | Wpisz | Modyfikatory | Przechowywanie | Opis
----------------- + ----------------------------- + - -------------------------------------------------- ------ + ---------- + -------------
 id | liczba całkowita | not null default nextval ('attachments_id_seq' :: regclass) | zwykły |
 transakcja | liczba całkowita | nie zerowy | zwykły |
 rodzic | liczba całkowita | nie jest wartością domyślną 0 | zwykły |
 messageid | różniące się postaciami (160) | | rozszerzony |
 temat | różniące się postaciami (255) | | rozszerzony |
 nazwa pliku | różniące się postaciami (255) | | rozszerzony |
 typ zawartości | różniące się postaciami (80) | | rozszerzony |
 kodowanie zawartości | różniące się postaciami (80) | | rozszerzony |
 treść | tekst | | rozszerzony |
 nagłówki | tekst | | rozszerzony |
 twórca | liczba całkowita | nie jest wartością domyślną 0 | zwykły |
 utworzony | sygnatura czasowa bez strefy czasowej | | zwykły |
 contentindex | tsvector | | rozszerzony |
Indeksy:
    „attachments_pkey” KLUCZ PODSTAWOWY, btree (id)
    „attachments1” btree (rodzic)
    „attachments2” btree (transactionid)
    „attachments3” btree (rodzic, transakcja)
    „contentindex_idx” gin (contentindex)
Ma identyfikatory OID: nie

Mogę bardzo szybko wykonać zapytanie do bazy danych (<1s) za pomocą zapytania takiego jak:

select objectid
from attachments
join transactions on attachments.transactionid = transactions.id
where contentindex @@ to_tsquery('frobnicate');

Jednak gdy RT uruchamia zapytanie, które ma wykonać wyszukiwanie pełnotekstowe indeksu w tej samej tabeli, zwykle zajmuje setki sekund. Dane wyjściowe analizy zapytania są następujące:

Pytanie

SELECT COUNT(DISTINCT main.id)
FROM Tickets main
JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectType = 'RT::Ticket' )
                                AND ( Transactions_1.ObjectId = main.id )
JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id )
WHERE (main.Status != 'deleted')
AND ( ( ( Attachments_2.ContentIndex @@ plainto_tsquery('frobnicate') ) ) )
AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id);

EXPLAIN ANALYZE wynik

                                                                             PLAN ZAPYTANIA 
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------
 Agregat (koszt = 51210.60..51210.61 wierszy = 1 szerokość = 4) (czas rzeczywisty = 477778.806..477778.806 wierszy = 1 pętla = 1)
   -> Zagnieżdżona pętla (koszt = 0,00..51210,57 wierszy = 15 szerokości = 4) (czas rzeczywisty = 17943.986..477775.174 wierszy = 4197 pętli = 1)
         -> Zagnieżdżona pętla (koszt = 0,00..40643,08 wierszy = 6507 szerokość = 8) (czas rzeczywisty = 8,526..20610,380 wierszy = 1714818 pętli = 1)
               -> Seq Scan on bilety główne (koszt = 0,00..98188,37 wierszy = 598 szerokość = 8) (czas rzeczywisty = 0,008..256,042 wierszy = 96990 pętli = 1)
                     Filtr: (((status) :: tekst „usunięty” :: tekst) ORAZ (id = efektywny) ORAZ ((typ) :: tekst = „bilet” :: tekst))
               -> Skanowanie indeksu przy użyciu transakcji1 na transakcjach transakcyjnych_1 (koszt = 0,00..51,36 wierszy = 15 szerokości = 8) (rzeczywisty czas = 0,102..0,202 wierszy = 18 pętli = 96990)
                     Indeks Cond: (((typ obiektu) :: text = 'RT :: Ticket' :: text) AND (objectid = main.id))
         -> Indeksuj skanowanie za pomocą załączników2 na załącznikach attachments_2 (koszt = 0,00..1,61 wiersza = 1 szerokość = 4) (rzeczywisty czas = 0,266..0,266 wierszy = 0 pętli = 1714818)
               Indeks War: (transakcja = transakcje_1.id)
               Filtr: (contentindex @@ plainto_tsquery ('frobnicate' :: text))
 Całkowity czas działania: 477778,883 ms

O ile mi wiadomo, wydaje się, że problem polega na tym, że nie używa on indeksu utworzonego w contentindexpolu ( contentindex_idx), a raczej filtruje dużą liczbę pasujących wierszy w tabeli załączników. Liczby ANALYZEwierszy w wynikach wyjaśniania również wydają się bardzo niedokładne, nawet po ostatnich : szacowane wiersze = 6507 rzeczywistych wierszy = 1714818.

Nie jestem do końca pewien, co dalej z tym zrobić.

Można to poprawić na tysiąc i jeden sposób, to powinno być kwestią milisekund .

Lepsze zapytania

To jest tylko twoje zapytanie sformatowane za pomocą aliasów i usuniętych szumów, aby usunąć mgłę:

SELECT count(DISTINCT t.id)
FROM   tickets      t
JOIN   transactions tr ON tr.objectid = t.id
JOIN   attachments  a  ON a.transactionid = tr.id
WHERE  t.status <> 'deleted'
AND    t.type = 'ticket'
AND    t.effectiveid = t.id
AND    tr.objecttype = 'RT::Ticket'
AND    a.contentindex @@ plainto_tsquery('frobnicate');

Większość problemu ze swoimi kłamstwami zapytanie w pierwszych dwóch tabelach ticketsi transactions, które są brakujące z pytaniem. Wypełniam wykształcone domysły.

• t.status, t.objecttypei tr.objecttypeprawdopodobnie nie powinno być text, ale enumewentualnie jakaś bardzo mała wartość odnosząca się do tabeli przeglądowej.

EXISTS częściowo połączyć

Zakładając, że tickets.idjest to klucz podstawowy, ta przepisana forma powinna być znacznie tańsza:

SELECT count(*)
FROM   tickets t
WHERE  status <> 'deleted'
AND    type = 'ticket'
AND    effectiveid = id
AND    EXISTS (
   SELECT 1
   FROM   transactions tr
   JOIN   attachments  a ON a.transactionid = tr.id
   WHERE  tr.objectid = t.id
   AND    tr.objecttype = 'RT::Ticket'
   AND    a.contentindex @@ plainto_tsquery('frobnicate')
   );

Zamiast mnożenia wierszy przez dwa sprzężenia 1: n, tylko w celu zwinięcia wielu dopasowań na końcu count(DISTINCT id), użyj połączenia EXISTSczęściowego, które może przestać szukać dalej, jak tylko zostanie znalezione pierwsze dopasowanie, a jednocześnie przestaje być ostatnim DISTINCTkrokiem. Według dokumentacji:

Podkwerenda będzie na ogół wykonywana tylko na tyle długo, aby ustalić, czy zwracany jest co najmniej jeden wiersz, a nie do końca.

Skuteczność zależy od liczby transakcji na bilet i załączników na transakcję.

Określ kolejność połączeń za pomocą join_collapse_limit

Jeśli wiesz, że wyszukiwane hasło attachments.contentindexjest bardzo selektywne - bardziej selektywne niż inne warunki w zapytaniu (co prawdopodobnie ma miejsce w przypadku „frobnicate”, ale nie „problem”), możesz wymusić sekwencję złączeń. Narzędzie do planowania zapytań nie może ocenić selektywności poszczególnych słów, z wyjątkiem najczęściej używanych. Według dokumentacji:

join_collapse_limit( integer)

[...]
Ponieważ planista zapytań nie zawsze wybiera optymalną kolejność łączenia, zaawansowani użytkownicy mogą tymczasowo ustawić tę zmienną na 1, a następnie jawnie określić kolejność łączenia.

Użyj SET LOCALtego celu, aby ustawić go tylko dla bieżącej transakcji.

BEGIN;
SET LOCAL join_collapse_limit = 1;

SELECT count(DISTINCT t.id)
FROM   attachments  a                              -- 1st
JOIN   transactions tr ON tr.id = a.transactionid  -- 2nd
JOIN   tickets      t  ON t.id = tr.objectid       -- 3rd
WHERE  t.status <> 'deleted'
AND    t.type = 'ticket'
AND    t.effectiveid = t.id
AND    tr.objecttype = 'RT::Ticket'
AND    a.contentindex @@ plainto_tsquery('frobnicate');

ROLLBACK; -- or COMMIT;

Kolejność WHEREwarunków jest zawsze nieistotna. Znaczenie ma tylko kolejność dołączeń.

Lub użyj CTE, takiego jak @jjanes, wyjaśniono w „Opcji 2”. dla podobnego efektu.

Indeksy

Indeksy B-drzewa

Podjąć wszelkie warunki, na ticketsktóre są stosowane identycznie z większością zapytań i utworzyć częściowe indeksu na tickets:

CREATE INDEX tickets_partial_idx
ON tickets(id)
WHERE  status <> 'deleted'
AND    type = 'ticket'
AND    effectiveid = id;

Jeśli jeden z warunków jest zmienny, usuń go z WHEREwarunku i zamiast tego wstaw kolumnę jako kolumnę indeksu.

Kolejny na transactions:

CREATE INDEX transactions_partial_idx
ON transactions(objecttype, objectid, id)

Trzecia kolumna służy tylko do skanowania indeksów.

Ponadto, ponieważ masz ten indeks złożony z dwiema liczbami całkowitymi na attachments:

"attachments3" btree (parent, transactionid)

Ten dodatkowy indeks jest kompletnym marnotrawstwem , usuń go:

"attachments1" btree (parent)

Detale:

• Czy indeks złożony jest również przydatny w przypadku zapytań dotyczących pierwszego pola?

Indeks GIN

Dodaj transactioniddo swojego indeksu GIN, aby był o wiele bardziej skuteczny. Może to być kolejna srebrna kula , ponieważ potencjalnie umożliwia skanowanie tylko za pomocą indeksu, całkowicie eliminując wizyty na dużym stole.
Potrzebujesz dodatkowych klas operatorów zapewnianych przez dodatkowy moduł btree_gin. Szczegółowe instrukcje:

• Łączenie wewnętrzne za pomocą kolumny tablicowej

"contentindex_idx" gin (transactionid, contentindex)

4 bajty z integerkolumny nie zwiększają znacznie indeksu. Na szczęście dla Ciebie indeksy GIN różnią się od indeksów B-drzewa w kluczowym aspekcie. Według dokumentacji:

Wielokolumnowy indeks GIN może być używany z warunkami zapytania obejmującymi dowolny podzbiór kolumn indeksu . W przeciwieństwie do B-drzewa lub GiST, skuteczność wyszukiwania indeksu jest taka sama, niezależnie od tego, które kolumny indeksu wykorzystują warunki zapytania.

Odważny nacisk moje. Potrzebujesz więc tylko jednego (dużego i nieco kosztownego) indeksu GIN.

Definicja tabeli

Przesuń integer not null columnsdo przodu. Ma to kilka niewielkich pozytywnych skutków dla przechowywania i wydajności. W tym przypadku oszczędza 4 - 8 bajtów na wiersz.

                      Table "public.attachments"
         Column      |            Type             |         Modifiers
    -----------------+-----------------------------+------------------------------
     id              | integer                     | not null default nextval('...
     transactionid   | integer                     | not null
     parent          | integer                     | not null default 0
     creator         | integer                     | not null default 0  -- !
     created         | timestamp                   |                     -- !
     messageid       | character varying(160)      |
     subject         | character varying(255)      |
     filename        | character varying(255)      |
     contenttype     | character varying(80)       |
     contentencoding | character varying(80)       |
     content         | text                        |
     headers         | text                        |
     contentindex    | tsvector                    |

opcja 1

Planista nie ma wglądu w prawdziwą naturę relacji między EffectiveId i id, więc prawdopodobnie myśli klauzulę:

main.EffectiveId = main.id

będzie znacznie bardziej selektywna niż w rzeczywistości. Jeśli tak właśnie myślę, EffectiveID jest prawie zawsze równy main.id, ale planista tego nie wie.

Prawdopodobnie lepszym sposobem na przechowywanie tego typu relacji jest zwykle zdefiniowanie NULL wartości EffectiveID w celu oznaczenia „efektywnie taki sam jak identyfikator” i zapisanie czegoś w nim tylko w przypadku różnicy.

Zakładając, że nie chcesz reorganizować swojego schematu, możesz spróbować obejść go, przepisując tę ​​klauzulę jako coś w rodzaju:

main.EffectiveId+0 between main.id+0 and main.id+0

Planista może założyć, że betweenjest mniej selektywny niż równość, i to może wystarczyć, aby wyrzucić go z obecnej pułapki.

Opcja 2

Innym podejściem jest użycie CTE:

WITH attach as (
    SELECT * from Attachments 
        where ContentIndex @@ plainto_tsquery('frobnicate') 
)
<rest of query goes here, with 'attach' used in place of 'Attachments'>

Zmusza to planistę do korzystania z ContentIndex jako źródła selektywności. Gdy zostanie to do tego zmuszone, mylące korelacje kolumn w tabeli biletów nie będą już wyglądać tak atrakcyjnie. Oczywiście, jeśli ktoś wyszuka „problem” zamiast „frobnicate”, może to przynieść odwrót.

Opcja 3

Aby dalej zbadać szacunki złych wierszy, powinieneś uruchomić poniższe zapytanie we wszystkich 2 ^ 3 = 8 permutacjach różnych klauzul AND, które są komentowane. Pomoże to ustalić, skąd pochodzi zła ocena.

explain analyze
SELECT * FROM Tickets main WHERE 
   main.Status != 'deleted' AND 
   main.Type = 'ticket' AND 
   main.EffectiveId = main.id;