Powolne ORDER BY z LIMITEM

Mam to zapytanie:

SELECT * 
FROM location 
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw')) 
order by displaycount

Jestem z tego zadowolony:

"Sort  (cost=3842.56..3847.12 rows=1826 width=123) (actual time=1.915..2.084 rows=1307 loops=1)"
"  Sort Key: displaycount"
"  Sort Method: quicksort  Memory: 206kB"
"  ->  Bitmap Heap Scan on location  (cost=34.40..3743.64 rows=1826 width=123) (actual time=0.788..1.208 rows=1307 loops=1)"
"        Recheck Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"        ->  Bitmap Index Scan on location_lower_idx  (cost=0.00..33.95 rows=1826 width=0) (actual time=0.760..0.760 rows=1307 loops=1)"
"              Index Cond: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2.412 ms"

Ale kiedy dodam LIMIT, wykonanie zajmuje więcej niż 2 sekundy:

SELECT * 
FROM location 
WHERE to_tsvector('simple',unaccent2("city"))
   @@ to_tsquery('simple',unaccent2('wroclaw')) 
order by displaycount 
limit 20

Wyjaśnić:

"Limit  (cost=0.00..1167.59 rows=20 width=123) (actual time=2775.452..2775.643 rows=20 loops=1)"
"  ->  Index Scan using location_displaycount_index on location  (cost=0.00..106601.25 rows=1826 width=123) (actual time=2775.448..2775.637 rows=20 loops=1)"
"        Filter: (to_tsvector('simple'::regconfig, unaccent2((city)::text)) @@ '''wroclaw'''::tsquery)"
"Total runtime: 2775.693 ms"

Myślę, że to jakiś problem z ORDER BY i LIMIT. Jak zmusić PostgreSQL do korzystania z indeksu i zamawiania na końcu?

Subquery nie pomaga:

SELECT * 
FROM (
    SELECT * 
    FROM location 
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw')) 
    order by displaycount
) t 
LIMIT 20;

lub:

SELECT * 
FROM (
    SELECT * 
    FROM location 
    WHERE to_tsvector('simple',unaccent2("city"))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
) t 
order by displaycount 
LIMIT 20;

Domyślam się, że to naprawi twoje zapytanie:

SELECT * 
FROM   location 
WHERE     to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) 
ORDER  BY to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) DESC
         ,displaycount 
LIMIT  20;

Powtarzam ten WHEREwarunek jako pierwszy element ORDER BYklauzuli - która jest logicznie redundantna, ale powinna powstrzymać planistę kwerend od zakładania, że ​​lepiej byłoby przetwarzać wiersze zgodnie z indeksem location_displaycount_index- co okazuje się znacznie droższe.

Podstawowym problemem jest to, że planista zapytań w oczywisty sposób rażąco błędnie ocenia selektywność i / lub koszt twojego WHEREstanu. Mogę tylko spekulować, dlaczego tak jest.

Czy korzystasz z automatycznego odkurzania - które również powinny zadbać o to, abyś działał ANALYZEna swoich stołach? Czy twoje statystyki tabel są aktualne? Dowolny efekt, jeśli uruchomisz:

ANALYZE location;

I spróbuj ponownie?

Może być również tak, że selektywność @@operatora jest źle oceniana. Wyobrażam sobie, że z powodów logicznych bardzo trudno jest to oszacować.

Jeśli moje zapytanie nie rozwiąże problemu i ogólnie w celu zweryfikowania leżącej u podstaw teorii, wykonaj jedną z tych dwóch rzeczy:

• tymczasowo usuń indeks location_displaycount_index

• tymczasowo wyłącz podstawowe skanowanie indeksu , uruchamiając:

  SET enable_indexscan = OFF;

Ta ostatnia jest mniej inwazyjna i wpływa tylko na bieżącą sesję. Pozostawia metody bitmap heap scani bitmap index scanotwarte, które są używane przez szybszy plan.
Następnie ponownie uruchom zapytanie.

BTW: Jeśli teoria jest solidna, twoje zapytanie (tak jak teraz) będzie znacznie szybsze z mniej selektywnym wyszukiwanym terminem w stanie FTS - w przeciwieństwie do tego, czego możesz się spodziewać. Spróbuj.

Podczas korzystania z LIMIT postgresql dostosowania jego plan jest optymalny tylko do pobierania podzbioru wiersza. Niestety w twoim przypadku to zły wybór. Może to być spowodowane tym, że statystyki dla tabeli są zbyt stare. Spróbuj zaktualizować statystyki, wydając lokalizację VACUUM ANALYZE;

Wymuszenie użycia indeksów jest zwykle wykonywane przez niedozwolone użycie skanowania sekwencyjnego (ustaw enable_seqscan = false). Jednak w twoim przypadku nie wykonuje on skanowania sekwencyjnego, po prostu przełącza się na inny indeks dla zapytania z LIMIT.

Jeśli analiza nie pomoże ci ustalić, której wersji postgresql używasz? A także ile wierszy jest w tabeli?