Dlaczego bezpośrednie wskazanie danych jest dużo szybsze

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.

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

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

@dasek Zrob explain na tym zapytaniu i podeslij

@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

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.

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:

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

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)

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

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

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

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ą

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

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

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

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

SELECT count(*) FROM order

oraz

SELECT genre_id, count(*) FROM book GROUP BY genre_id

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.