Re: Simple JOIN on heavy table not using expected index

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> kimaidou <kimaidou(at)gmail(dot)com> 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'd expect
> the planner to use an index-based scan for up to 1 or 2 percent of
> the table. Beyond that, you're going to be touching most pages
> of the table anyway.
>
> You can try reducing random_page_cost to favor indexscans, but
> you might not find that the query gets any faster.
>
> regards, tom lane
>