Co zrobić, gdy postgres dobiera zły index

0

Witam, przychodzę z kolejnym problemem, jest to w pewnym stopniu kontynuacja tego wątku:
https://4programmers.net/Forum/Bazy_danych/373258-dlaczego_bezposrednie_wskazanie_danych_jest_duzo_szybsze?p=1966327#id1966327

Dokładniej tutaj opiszę swój problem i podam skrypty na pełne odtworzenie sytuacji.

Tworzę 2 identyczne tabele

CREATE TABLE logs1 (
    user_id int4 NOT NULL,
    create_time timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE logs2 (
    user_id int4 NOT NULL,
    create_time timestamptz NOT NULL DEFAULT now()
);

Do pierwszej tabeli wrzucam 200 000 000 wpisów, po 2 000 000 na każdego ze 100 użytkowników:

INSERT INTO public.logs1 (user_id) (select 1 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 2 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 3 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 4 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 5 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 6 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 7 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 8 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 9 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 10 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 11 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 12 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 13 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 14 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 15 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 16 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 17 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 18 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 19 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 20 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 21 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 22 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 23 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 24 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 25 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 26 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 27 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 28 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 29 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 30 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 31 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 32 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 33 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 34 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 35 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 36 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 37 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 38 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 39 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 40 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 41 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 42 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 43 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 44 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 45 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 46 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 47 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 48 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 49 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 50 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 51 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 52 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 53 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 54 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 55 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 56 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 57 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 58 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 59 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 60 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 61 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 62 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 63 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 64 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 65 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 66 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 67 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 68 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 69 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 70 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 71 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 72 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 73 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 74 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 75 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 76 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 77 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 78 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 79 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 80 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 81 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 82 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 83 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 84 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 85 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 86 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 87 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 88 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 89 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 90 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 91 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 92 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 93 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 94 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 95 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 96 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 97 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 98 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 99 from generate_series(1,2000000));
INSERT INTO public.logs1 (user_id) (select 100 from generate_series(1,2000000));

Do drugiej tabeli również wrzucam 200 000 000 wpisów, natomiast w taki sposób, że dane dotyczące kilku użytkowników stanowią zdecydowaną większość wszystkich danych:

INSERT INTO public.logs2 (user_id) (select 1 from generate_series(1,95802970));
INSERT INTO public.logs2 (user_id) (select 2 from generate_series(1,10561980));
INSERT INTO public.logs2 (user_id) (select 3 from generate_series(1,17014900));
INSERT INTO public.logs2 (user_id) (select 4 from generate_series(1,14987000));
INSERT INTO public.logs2 (user_id) (select 5 from generate_series(1,14701010));
INSERT INTO public.logs2 (user_id) (select 6 from generate_series(1,13137950));
INSERT INTO public.logs2 (user_id) (select 7 from generate_series(1,7180130));
INSERT INTO public.logs2 (user_id) (select 8 from generate_series(1,6930550));
INSERT INTO public.logs2 (user_id) (select 9 from generate_series(1,4260890));
INSERT INTO public.logs2 (user_id) (select 10 from generate_series(1,3702960));
INSERT INTO public.logs2 (user_id) (select 11 from generate_series(1,3215300));
INSERT INTO public.logs2 (user_id) (select 12 from generate_series(1,2648260));
INSERT INTO public.logs2 (user_id) (select 13 from generate_series(1,1197320));
INSERT INTO public.logs2 (user_id) (select 14 from generate_series(1,802200));
INSERT INTO public.logs2 (user_id) (select 15 from generate_series(1,709840));
INSERT INTO public.logs2 (user_id) (select 16 from generate_series(1,661650));
INSERT INTO public.logs2 (user_id) (select 17 from generate_series(1,628120));
INSERT INTO public.logs2 (user_id) (select 18 from generate_series(1,590780));
INSERT INTO public.logs2 (user_id) (select 19 from generate_series(1,433570));
INSERT INTO public.logs2 (user_id) (select 20 from generate_series(1,291460));
INSERT INTO public.logs2 (user_id) (select 21 from generate_series(1,195000));
INSERT INTO public.logs2 (user_id) (select 22 from generate_series(1,158450));
INSERT INTO public.logs2 (user_id) (select 23 from generate_series(1,55980));
INSERT INTO public.logs2 (user_id) (select 24 from generate_series(1,34210));
INSERT INTO public.logs2 (user_id) (select 25 from generate_series(1,21660));
INSERT INTO public.logs2 (user_id) (select 26 from generate_series(1,17400));
INSERT INTO public.logs2 (user_id) (select 27 from generate_series(1,9500));
INSERT INTO public.logs2 (user_id) (select 28 from generate_series(1,7850));
INSERT INTO public.logs2 (user_id) (select 29 from generate_series(1,7450));
INSERT INTO public.logs2 (user_id) (select 30 from generate_series(1,4910));
INSERT INTO public.logs2 (user_id) (select 31 from generate_series(1,4860));
INSERT INTO public.logs2 (user_id) (select 32 from generate_series(1,3150));
INSERT INTO public.logs2 (user_id) (select 33 from generate_series(1,2760));
INSERT INTO public.logs2 (user_id) (select 34 from generate_series(1,2620));
INSERT INTO public.logs2 (user_id) (select 35 from generate_series(1,2160));
INSERT INTO public.logs2 (user_id) (select 36 from generate_series(1,1810));
INSERT INTO public.logs2 (user_id) (select 37 from generate_series(1,1630));
INSERT INTO public.logs2 (user_id) (select 38 from generate_series(1,1410));
INSERT INTO public.logs2 (user_id) (select 39 from generate_series(1,1090));
INSERT INTO public.logs2 (user_id) (select 40 from generate_series(1,1050));
INSERT INTO public.logs2 (user_id) (select 41 from generate_series(1,830));
INSERT INTO public.logs2 (user_id) (select 42 from generate_series(1,610));
INSERT INTO public.logs2 (user_id) (select 43 from generate_series(1,560));
INSERT INTO public.logs2 (user_id) (select 44 from generate_series(1,540));
INSERT INTO public.logs2 (user_id) (select 45 from generate_series(1,500));
INSERT INTO public.logs2 (user_id) (select 46 from generate_series(1,490));
INSERT INTO public.logs2 (user_id) (select 47 from generate_series(1,330));
INSERT INTO public.logs2 (user_id) (select 48 from generate_series(1,240));
INSERT INTO public.logs2 (user_id) (select 49 from generate_series(1,210));
INSERT INTO public.logs2 (user_id) (select 50 from generate_series(1,160));
INSERT INTO public.logs2 (user_id) (select 51 from generate_series(1,130));
INSERT INTO public.logs2 (user_id) (select 52 from generate_series(1,110));
INSERT INTO public.logs2 (user_id) (select 53 from generate_series(1,100));
INSERT INTO public.logs2 (user_id) (select 54 from generate_series(1,100));
INSERT INTO public.logs2 (user_id) (select 55 from generate_series(1,90));
INSERT INTO public.logs2 (user_id) (select 56 from generate_series(1,90));
INSERT INTO public.logs2 (user_id) (select 57 from generate_series(1,90));
INSERT INTO public.logs2 (user_id) (select 58 from generate_series(1,80));
INSERT INTO public.logs2 (user_id) (select 59 from generate_series(1,80));
INSERT INTO public.logs2 (user_id) (select 60 from generate_series(1,70));
INSERT INTO public.logs2 (user_id) (select 61 from generate_series(1,70));
INSERT INTO public.logs2 (user_id) (select 62 from generate_series(1,70));
INSERT INTO public.logs2 (user_id) (select 63 from generate_series(1,60));
INSERT INTO public.logs2 (user_id) (select 64 from generate_series(1,40));
INSERT INTO public.logs2 (user_id) (select 65 from generate_series(1,40));
INSERT INTO public.logs2 (user_id) (select 66 from generate_series(1,40));
INSERT INTO public.logs2 (user_id) (select 67 from generate_series(1,30));
INSERT INTO public.logs2 (user_id) (select 68 from generate_series(1,30));
INSERT INTO public.logs2 (user_id) (select 69 from generate_series(1,30));
INSERT INTO public.logs2 (user_id) (select 70 from generate_series(1,30));
INSERT INTO public.logs2 (user_id) (select 71 from generate_series(1,30));
INSERT INTO public.logs2 (user_id) (select 72 from generate_series(1,20));
INSERT INTO public.logs2 (user_id) (select 73 from generate_series(1,20));
INSERT INTO public.logs2 (user_id) (select 74 from generate_series(1,20));
INSERT INTO public.logs2 (user_id) (select 75 from generate_series(1,20));
INSERT INTO public.logs2 (user_id) (select 76 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 77 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 78 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 79 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 80 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 81 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 82 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 83 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 84 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 85 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 86 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 87 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 88 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 89 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 90 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 91 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 92 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 93 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 94 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 95 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 96 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 97 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 98 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 99 from generate_series(1,10));
INSERT INTO public.logs2 (user_id) (select 100 from generate_series(1,10));

Ttworzę indexy:

CREATE INDEX logs1_user_id_idx ON ONLY logs1 USING btree (user_id);
CREATE INDEX logs1_create_time_idx ON ONLY logs1 USING btree (create_time);
CREATE INDEX logs1_user_id_create_time_idx ON ONLY logs1 USING btree (user_id, create_time);

CREATE INDEX logs2_user_id_idx ON ONLY logs2 USING btree (user_id);
CREATE INDEX logs2_create_time_idx ON ONLY logs2 USING btree (create_time);
CREATE INDEX logs2_user_id_create_time_idx ON ONLY logs2 USING btree (user_id, create_time);

Wykonuję analyze:

analyze logs1;
analyze logs2;

W rezultacie mam 2 tabele z takimi samymi schematami, indexami, taką samą ilością danych, jednak z innym rozkładem tych danych.

Odpaliłem sobie te zapytanie na obu tabelach:

EXPLAIN ANALYZE
SELECT *
FROM logs1 -- logs2
WHERE user_id = 1001 
ORDER BY create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only

I oto wynik:

--------- logs1 ---------
Limit  (cost=0.57..8.59 rows=1 width=12) (actual time=0.026..0.028 rows=0 loops=1)
  ->  Index Only Scan Backward using logs1_user_id_create_time_idx on logs1  (cost=0.57..8.59 rows=1 width=12) (actual time=0.024..0.024 rows=0 loops=1)
        Index Cond: (user_id = 1001)
        Heap Fetches: 0
Planning Time: 0.447 ms
Execution Time: 0.062 ms

--------- logs2 ---------
Limit  (cost=0.57..0.78 rows=10 width=12) (actual time=0.042..0.044 rows=0 loops=1)
  ->  Index Only Scan Backward using logs2_user_id_create_time_idx on logs2  (cost=0.57..414.57 rows=20000 width=12) (actual time=0.039..0.040 rows=0 loops=1)
        Index Cond: (user_id = 1001)
        Heap Fetches: 0
Planning Time: 0.220 ms
Execution Time: 0.080 ms

W obu przypadkach został użyty prawidłówy index - logs1_user_id_create_time_idx, przez co zapytanie wykonało się szybko.

No to teraz odpalam sobie takie zapytanie:

EXPLAIN ANALYZE
SELECT *
FROM logs1 -- logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only

I oto wynik:

--------- logs1 ---------
Limit  (cost=13.18..13.18 rows=1 width=12) (actual time=0.086..0.087 rows=0 loops=1)
  ->  Sort  (cost=13.18..13.18 rows=1 width=12) (actual time=0.083..0.084 rows=0 loops=1)
        Sort Key: create_time DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Only Scan using logs1_user_id_create_time_idx on logs1  (cost=0.57..13.17 rows=1 width=12) (actual time=0.074..0.074 rows=0 loops=1)
              Index Cond: (user_id = ANY ('{1001,1002}'::integer[]))
              Heap Fetches: 0
Planning Time: 0.223 ms
Execution Time: 0.124 ms

--------- logs2 ---------
Limit  (cost=0.57..1303.11 rows=10 width=12) (actual time=254657.997..254658.003 rows=0 loops=1)
  ->  Index Scan Backward using logs2_create_time_idx on logs2  (cost=0.57..5210160.65 rows=40000 width=12) (actual time=254657.992..254657.993 rows=0 loops=1)
        Filter: (user_id = ANY ('{1001,1002}'::integer[]))
        Rows Removed by Filter: 200000000
Planning Time: 0.247 ms
Execution Time: 254658.069 ms

W przypadku logs1 zapytanie wykonało się szybko, bo użyty został ten sam index, co w poprzednim zapytaniu.

W logs2 natomiast zapytanie wykonywało się 2 miliony razy dłużej (ponad 4 minuty), bo użyty został inny index - logs2_create_time_idx. Jeśli usunę sobie ten index logs2_create_time_idx, to postgres pójdzie po rozum do głowy i użyje logs2_user_id_create_time_idx, powodując, że zapytanie wykona się szybko:

Limit  (cost=9.18..9.18 rows=1 width=12) (actual time=0.143..0.146 rows=0 loops=1)
  ->  Sort  (cost=9.18..9.18 rows=1 width=12) (actual time=0.141..0.143 rows=0 loops=1)
        Sort Key: create_time DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Only Scan using logs1_user_id_create_time_idx on logs1  (cost=0.57..9.17 rows=1 width=12) (actual time=0.122..0.123 rows=0 loops=1)
              Index Cond: (user_id = ANY ('{1001,1002}'::integer[]))
              Heap Fetches: 0
Planning Time: 0.245 ms
Execution Time: 0.188 ms

Jestem w sytuacji, gdzie postgres wybiera mi zły index, przez co zapytanie wykonuje się nieporównywalnie dłużej, niż gdyby został wybrany index prawidłowy. Co mogę z tym zrobić? W sql serverze mógłbym zmusić silnik do skorzystania z konkretnego indexu, natomiast w pogresie z tego co wiem nie da się tego zrobić.

Dodam jeszcze, że postgres używa prawidłowego indexu, gdy operuję na mniejszej liczbie danych, np. nie 200 000 000, a 2 000 000.

0

nie związane z pytaniem - indeks logs1_user_id_idx jest zbędny bo jest dublowany przez logs1_user_id_create_time_idx

0

A to może nie problem w zapytaniu tylko w konieczności odczutu znacznie większej ilości danyc. A co jak dasz inne uzytkownika?

0

Widzisz nie doczytałem, że tam masz sortowanie z limitem - w takim przypadku indeks powinien być na polach create_time, user_id - kolejność pól w indeksie ma znaczenie.
Dodaj go i wrzuć tu wyniki

0

abrakadaber napisał(a):

Widzisz nie doczytałem, że tam masz sortowanie z limitem - w takim przypadku indeks powinien być na polach create_time, user_id - kolejność pól w indeksie ma znaczenie.
Dodaj go i wrzuć tu wyniki

Wykonałem:

CREATE INDEX logs1_create_time_user_id_idx ON ONLY logs1 USING btree (create_time, user_id);
CREATE INDEX logs2_create_time_user_id_idx ON ONLY logs2 USING btree (create_time, user_id);

analyze logs1;
analyze logs2;

A potem uruchomiłem na obu tabelach moje zapytanie:

EXPLAIN ANALYZE
SELECT *
FROM logs2 -- logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time  DESC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS only

I oto wyniki:

--------- logs1 ---------
Limit  (cost=9.18..9.18 rows=1 width=12) (actual time=0.093..0.095 rows=0 loops=1)
  ->  Sort  (cost=9.18..9.18 rows=1 width=12) (actual time=0.091..0.092 rows=0 loops=1)
        Sort Key: create_time DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Only Scan using logs1_user_id_create_time_idx on logs1  (cost=0.57..9.17 rows=1 width=12) (actual time=0.081..0.082 rows=0 loops=1)
              Index Cond: (user_id = ANY ('{1001,1002}'::integer[]))
              Heap Fetches: 0
Planning Time: 0.241 ms
Execution Time: 0.135 ms

--------- logs2 ---------
Limit  (cost=0.57..661.86 rows=10 width=12) (actual time=17540.318..17540.319 rows=0 loops=1)
  ->  Index Only Scan Backward using logs2_create_time_user_id_idx on logs2  (cost=0.57..4188139.65 rows=63333 width=12) (actual time=17540.315..17540.315 rows=0 loops=1)
        Filter: (user_id = ANY ('{1001,1002}'::integer[]))
        Rows Removed by Filter: 200000000
        Heap Fetches: 0
Planning Time: 0.363 ms
Execution Time: 17540.357 ms

W przypadku logs1 nadal używany jest index logs1_user_id_create_time_idx, natomiast logs2 używa zaproponowanego przez Ciebie - logs2_create_time_user_id_idx, który jest 14.5 razy szybszy od logs2_create_time_idx, ale nadal 130 000 razy wolniejszy od logs2_user_id_create_time_idx.

Dodam - może to ma jakieś znaczenie - że moje zapytanie nie zwraca wyników (nie ma użytkowników z id 1001, 1002)

4

W tych tabelach masz różny rozkład danych. Możliwe, że statystyki zebrane słabo to odzwierciedlają, w efekcie planner gorzej się spisuje. Dodaj więcej bucketów na histogram i zobacz co się zmieni:

ALTER TABLE logs2 ALTER COLUMN user_id SET STATISTICS 1500;
ALTER TABLE logs2 ALTER COLUMN create_time SET STATISTICS 1500;

ANALYZE logs2;

EXPLAIN ANALYZE
SELECT *
FROM logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time DESC
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;

0

yarel napisał(a):

W tych tabelach masz różny rozkład danych. Możliwe, że statystyki zebrane słabo to odzwierciedlają, w efekcie planner gorzej się spisuje. Dodaj więcej bucketów na histogram i zobacz co się zmieni:
ALTER TABLE logs2 ALTER COLUMN user_id SET STATISTICS 1500;
ALTER TABLE logs2 ALTER COLUMN create_time SET STATISTICS 1500;

ANALYZE logs2;

EXPLAIN ANALYZE
SELECT *
FROM logs2
WHERE user_id IN (1001, 1002)
ORDER BY create_time DESC
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;

Ooo, faktycznie, pomogło. Hmm to teraz się zastanawiam - przecież w dużych systemach ciągle musi być jakaś sytuacja, że gdzieś tabela się zapełnia, jest rożny rozkład danych i zapytania zaczynają zamulać. NIe jest to jakimś problemem?

2

generalnie duże systemy danych wymagają ciągłego nadzoru i dostosowywania. To nie projekt z półki, który instaluje na stacji roboczej MSSQL Express i ew. zmiany w bazie mogą się pojawić jedynie wraz z aktualizacją softu. W postgresie, kiedyś - nie wiem jak dzisiaj, normą było automatyczne odpalanie przeliczania statystyk w weekend.

1

dasek napisał(a):

...
Ooo, faktycznie, pomogło. Hmm to teraz się zastanawiam - przecież w dużych systemach ciągle musi być jakaś sytuacja, że gdzieś tabela się zapełnia, jest rożny rozkład danych i zapytania zaczynają zamulać. NIe jest to jakimś problemem?

Jest to problem ;-) Jak zrozumiesz dlaczego konkretny problem występuje, to wymyślasz rozwiązanie. Masz podstawowe strategie radzenia sobie:
a) robić "czegoś" mniej
b) robić "coś" szybciej

W przypadku tego zapytania na logs2 baza robiła dużo I/O (przez nieoptymalny plan zapytania) i po to by rozwiązać problem, mogłeś:
a) robić mniej I/O (przez wpłynięcie na postgresa by wybierał lepszy plan wykonania zapytania)
b) robić to I/O szybciej (np. przez dodanie RAMu, w którym dane by były cachowane, bądź umieszczenie tabeli na super szybkich dyskach/macierzy, czy organizację fizyczną tabeli - tak by optymalizator miał więcej możliwości realizacji zapytania - np. partycjonowanie czy columnar storage)

Poczytaj sobie jak działa optymalizator w postgres (np. https://momjian.us/main/writings/pgsql/optimizer.pdf), to może będziesz w stanie zrozumieć czy plan zapytania wybrany przez postgresa jest tym planem, który wg Ciebie powinien być wybrany.

Co zrobić, gdy postgres dobiera zły index

abrakadaber napisał(a):

yarel napisał(a):

dasek napisał(a):

Praca dla programistów

Forum dyskusyjne

Sprawy administracyjne

O nas

Skontaktuj się z nami