"Olivier Poquet" <opoquet@plumdev.com> writes: > Looking at it in more detail, I found that the planner is assuming that I'll get millions of rows back even when I do a simple query that does an index scan on my partial index:
We don't look at partial-index predicates when trying to estimate the selectivity of a WHERE clause. It's not clear to me whether that'd be a useful thing to do, or whether it could be shoehorned into the system easily. (One big problem is that while the index size could provide an upper bound, it's not apparent how to combine that knowledge with selectivities of unrelated conditions. Also, it's riskier to extrapolate a current rowcount estimate from stale relpages/reltuples data for an index than it is for a table, because the index is less likely to scale up linearly.)
regards, tom lane
Aren't there custom stats created for functional indexes? Would it be feasible to create those for partial indexes as well, maybe only optionally? I assume there may be giant gaps with that notion.
From:
"Ehrenreich, Sigrid" Date: Subject:
RE: Postgres Optimizer ignores information about foreign key relationship, severly misestimating number of returned rows in join