Re: increasing effective_cache_size slows down join queries by a factor of 4000x

> Question: are there other ways to give PostgreSQL a hint

What you your pg version?

Maybe with pg_hint_plan extension ( https://github.com/ossc-db/pg_hint_plan
)
"pg_hint_plan makes it possible to tweak PostgreSQL execution plans using
so-called "hints" in SQL comments, like /*+ SeqScan(a) */."

regards,
Imre

Artyom Shaposhnikov <artyom(at)gmail(dot)com> ezt írta (időpont: 2022. febr. 2.,
Sze, 20:05):

> I recently moved a postgres DB to a more powerful server with 1TB of
> RAM instead of 64GB before. To my surprise after running the tuning on
> the new server, the performance of joins deteriorated by 4000x
> compared to the old server. I carefully examined all of the changes
> and found the culprit:
>
> if I use the effective_cache_size > 25GB, the query plan used is this:
>
> Limit (cost=124.12..590.33 rows=1000 width=205) (actual
> time=42326.662..42336.212 rows=1000 loops=1)
> -> Nested Loop (cost=124.12..31909018.46 rows=68443040 width=205)
> (actual time=42326.660..42336.127 rows=1000 loops=1)
> -> Merge Join (cost=124.12..30710356.97 rows=68443040 width=169)
> (actual time=42326.613..42332.764 rows=1000 loops=1)
> Merge Cond: (d.id = dc.data_id)
> -> Nested Loop (cost=1.00..31036282.72 rows=58785023 width=165)
> (actual time=0.042..5.533 rows=854 loops=1)
> -> Index Scan using data_pkey on data t (cost=0.57..4787030.00
> rows=58785023 width=131) (actual time=0.023..0.526 rows=854 loops=1)
> Index Cond: (id > 205284974)
> -> Index Scan using user_pkey on data_user u (cost=0.43..0.45 rows=1
> width=42) (actual time=0.005..0.005 rows=1 loops=854)
> Index Cond: (id = d.user_id)
> -> Index Only Scan using data_class_pkey on data_class ta
> (cost=0.57..4935483.78 rows=216964862 width=8) (actual
> time=0.018..35022.908 rows=151321889 loops=1)
> Heap Fetches: 151321889
> -> Index Scan using class_id_index on class a (cost=0.00..0.02 rows=1
> width=44) (actual time=0.003..0.003 rows=1 loops=1000)
> Index Cond: (id = dc.class_id)
> Planning Time: 4.114 ms
> Execution Time: 42336.397 ms
>
> and it is 4000x slower than the query plan used with the lower
> effective_cache_size that uses indexes instead of the merge joins:
>
> Limit (cost=1.57..4832.30 rows=1000 width=205) (actual
> time=0.081..10.457 rows=1000 loops=1)
> -> Nested Loop (cost=1.57..330629805.46 rows=68443040 width=205)
> (actual time=0.080..10.378 rows=1000 loops=1)
> -> Nested Loop (cost=1.57..267793481.39 rows=68443040 width=169)
> (actual time=0.065..7.496 rows=1000 loops=1)
> -> Nested Loop (cost=1.00..100917823.18 rows=58785023 width=165)
> (actual time=0.040..5.424 rows=854 loops=1)
> -> Index Scan using data_pkey on data t (cost=0.57..21427806.53
> rows=58785023 width=131) (actual time=0.024..0.482 rows=854 loops=1)
> Index Cond: (id > 205284974)
> -> Index Scan using user_pkey on data_user u (cost=0.43..1.35 rows=1
> width=42) (actual time=0.005..0.005 rows=1 loops=854)
> Index Cond: (id = d.user_id)
> -> Index Only Scan using data_class_pkey on data_class ta
> (cost=0.57..2.80 rows=4 width=8) (actual time=0.002..0.002 rows=1
> loops=854)
> Index Cond: (data_id = d.id)
> Heap Fetches: 1000
> -> Index Scan using class_id_index on class a (cost=0.00..0.92 rows=1
> width=44) (actual time=0.002..0.002 rows=1 loops=1000)
> Index Cond: (id = dc.class_id)
> Planning Time: 5.074 ms
> Execution Time: 10.614 ms
>
> query:
>
> explain analyze select d.time as time,d.id as id, u.username as
> username, a.query_symbol as query_symbol from data as d, data_user as
> u, class as a, data_class as dc
> where dc.class_id = a.id and dc.data_id = d.id and d.user_id = u.id
> and d.id > 205284974
> order by d.id
> limit 1000;
>
> I found a way to fix it by creating a distinct statistics on the
> largest join table:
>
> create statistics stat_data_class (ndistinct) on data_id, class_id
> from data_class;
> analyze data_class;
>
> Question: are there other ways to give PostgreSQL a hint to use the
> indexes instead of the merge join plan without artificially lowering
> the memory available in the parameter effective_cache_size or creating
> the additional statistics on the table?
>
> Thank you,
>
> -Art
>
>
>