Re: Simple JOIN on heavy table not using expected index

2024-02-09 Thread kimaidou
Tom, thanks a lot for your suggestion. Indeed, setting random_page_cost to 2 instead of 4 improves this query a lot ! See the new plan : https://explain.dalibo.com/plan/h924389529e11244 30 seconds VS 17 minutes before Cheers Michaël Le vendredi 9 février 2024, Tom Lane a écrit : > kimaidou w

Re: Simple JOIN on heavy table not using expected index

2024-02-09 Thread Tom Lane
kimaidou writes: > It seems PostgreSQL does not use the index parcelles_dep_idx on "dep" (text > field), even if the corresponding number of lines for this WHERE clause is > a smal subset of the entire data: > approx 6M against 80M in total 6M out of 80M rows is not a "small subset". Typically I

Re: Simple JOIN on heavy table not using expected index

2024-02-09 Thread kimaidou
The query plan is visible here : https://explain.dalibo.com/plan/50a719h92hde6950 Regards Le vendredi 9 février 2024, Burçin Yazıcı a écrit : > can you share result for: > > *explain analyze* SELECT p.id_parcelle > FROM private.parcelles p > WHERE (p.dep IN ( '08', '10', '54', '57', '67', '68')

Re: Simple JOIN on heavy table not using expected index

2024-02-09 Thread Burçin Yazıcı
can you share result for: *explain analyze* SELECT p.id_parcelle FROM private.parcelles p WHERE (p.dep IN ( '08', '10', '54', '57', '67', '68')) ; On Fri, 9 Feb 2024 at 17:14, kimaidou wrote: > Hi all, > > I have performance issue for a pretty simple request in a PostgreSQL > server 14.10 > > *

Simple JOIN on heavy table not using expected index

2024-02-09 Thread kimaidou
Hi all, I have performance issue for a pretty simple request in a PostgreSQL server 14.10 * Request SELECT p.id_parcelle FROM private.parcelles p WHERE (p.dep IN ( '08', '10', '54', '57', '67', '68')) ; * Table definition (extract) Table « private.parcelles »