From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Olivier Poquet <opoquet(at)plumdev(dot)com>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: query plan using partial index expects a much larger number of rows than is possible |
Date: | 2020-10-29 15:08:11 |
Message-ID: | CAHOFxGqeGh_u4hp2+H9-WFFPgjdVRtyAz5MiXZoiV5QORQiq4g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Oct 28, 2020 at 5:30 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Olivier Poquet" <opoquet(at)plumdev(dot)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 | Date | Subject | |
---|---|---|---|
Next Message | Philip Semanchuk | 2020-10-29 15:25:48 | Re: Understanding bad estimate (related to FKs?) |
Previous Message | Ehrenreich, Sigrid | 2020-10-29 12:12:09 | RE: Postgres Optimizer ignores information about foreign key relationship, severly misestimating number of returned rows in join |