4programmers.net 4p Forum Bazy danych Dlaczego bezpośrednie wskazanie danych jest dużo szybsze

Dlaczego bezpośrednie wskazanie danych jest dużo szybsze

Dlaczego bezpośrednie wskazanie danych jest dużo szybsze
dasek
DA
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 27
0

te zapytanie:

Kopiuj
select id
from book b
where b.genre_id = 4

zwraca natychmiastowo:

12, 15, 22, 34, 77, 99, 127, 212, 515, 896

Dlaczego te zapytanie wykonuje się wolno:

Kopiuj
select *
from order o
where o.book_id in 
(
	select id
	from book b
	where b.genre_id = 4
)

Skoro te wykonuje się bardzo szybko?

Kopiuj
select *
from order o
where o.book_id in 
(
	12, 15, 22, 34, 77, 99, 127, 212, 515, 896
)

Zapytania różnią się fragmentem, który przecież zwraca dane natychmiastowo, więc z tego co rozumiem nie powinno być różnicy, czy "zahardkoduję" je, czy nie.

Udostępnij
Komentuj
woolfik
woolfik
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 1619
0

A co jest nie tak z joinem ? Jak wygladaja indeksy na tabelach przygotuj sqlfiddle to rzucę okiem

Udostępnij
Komentuj
anonimowy
AN
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 1011
0

Z jakiej wersji postgresa korzystasz? Też kiedyś miałem podobny problem, ogólnie w niektórych przypadkach postgres ssie, pokaż analizę to może coś ktoś podpowie więcej bo bez ciężko

Udostępnij
Komentuj
Dregorio
DR
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 1135
0

@dasek Zrob explain na tym zapytaniu i podeslij

Udostępnij
Komentuj
dasek
DA
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 27
0

@anonimowy @Dregorio
Zapytanie wolne:

Kopiuj
Hash Join  (cost=2.26..7408029.29 rows=29604258 width=146) (actual time=30748.790..41914.833 rows=19 loops=1)
  Hash Cond: (o.book_id = b.id)
  ->  Append  (cost=0.00..6901582.75 rows=183969317 width=146) (actual time=0.025..28696.397 rows=183874198 loops=1)
        ->  Seq Scan on order_2022_08 wl_1  (cost=0.00..1.33 rows=33 width=150) (actual time=0.023..0.026 rows=33 loops=1)
        ->  Seq Scan on order_2022_09 wl_2  (cost=0.00..11.00 rows=100 width=732) (actual time=0.007..0.007 rows=0 loops=1)
        ->  Seq Scan on order_2022_10 wl_3  (cost=0.00..77.39 rows=2539 width=129) (actual time=0.021..0.500 rows=2539 loops=1)
        ->  Seq Scan on order_2022_11 wl_4  (cost=0.00..11.00 rows=100 width=732) (actual time=0.002..0.002 rows=0 loops=1)
        ->  Seq Scan on order_2022_12 wl_5  (cost=0.00..11.00 rows=100 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        ->  Seq Scan on order_2023_01 wl_6  (cost=0.00..11.00 rows=100 width=732) (actual time=0.002..0.002 rows=0 loops=1)
        ->  Seq Scan on order_2023_02 wl_7  (cost=0.00..11.00 rows=100 width=732) (actual time=0.002..0.002 rows=0 loops=1)
        ->  Seq Scan on order_2023_03 wl_8  (cost=0.00..11.00 rows=100 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        ->  Seq Scan on order_2023_04 wl_9  (cost=0.00..11.00 rows=100 width=732) (actual time=0.002..0.002 rows=0 loops=1)
        ->  Seq Scan on order_2023_05 wl_10  (cost=0.00..28481.43 rows=911143 width=136) (actual time=0.012..128.545 rows=911143 loops=1)
        ->  Seq Scan on order_2023_06 wl_11  (cost=0.00..85697.79 rows=2741479 width=136) (actual time=0.015..370.163 rows=2741483 loops=1)
        ->  Seq Scan on order_2023_07 wl_12  (cost=0.00..95413.89 rows=3052689 width=136) (actual time=0.015..406.853 rows=3052669 loops=1)
        ->  Seq Scan on order_2023_08 wl_13  (cost=0.00..134309.17 rows=4434017 width=128) (actual time=0.017..584.706 rows=4435031 loops=1)
        ->  Seq Scan on order_default wl_14  (cost=0.00..5637678.17 rows=172826717 width=147) (actual time=0.020..17390.829 rows=172731300 loops=1)
  ->  Hash  (cost=2.09..2.09 rows=14 width=4) (actual time=92.141..92.143 rows=14 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on book b  (cost=0.00..2.09 rows=14 width=4) (actual time=92.125..92.131 rows=14 loops=1)
              Filter: (genre_id = 4)
              Rows Removed by Filter: 73
Planning Time: 1.643 ms
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.346 ms, Inlining 5.435 ms, Optimization 55.504 ms, Emission 30.951 ms, Total 94.234 ms
Execution Time: 41917.352 ms

Zapytanie szybkie:

Kopiuj
Append  (cost=0.00..506781.30 rows=144186 width=145) (actual time=308.350..308.519 rows=19 loops=1)
  ->  Seq Scan on order_2022_08 wl_1  (cost=0.00..1.91 rows=1 width=150) (actual time=307.914..307.914 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
        Rows Removed by Filter: 33
  ->  Seq Scan on order_2022_09 wl_2  (cost=0.00..12.75 rows=14 width=732) (actual time=0.010..0.010 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Index Scan using order_2022_10_book_id_order_s_idx on order_2022_10 wl_3  (cost=0.28..77.78 rows=14 width=129) (actual time=0.104..0.104 rows=0 loops=1)
        Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Seq Scan on order_2022_11 wl_4  (cost=0.00..12.75 rows=14 width=732) (actual time=0.002..0.002 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Seq Scan on order_2022_12 wl_5  (cost=0.00..12.75 rows=14 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Seq Scan on order_2023_01 wl_6  (cost=0.00..12.75 rows=14 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Seq Scan on order_2023_02 wl_7  (cost=0.00..12.75 rows=14 width=732) (actual time=0.002..0.003 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Seq Scan on order_2023_03 wl_8  (cost=0.00..12.75 rows=14 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Seq Scan on order_2023_04 wl_9  (cost=0.00..12.75 rows=14 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Index Scan using order_2023_05_book_id_order_s_idx on order_2023_05 wl_10  (cost=0.42..3404.15 rows=1323 width=136) (actual time=0.059..0.060 rows=0 loops=1)
        Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Index Scan using order_2023_06_book_id_order_s_idx on order_2023_06 wl_11  (cost=0.43..6679.33 rows=2399 width=136) (actual time=0.059..0.060 rows=0 loops=1)
        Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Index Scan using order_2023_07_book_id_order_s_idx on order_2023_07 wl_12  (cost=0.43..21660.29 rows=8141 width=136) (actual time=0.055..0.056 rows=0 loops=1)
        Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Bitmap Heap Scan on order_2023_08 wl_13  (cost=317.07..32044.41 rows=11233 width=128) (actual time=0.054..0.054 rows=0 loops=1)
        Recheck Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
        ->  Bitmap Index Scan on order_2023_08_book_id_order_s_idx  (cost=0.00..314.26 rows=11233 width=0) (actual time=0.051..0.052 rows=0 loops=1)
              Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
  ->  Index Scan using order_default_book_id_order_s_idx on order_default wl_14  (cost=0.57..442103.26 rows=120977 width=147) (actual time=0.068..0.230 rows=19 loops=1)
        Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
Planning Time: 1.260 ms
JIT:
  Functions: 28
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 6.408 ms, Inlining 3.418 ms, Optimization 188.866 ms, Emission 115.242 ms, Total 313.934 ms
Execution Time: 315.176 ms
Udostępnij
Komentuj
Ryan_1975
Ryan_1975
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 38
0

Druga kwerenda jest wolniejsza, ponieważ zawiera podzapytanie, które musi zostać wykonane przed porównaniem wyników z tabelą order.
Podzapytanie generuje dynamiczną listę wartości, co jest mniej efektywne niż użycie statycznej listy. Porównywanie każdego book_id z dynamicznie wygenerowaną listą jest bardziej czasochłonne. Trzecia kwerenda jest szybsza, ponieważ wykorzystuje statyczną listę wartości, co jest prostsze do przetworzenia przez silnik bazy danych. Dodatkowo, w przypadku trzeciej kwerendy, baza danych może lepiej zoptymalizować zapytanie, eliminując potrzebę wielokrotnego porównywania wyników.

Spróbuj jeszcze:
select o.*
from order o
join book b on o.book_id = b.id
where b.genre_id = 4

albo dodaj indeks na kolumnę book_id w order i genre_id w book.

Udostępnij
Komentuj
dasek
DA
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 27
0

Opisałem problem również na so i tam uzyskałem taką odpowiedź (od użytkownika, który jest kontrybutorem postgresa):

In the first case, PostgreSQL doesn't have this information when the query is planned, so it uses an estimate. That estimate seems to be off, do that PostgreSQL chooses a bad query plan.
In the second case, PostgreSQL knows what the values will be when it plans the query, so it can get a good estimate for the number of result rows.

Od innego użytkownika (ten prowadzi bloga o postgresie) otrzymałem taką odpowiedź, gdzie każe mi modyfikować ustawienia postgresa:
screenshot-20240620183755.png

Udostępnij
Komentuj
abrakadaber
abrakadaber
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 6610
1
Kopiuj
select *
from order o
join book b on b.id = o.book_id
where b.genre_id = 4

i indeks powinien być na book(gender_id, id) i order(book_id) i nie ma bata, musis działać

do poczytania https://www.cybertec-postgresql.com/en/subqueries-and-performance-in-postgresql/ i ewentualnie możesz zamienić in na exists ale w twoim przypadku in albo exists zamiast join to wg mnie błąd

Udostępnij
Komentuj
yarel
YA
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 2399
1

Silnik zanim wykona zapytanie, to przygotowuje plan wykonania. Dla tych podanych zapytań dysponuje zupełnie innymi danymi wejściowymi do przegotowania planu.

  • "wolne" - wie, że podzapytanie zwróci ~14 wierszy (ale nie wiadomo jakie to będą book_id i w których partycjach będą leżeć - może więc założyć, że we wszystkich)
  • "szybkie" - wie, że ma 10 wartości i wie jakie to są konkretne wartości

Z tego co widzę, to tabela order jest partycjonowana po zakresie czasowym (tak sugeruje nazewnictwo). Natomiast nie wiem ile faktycznie jest wierszy w każdej partycji i jakie są indeksy.

Możesz wyłączyć tymczasowo (na potrzeby testy) hash joina :

Kopiuj
SET enable_hashjoin = off;

A następnie sprawdzić czy plan "wolnego" zapytania uległ zmianie.

Dla każdej partycji warto by się przyjrzeć rozkładowi danych "book_id" bo jak np. dostaniesz z podzapytania book_id=12, to ile % wierszy z partycji X to właśnie book_id=12? (jak 90% to lepiej zrobić skan całej partycji niż lecieć do indeksu, a później jeszcze raz do tabeli, jak to np. 0.01% - to lepiej lecieć przez indeks) i na podstawie tego wyciągać wnioski dlaczego planer mogł się zachować tak a nie inaczej.

Z "szybkiego" planu wygląda, że: informacje o konkretnych ID pozwalają mu podjąć inne decyzje per partycja.

Kopiuj
->  Seq Scan on order_2023_04 wl_9  (cost=0.00..12.75 rows=14 width=732) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))

->  Index Scan using order_2023_05_book_id_order_s_idx on order_2023_05 wl_10  (cost=0.42..3404.15 rows=1323 width=136) (actual time=0.059..0.060 rows=0 loops=1)
        Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))

->  Bitmap Heap Scan on order_2023_08 wl_13  (cost=317.07..32044.41 rows=11233 width=128) (actual time=0.054..0.054 rows=0 loops=1)
        Recheck Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))
        ->  Bitmap Index Scan on order_2023_08_book_id_order_s_idx  (cost=0.00..314.26 rows=11233 width=0) (actual time=0.051..0.052 rows=0 loops=1)
              Index Cond: (book_id = ANY ('{12, 15, 22, 34, 77, 99, 127, 212, 515, 896}'::integer[]))

Tak poza tym, to te partycje wyglądają jakby ktoś zapomniał po sierpniu 2023 tworzyć nowe:
wl13 - ~4.4M rows
wl14 - ~172M rows <-- to może być problemem dla "wolnego" zapytania, bo postgres nie wie ile z tych estymowanych 14 (zwracanych przed podzapytanie), wpadnie w tę partycję.

Kopiuj
  ->  Seq Scan on order_2023_08 wl_13  (cost=0.00..134309.17 rows=4434017 width=128) (actual time=0.017..584.706 rows=4435031 loops=1)
  ->  Seq Scan on order_default wl_14  (cost=0.00..5637678.17 rows=172826717 width=147) (actual time=0.020..17390.829 rows=172731300 loops=1)
Udostępnij
Komentuj
Marcin.Miga
Marcin.Miga
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 2794
1
abrakadaber napisał(a):
Kopiuj
select *
from order o
join book b on b.id = o.book_id
where b.genre_id = 4

i indeks powinien być na book(gender_id, id) i order(book_id) i nie ma bata, musis działać

do poczytania https://www.cybertec-postgresql.com/en/subqueries-and-performance-in-postgresql/ i ewentualnie możesz zamienić in na exists ale w twoim przypadku in albo exists zamiast join to wg mnie błąd

jw. ale jeszcze możesz spróbować tak:

Kopiuj
select *
from order o
join book b on b.id = o.book_id and b.genre_id = 4
Udostępnij
Komentuj
loza_prowizoryczna
loza_prowizoryczna
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 1628
0
Ryan_1975 napisał(a):

Druga kwerenda jest wolniejsza, ponieważ zawiera podzapytanie, które musi zostać wykonane przed porównaniem wyników z tabelą order.

Zawsze mówili że Postgres to taki Oracle Database dla ubogich i studentów (tak samo jak MyISAM to sqlite dla stronek w PHP) ale tego że ma tak zjechany kompilator sql że nie potrafi zrobić prostej optymalizacji to żem się nie spodziewał.

Udostępnij
Komentuj
grzegorz_so
GS
  • Rejestracja: dni
  • Ostatnio: dni
1

@loza_prowizoryczna:

"Zawsze mówili że Postgres to taki Oracle Database dla ubogich i studentów ".

Nie każdy, kto wybiera darmowe rozwiązanie, musi być ubogim studentem ..

Udostępnij
Komentuj
slsy
SL
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 1134
2

Wygląda na to, że planner po prostu zgłupiał. Najprościej będzie użyć WITH, który domyślnie jest materializowany (można to teraż też wyłączyć https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION ) i taka konstrukcja powinna dać ci to samo co dwa zapytania jedno po drugim

Możliwe, że dobre stunowanie instancji (https://pgtune.leopard.in.ua/ ) oraz odpalanie VACUUM ANALYZE może pomóc. Nowe wersje postgresa dużo lepiej używają statystyk odnośnie rozkładu danych w tabelach, więc pewnie podbicie wersji może pomóc, jeśli masz jakąś starszą

Udostępnij
Komentuj
dasek
DA
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 27
0

@slsy, @Marcin.Miga no właśnie WITH CTE , czy INNER JOINR nie pomaga:
Wersja 1, czas wykonania 1 sec

Kopiuj
select *
from order o 
where o.book_id in 
(
	12, 15, 22, 34, 77, 99, 127, 212, 515, 896
)

Wersja 2, czas wykonania 25 sec

Kopiuj
select *
from order o 
where o.book_id in 
(
	select id
	from book b 
	where b.genre_id = 4
)

Wersja 3, czas wykonania 25 sec

Kopiuj
with cte as 
(
	select id
	from book b 
	where b.genre_id = 4
)
select *
from order o 
where o.book_id in (select id from cte)

Wersja 4, czas wykonania 25 sec

Kopiuj
with cte as 
(
	select id
	from book b 
	where b.genre_id = 4
)
select o.*
from order o 
inner join cte c on c.id = o.book_id

Wersja 5, czas wykonania 25 sec

Kopiuj
select o.*
from order o 
inner join book b on b.id = o.book_id
where b.genre_id = 4

Oczywiście nadal fragment:

Kopiuj
select id
from book b 
where b.genre_id = 4

zwraca natychmiastowo: 12, 15, 22, 34, 77, 99, 127, 212, 515, 896

explain analyze też odpalałem (zarówno na order, jak i book), ale nic to nie dało. Postgres tam jest w wersji 13.

Udostępnij
Komentuj
abrakadaber
abrakadaber
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 6610
0

a jakie masz indeksy założone na obu tabelach?

Udostępnij
Komentuj
obscurity
obscurity
  • Rejestracja: dni
  • Ostatnio: dni
0

Bo postgres jest najwidoczniej upośledzony. Kiedyś podobny problem rozwiązywaliśmy przez materialized view natomiast nie wiem czy pomoże tutaj

Udostępnij
Komentuj
Marcin.Miga
Marcin.Miga
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 2794
1

Daj DDL tabel "order" i "book"
Oraz wyniki zapytań:

Kopiuj
SELECT count(*) FROM order

oraz

Kopiuj
SELECT genre_id, count(*) FROM book GROUP BY genre_id
Udostępnij
Komentuj
Fac
FA
  • Rejestracja: dni
  • Ostatnio: dni
  • Postów: 225
2

Widzę, że i w Postgresie czasem trzeba szyć, żeby proste zapytania udrożnić...

Ja z Postgresem nie mam za wiele wspólnych wspomnień, ale spróbowałbym jeszcze taki brzydki chwyt:

Kopiuj
select o.*
from book b
left join order o on b.id = o.book_id
where b.genre_id = 4
  and o.book_id is not null

Tak swoją drogą, to kiedyś czytałem, że nie warto nazywać tabel słowami kluczowymi, a jednym z tych słów jest ORDER - służy do określania przestrzeni zamawiania jedzenia w znanej jadłodajni spod znaku złotych łuków.

Udostępnij
Komentuj

Zarejestruj się i dołącz do największej społeczności programistów w Polsce.

Otrzymaj wsparcie, dziel się wiedzą i rozwijaj swoje umiejętności z najlepszymi.

Utwórz konto