Zapytanie PostgreSQL jest bardzo powolne, gdy dodawane jest zapytanie podrzędne

Mam stosunkowo proste zapytanie dotyczące tabeli z 1,5 mln wierszy:

SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;

EXPLAIN ANALYZE wynik:

Limit  (cost=8.84..12.86 rows=1 width=8) (actual time=0.985..0.986 rows=1 loops=1)
  ->  Bitmap Heap Scan on publication  (cost=8.84..12.86 rows=1 width=8) (actual time=0.984..0.985 rows=1 loops=1)
        Recheck Cond: ((mtid = 9762715) OR (last_modifier = 21321))
        ->  BitmapOr  (cost=8.84..8.84 rows=1 width=0) (actual time=0.971..0.971 rows=0 loops=1)
              ->  Bitmap Index Scan on publication_pkey  (cost=0.00..4.42 rows=1 width=0) (actual time=0.295..0.295 rows=1 loops=1)
                    Index Cond: (mtid = 9762715)
              ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..4.42 rows=1 width=0) (actual time=0.674..0.674 rows=0 loops=1)
                    Index Cond: (last_modifier = 21321)
Total runtime: 1.027 ms

Jak dotąd dobry, szybki i wykorzystuje dostępne indeksy.
Teraz, jeśli nieco zmodyfikuję zapytanie, wynikiem będzie:

SELECT mtid FROM publication
WHERE mtid IN (SELECT 9762715) OR last_modifier=21321
LIMIT 5000;

Dane EXPLAIN ANALYZEwyjściowe to:

Limit  (cost=0.01..2347.74 rows=5000 width=8) (actual time=2735.891..2841.398 rows=1 loops=1)
  ->  Seq Scan on publication  (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1)
        Filter: ((hashed SubPlan 1) OR (last_modifier = 21321))
        SubPlan 1
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 2841.442 ms

Nie tak szybko i przy użyciu skanowania sekwencyjnego ...

Oczywiście oryginalne zapytanie uruchamiane przez aplikację jest nieco bardziej złożone, a nawet wolniejsze, i oczywiście nie jest generowany hibernacja (SELECT 9762715), ale powolność jest nawet do tego (SELECT 9762715)! Zapytanie jest generowane przez hibernację, więc ich zmiana jest dość trudna, a niektóre funkcje nie są dostępne (np. UNIONNie są dostępne, co byłoby szybkie).

Pytania

1. Dlaczego nie można użyć indeksu w drugim przypadku? Jak można je wykorzystać?
2. Czy mogę poprawić wydajność zapytań w inny sposób?

Dodatkowe przemyślenia

Wydaje się, że moglibyśmy użyć pierwszego przypadku, ręcznie wykonując WYBÓR, a następnie umieszczając wynikową listę w zapytaniu. Nawet przy 5000 liczb na liście IN () jest czterokrotnie szybszy niż drugie rozwiązanie. Wydaje się to jednak NIEPRAWIDŁOWE (może być 100 razy szybsze :)). Jest całkowicie niezrozumiałe, dlaczego planista zapytań stosuje zupełnie inną metodę dla tych dwóch zapytań, dlatego chciałbym znaleźć lepsze rozwiązanie tego problemu.

Rdzeń problemu staje się tutaj oczywisty:

Seq Scan przy publikacji (koszt = 0,01..349652,84 wierszy = 744661 szerokość = 8) (rzeczywisty czas = 2735.888..2841.393 wierszy = 1 pętla = 1)

Szacunki Postgres zwracają 744661 wierszy, podczas gdy w rzeczywistości okazuje się, że jest to jeden wiersz. Jeśli Postgres nie wie lepiej, czego oczekiwać od zapytania, nie może lepiej zaplanować. Musielibyśmy zobaczyć rzeczywiste zapytanie ukryte za nimi (SELECT 9762715)- i prawdopodobnie także znalibyśmy definicję tabeli, ograniczenia, liczności i rozkład danych. Oczywiście, Postgres nie jest w stanie przewidzieć, jak niewiele wierszy zostanie zwrócony przez nią. Mogą istnieć sposoby na przepisanie zapytania, w zależności od tego, co to jest .

Jeśli wiesz, że podzapytanie nigdy nie może zwrócić więcej niż nwierszy, możesz po prostu powiedzieć Postgres, używając:

SELECT mtid
FROM   publication
WHERE  mtid IN (SELECT ... LIMIT n) --  OR last_modifier=21321
LIMIT  5000;

Jeśli n jest wystarczająco małe, Postgres przełączy się na skanowanie indeksu (bitmapy). Działa to jednak tylko w prostym przypadku. Przestaje działać podczas dodawania ORwarunku: narzędzie do planowania zapytań nie może obecnie sobie z tym poradzić.

Rzadko używam IN (SELECT ...)na początek. Zazwyczaj istnieje lepszy sposób na wdrożenie tego samego, często z EXISTSłączeniem częściowym. Czasami za pomocą ( LEFT) JOIN( LATERAL) ...

Oczywistym obejściem byłoby użycie UNION, ale wykluczyłeś to. Nie mogę powiedzieć więcej, nie znając faktycznego podzapytania i innych istotnych szczegółów.

Mój kolega znalazł sposób na zmianę zapytania, tak aby wymagało to prostego przepisania i robi to, co musi zrobić, tj. Wykonuje podselekcję w jednym kroku, a następnie wykonuje dalsze operacje na wyniku:

SELECT mtid FROM publication 
WHERE 
  mtid = ANY( (SELECT ARRAY(SELECT 9762715))::bigint[] )
  OR last_modifier=21321
LIMIT 5000;

Wyjaśnij teraz analizę:

 Limit  (cost=92.58..9442.38 rows=2478 width=8) (actual time=0.071..0.074 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.01..0.02 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Bitmap Heap Scan on publication  (cost=92.56..9442.36 rows=2478 width=8) (actual time=0.069..0.070 rows=1 loops=1)
         Recheck Cond: ((mtid = ANY (($1)::bigint[])) OR (last_modifier = 21321))
         Heap Blocks: exact=1
         ->  BitmapOr  (cost=92.56..92.56 rows=2478 width=0) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Bitmap Index Scan on publication_pkey  (cost=0.00..44.38 rows=10 width=0) (actual time=0.046..0.046 rows=1 loops=1)
                     Index Cond: (mtid = ANY (($1)::bigint[]))
               ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..46.94 rows=2468 width=0) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: (last_modifier = 21321)
 Planning time: 0.704 ms
 Execution time: 0.153 ms

Wydaje się, że możemy stworzyć prosty parser, który wyszukuje i przepisuje wszystkie podselekcje w ten sposób, i dodaje go do hibernacji, aby manipulować rodzimym zapytaniem.

Odpowiedz na drugie pytanie: Tak, możesz dodać ORDER BY do swojego podzapytania, co będzie miało pozytywny wpływ. Ale podobieństwo do rozwiązania „ISTNIEJE (podzapytanie)” pod względem wydajności. Istnieje znacząca różnica nawet przy podzapytaniu skutkującym dwoma wierszami.

SELECT mtid FROM publication
WHERE mtid IN (SELECT #column# ORDER BY #column#) OR last_modifier=21321
LIMIT 5000;