Co zrobić, gdy postgres dobiera zły index

Co zrobić, gdy postgres dobiera zły index
dasek
DA
dasek
DA
  • Rejestracja:prawie 3 lata
  • Ostatnio:2 dni
  • Postów:23
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.

  1. Tworzę 2 identyczne tabele
Kopiuj
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()
);
  1. Do pierwszej tabeli wrzucam 200 000 000 wpisów, po 2 000 000 na każdego ze 100 użytkowników:
Kopiuj
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));
  1. 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:
Kopiuj
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));
  1. Ttworzę indexy:
Kopiuj
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);
  1. Wykonuję analyze:
Kopiuj
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:

Kopiuj
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:

Kopiuj
--------- 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:

Kopiuj
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:

Kopiuj
--------- 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:

Kopiuj
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.

edytowany 3x, ostatnio: dasek
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
abrakadaber
abrakadaber
abrakadaber
abrakadaber
  • Rejestracja:ponad 12 lat
  • Ostatnio:7 miesięcy
  • Postów:6610
0

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

Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Adin
AD
Adin
AD
  • Rejestracja:ponad rok
  • Ostatnio:około 5 godzin
  • Postów:322
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?

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
abrakadaber
abrakadaber
abrakadaber
abrakadaber
  • Rejestracja:ponad 12 lat
  • Ostatnio:7 miesięcy
  • Postów:6610
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

Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
edytowany 2x, ostatnio: abrakadaber
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
dasek
DA
dasek
DA
  • Rejestracja:prawie 3 lata
  • Ostatnio:2 dni
  • Postów:23
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:

Kopiuj
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:

Kopiuj
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:

Kopiuj
--------- 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)

edytowany 4x, ostatnio: dasek
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
yarel
YA
yarel
YA
  • Rejestracja:prawie 10 lat
  • Ostatnio:około 7 godzin
  • Postów:2368
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:

Kopiuj
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;
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
dasek
DA
dasek
DA
  • Rejestracja:prawie 3 lata
  • Ostatnio:2 dni
  • Postów:23
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:

Kopiuj
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?

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
abrakadaber
abrakadaber
abrakadaber
abrakadaber
  • Rejestracja:ponad 12 lat
  • Ostatnio:7 miesięcy
  • Postów:6610
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.

Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
yarel
YA
yarel
YA
  • Rejestracja:prawie 10 lat
  • Ostatnio:około 7 godzin
  • Postów:2368
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.

Udostępnij
Kopiuj link do postu Kopiuj link do postu jako Markdown
Komentuj
Treść
Podgląd
Kliknij, aby dodać treść...
Kliknij, aby dodać załącznik lub wklej ze schowka.
Instrukcja obsługi Markdown
Pomoc 1.18.8

Typografia

Edytor obsługuje składnie Markdown, w której pojedynczy akcent *kursywa* oraz _kursywa_ to pochylenie. Z kolei podwójny akcent **pogrubienie** oraz __pogrubienie__ to pogrubienie. Dodanie znaczników ~~strike~~ to przekreślenie.

Możesz dodać formatowanie komendami , , oraz .

Ponieważ dekoracja podkreślenia jest przeznaczona na linki, markdown nie zawiera specjalnej składni dla podkreślenia. Dlatego by dodać podkreślenie, użyj <u>underline</u>.

Komendy formatujące reagują na skróty klawiszowe: Ctrl+B, Ctrl+I, Ctrl+U oraz Ctrl+S.

Linki

By dodać link w edytorze użyj komendy lub użyj składni [title](link). URL umieszczony w linku lub nawet URL umieszczony bezpośrednio w tekście będzie aktywny i klikalny.

Jeżeli chcesz, możesz samodzielnie dodać link: <a href="link">title</a>.

Wewnętrzne odnośniki

Możesz umieścić odnośnik do wewnętrznej podstrony, używając następującej składni: [[Delphi/Kompendium]] lub [[Delphi/Kompendium|kliknij, aby przejść do kompendium]]. Odnośniki mogą prowadzić do Forum 4programmers.net lub np. do Kompendium.

Wspomnienia użytkowników

By wspomnieć użytkownika forum, wpisz w formularzu znak @. Zobaczysz okienko samouzupełniające nazwy użytkowników. Samouzupełnienie dobierze odpowiedni format wspomnienia, zależnie od tego czy w nazwie użytkownika znajduje się spacja.

Znaczniki HTML

Dozwolone jest używanie niektórych znaczników HTML: <a>, <b>, <i>, <kbd>, <del>, <strong>, <dfn>, <pre>, <blockquote>, <hr/>, <sub>, <sup> oraz <img/>.

Skróty klawiszowe

Dodaj kombinację klawiszy komendą notacji klawiszy lub skrótem klawiszowym Alt+K.

Reprezentuj kombinacje klawiszowe używając taga <kbd>. Oddziel od siebie klawisze znakiem plus, np <kbd>Alt+Tab</kbd>.

Indeks górny oraz dolny

Przykład: wpisując H<sub>2</sub>O i m<sup>2</sup> otrzymasz: H2O i m2.

Składnia Tex

By precyzyjnie wyrazić działanie matematyczne, użyj składni Tex.

<tex>arcctg(x) = argtan(\frac{1}{x}) = arcsin(\frac{1}{\sqrt{1+x^2}})</tex>

Kod źródłowy

Krótkie fragmenty kodu

Wszelkie jednolinijkowe instrukcje języka programowania powinny być zawarte pomiędzy obróconymi apostrofami: `kod instrukcji` lub ``console.log(`string`);``.

Kod wielolinijkowy

Dodaj fragment kodu komendą . Fragmenty kodu zajmujące całą lub więcej linijek powinny być umieszczone w wielolinijkowym fragmencie kodu. Znaczniki ``` lub ~~~ umożliwiają kolorowanie różnych języków programowania. Możemy nadać nazwę języka programowania używając auto-uzupełnienia, kod został pokolorowany używając konkretnych ustawień kolorowania składni:

```javascript
document.write('Hello World');
```

Możesz zaznaczyć również już wklejony kod w edytorze, i użyć komendy  by zamienić go w kod. Użyj kombinacji Ctrl+`, by dodać fragment kodu bez oznaczników języka.

Tabelki

Dodaj przykładową tabelkę używając komendy . Przykładowa tabelka składa się z dwóch kolumn, nagłówka i jednego wiersza.

Wygeneruj tabelkę na podstawie szablonu. Oddziel komórki separatorem ; lub |, a następnie zaznacz szablonu.

nazwisko;dziedzina;odkrycie
Pitagoras;mathematics;Pythagorean Theorem
Albert Einstein;physics;General Relativity
Marie Curie, Pierre Curie;chemistry;Radium, Polonium

Użyj komendy by zamienić zaznaczony szablon na tabelkę Markdown.

Lista uporządkowana i nieuporządkowana

Możliwe jest tworzenie listy numerowanych oraz wypunktowanych. Wystarczy, że pierwszym znakiem linii będzie * lub - dla listy nieuporządkowanej oraz 1. dla listy uporządkowanej.

Użyj komendy by dodać listę uporządkowaną.

1. Lista numerowana
2. Lista numerowana

Użyj komendy by dodać listę nieuporządkowaną.

* Lista wypunktowana
* Lista wypunktowana
** Lista wypunktowana (drugi poziom)

Składnia Markdown

Edytor obsługuje składnię Markdown, która składa się ze znaków specjalnych. Dostępne komendy, jak formatowanie , dodanie tabelki lub fragmentu kodu są w pewnym sensie świadome otaczającej jej składni, i postarają się unikać uszkodzenia jej.

Dla przykładu, używając tylko dostępnych komend, nie możemy dodać formatowania pogrubienia do kodu wielolinijkowego, albo dodać listy do tabelki - mogłoby to doprowadzić do uszkodzenia składni.

W pewnych odosobnionych przypadkach brak nowej linii przed elementami markdown również mógłby uszkodzić składnie, dlatego edytor dodaje brakujące nowe linie. Dla przykładu, dodanie formatowania pochylenia zaraz po tabelce, mogłoby zostać błędne zinterpretowane, więc edytor doda oddzielającą nową linię pomiędzy tabelką, a pochyleniem.

Skróty klawiszowe

Skróty formatujące, kiedy w edytorze znajduje się pojedynczy kursor, wstawiają sformatowany tekst przykładowy. Jeśli w edytorze znajduje się zaznaczenie (słowo, linijka, paragraf), wtedy zaznaczenie zostaje sformatowane.

  • Ctrl+B - dodaj pogrubienie lub pogrub zaznaczenie
  • Ctrl+I - dodaj pochylenie lub pochyl zaznaczenie
  • Ctrl+U - dodaj podkreślenie lub podkreśl zaznaczenie
  • Ctrl+S - dodaj przekreślenie lub przekreśl zaznaczenie

Notacja Klawiszy

  • Alt+K - dodaj notację klawiszy

Fragment kodu bez oznacznika

  • Alt+C - dodaj pusty fragment kodu

Skróty operujące na kodzie i linijkach:

  • Alt+L - zaznaczenie całej linii
  • Alt+, Alt+ - przeniesienie linijki w której znajduje się kursor w górę/dół.
  • Tab/⌘+] - dodaj wcięcie (wcięcie w prawo)
  • Shit+Tab/⌘+[ - usunięcie wcięcia (wycięcie w lewo)

Dodawanie postów:

  • Ctrl+Enter - dodaj post
  • ⌘+Enter - dodaj post (MacOS)