Wpływ LIMIT 1 na czas pytania i użycie RAM

Mam takie proste pytanie:

explain analyze SELECT id, ta, tb FROM public.xyz WHERE ta in(1,2) AND tb = 44 ORDER BY id DESC LIMIT 1;

Limit  (cost=0.44..73.58 rows=1 width=20) (actual time=13829.144..13829.145 rows=0 loops=1)
  ->  Index Scan Backward using xyz_pkey on xyz  (cost=0.44..1226229.92 rows=16765 width=20) (actual time=13829.142..13829.142 rows=0 loops=1)
        Filter: ((ta = ANY ('{1,2}'::integer[])) AND (tb = 44))
        Rows Removed by Filter: 26782368
Planning Time: 0.444 ms
Execution Time: 13829.183 ms

Zmiana limit 1 na limit 100 skutkuje całkiem innym planem:

explain analyze SELECT id, ta, tb FROM public.xyz WHERE ta in(1,2) AND tb = 44 ORDER BY id DESC LIMIT 100;

Limit  (cost=1313.18..1313.43 rows=100 width=20) (actual time=0.100..0.101 rows=0 loops=1)
  ->  Sort  (cost=1313.18..1355.09 rows=16765 width=20) (actual time=0.100..0.100 rows=0 loops=1)
        Sort Key: id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Only Scan using xyz_tb_ta_id_idx on xyz  (cost=0.56..672.43 rows=16765 width=20) (actual time=0.093..0.094 rows=0 loops=1)
              Index Cond: ((tb = 44) AND (ta = ANY ('{1,2}'::integer[])))
              Heap Fetches: 0
Planning Time: 0.187 ms
Execution Time: 0.119 ms

Jak zrobić żeby to pierwsze też użyło indexu?
Dodam, że w tabeli jest kilkadziesiąt milionów rekordów, ale żaden nie spełnia tych warunków.

Oprócz tego mam jeszcze jeden bardzo niepożadany efekt uboczny 1 pytania: wzrost zużycia RAM o kilka GB do czasu zamknięcia połączenia. Na zdalnym docker przez wsl z limitami tego nie odczułem, ale jak zainstalowałem standalone postgresql bez dockera to mi zajęło prawie cały RAM(ustawiłem 6 GB w virtualbox).

Odpaliłeś ANALYZE xyx;? Bez regularnych statystyk baza nie wie, że w tabeli jest kilkadziesiąt milionów rekordów, ale żaden nie spełnia tych warunków. i query planner głupieje. Można też odpalić VACUUM ANALYZE xyx;, żeby mieć wszystko w jednej komendzie

ale jak zainstalowałem standalone postgresql bez dockera to mi zajęło prawie cały RAM(ustawiłem 6 GB w virtualbox).

Jak zainstalowałeś? Normalnie postgres zużywa minimalne ilości ramu, możliwe, że twoja dystrybucja ustawiła po swojemu. Wywołaj select * from pg_settings; , porównaj z PGTUNE https://pgtune.leopard.in.ua/ czy jakaś wartość przypadkiem nie jest ustawiona zbyt wysoko

Tu problem wynika z kolejności sortowania i selectowania, które nie są spójne z kolejnością w indeksie. Wiersz z największym ID może mieć wartość TB=44,TA=1, a przed nim może być milion wierszy z TB=44,TA=2 (przeglądamy indeks w drugą stronę).

Spróbuj rozbić zapytanie na dwa uniony, gdzie dla każdej wartości TA masz osobne zapytanie. Postgres ładnie zmerguje dwa posortowane strumienie z danymi. Przecież tak działa partycjonowanie tabel.

—-

Co do statystyk, to zakładam, źe masz mnóstwo wierszy, które spełniają warunek na TA i mnóstwo wierszy spełniających warunek na TB, ale żadnego wiersza, który jest wspólny. Nie sortujesz w kolejności spójnej do indeksu, więc PG stwierdza, że musi przeczytać mniej danych w pierwszym zapytaniu.

Zakładając, że kolumny TA, TB i ID są kompletnie nieskorelowane, policz sobie na kartce, ile wierszy trzeba statystycznie wyciągnąć, żeby wykonać zapytanie z LIMIT 1 według planu A i według planu B. Możliwe, że postgres ma rację wybierając plan A.

Może extended statistics pomogą: https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED

—-
Jeśli ci to nie przeszkadza, możesz zmienić kolejność sortowania na ORDER BY TA, ID

—-
Jak bardzo zahardkodowane są te wartości? Chamski indeks częściowy z WHERE ta in(1,2) AND tb = 44 pewnie by pomógł.

Podsumowując:

1. Jak ustawić twardy limit maksymalnie 2 GB RAM dla całej bazy? Niestety PostgreSQL tworzy nowe processy z każdym połączeniem. Nie chcę ryzykować stabilności systemu.
2. Wszystko co ma IN(...) albo ANY = ... i pracuje na dużej ilości recordów nie nadaje się i trzeba to przepisać na coś innego. Trudno, że będzie 2x dłuższe z union. Index w tym przypadku nie ma sensu, gdy są same duplikaty w większości.

the_dude777 napisał(a):

1. Jak ustawić twardy limit maksymalnie 2 GB RAM dla całej bazy? Niestety PostgreSQL tworzy nowe processy z każdym połączeniem. Nie chcę ryzykować stabilności systemu.

Odpal bazę w wirtualce z sztywnym limitem 2 GB + hak na potrzeby wirtualki.

Linux nie ma litości - ubije każdy proces gdy się go zacznie głodzić.

the_dude777 napisał(a):

Podsumowując:

1. Jak ustawić twardy limit maksymalnie 2 GB RAM dla całej bazy? Niestety PostgreSQL tworzy nowe processy z każdym połączeniem. Nie chcę ryzykować stabilności systemu.

Najlepiej wygeneruj sobie konfigurację tutaj https://pgtune.leopard.in.ua/ . Liczba połączeń powinna być zawsze ograniczona a maksymalne zużycie pamięci przez jeden proces też da się ustawić choć powinno się to robić z głową mając na uwagę inne limity.