Dlaczego bezpośrednie wskazanie danych jest dużo szybsze

DA
  • Rejestracja:ponad 2 lata
  • Ostatnio:22 dni
  • Postów:23
0

te zapytanie:

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:

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?

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.

edytowany 2x, ostatnio: cerrato
woolfik
  • Rejestracja:ponad 17 lat
  • Ostatnio:38 minut
  • Postów:1577
0

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

DA
JOIN też działa wolno. Index jest w tabeli book na genre_id, a w order na book_id. Zresztą - mój przykład pokazuje, że to nie kwestia błędnych indexów.
woolfik
no jak zrobisz przykład na sqlfiddle to spróuję pomóc bez dostępu do bazy ciężko powiedzieć
AN
  • Rejestracja:ponad 10 lat
  • Ostatnio:4 minuty
  • Postów:957
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


Zdalna praca dla Senior Python Developerów --> PW
DR
  • Rejestracja:ponad 11 lat
  • Ostatnio:około 7 godzin
  • Postów:1122
0

@dasek Zrob explain na tym zapytaniu i podeslij

DA
  • Rejestracja:ponad 2 lata
  • Ostatnio:22 dni
  • Postów:23
0

@anonimowy @Dregorio
Zapytanie wolne:

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:

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
Ryan_1975
  • Rejestracja:około 2 lata
  • Ostatnio:około 19 godzin
  • Postów:23
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.

edytowany 1x, ostatnio: Ryan_1975
DA
No ale te podzapytanie wykonuje się bardzo szybko. Wychodzi na to, że szybciej osiągnę wynik robiąc 2 zapytania zamiast 1.
Marius.Maximus
@dasek wiele razy x szybko = wolno ot i cala tajemnica ;)
DA
Niestety z indeksami nie pomaga @Ryan_1975. Zerknij sobie też na moją odpowiedź poniżej
DA
  • Rejestracja:ponad 2 lata
  • Ostatnio:22 dni
  • Postów:23
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

edytowany 2x, ostatnio: dasek
abrakadaber
abrakadaber
  • Rejestracja:ponad 12 lat
  • Ostatnio:5 miesięcy
  • Postów:6610
1
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


Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
edytowany 1x, ostatnio: abrakadaber
YA
  • Rejestracja:ponad 9 lat
  • Ostatnio:około 8 godzin
  • Postów:2344
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 :

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.

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

  ->  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)
Marcin.Miga
  • Rejestracja:ponad 16 lat
  • Ostatnio:dzień
  • Postów:2791
1
abrakadaber napisał(a):
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:

select *
from order o
join book b on b.id = o.book_id and b.genre_id = 4
loza_prowizoryczna
  • Rejestracja:około 2 lata
  • Ostatnio:2 dni
  • Postów:1341
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ł.


Przetrzyma wszystko
KamilAdam
Problem (szczęście?) jest takie iż oracle coraz droższy, Ogłąszamy tydzień podwyżek cen licencji na oracle. Populacja programistów oracle zmniejsza się
loza_prowizoryczna
I co z tego? Sun zdechł, VMWare zdechł, RedHat zdechł. Open source i samodzielność to antyteza. A Oracle ciągle nabywa i się rozwija mimo mentalności sprzedawcy z komisu :D
GS
  • Rejestracja:ponad 14 lat
  • Ostatnio:2 minuty
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 ..

loza_prowizoryczna
Niby tak ale nie znam nikogo kogo by zwolnili za wybór IBMa...
SL
  • Rejestracja:prawie 7 lat
  • Ostatnio:około godziny
  • Postów:820
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ą

edytowany 1x, ostatnio: slsy
DA
  • Rejestracja:ponad 2 lata
  • Ostatnio:22 dni
  • Postów:23
0

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

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

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

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

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

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

Oczywiście nadal fragment:

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.

edytowany 1x, ostatnio: cerrato
Marcin.Miga
Mojej wersji nie podałeś. Ani wersji z EXISTS. Z własnego doświadczenia wiem, że przy dużej ilości danych PostgreSQL przestaje korzystać z indeksów. A czasy, które podałeś (25 sec) sugerują, że tabele są duże - może to ten przypadek
abrakadaber
abrakadaber
  • Rejestracja:ponad 12 lat
  • Ostatnio:5 miesięcy
  • Postów:6610
0

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


Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.
obscurity
  • Rejestracja:prawie 6 lat
  • Ostatnio:około godziny
0

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


"A car won't take your job, another horse driving a car will." - Horse influencer, 1910
Marcin.Miga
  • Rejestracja:ponad 16 lat
  • Ostatnio:dzień
  • Postów:2791
1

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

SELECT count(*) FROM order

oraz

SELECT genre_id, count(*) FROM book GROUP BY genre_id
FA
  • Rejestracja:ponad 4 lata
  • Ostatnio:dzień
  • Postów:174
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:

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.


Chciałbym kiedyś wiedzieć tyle, by spełniać wymagania na moim obecnym stanowisku :)
edytowany 1x, ostatnio: Fac

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.