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 <t...@sss.pgh.pa.us> a écrit : > kimaidou <kimai...@gmail.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 >
