[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: query plan using partial index expects a much larger number of rows than is possible
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2020-10-29 15:08:11
Message-ID: CAHOFxGqeGh_u4hp2+H9-WFFPgjdVRtyAz5MiXZoiV5QORQiq4g () mail ! gmail ! com
[Download RAW message or body]

On Wed, Oct 28, 2020 at 5:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "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.

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Wed, Oct 28, 2020 at 5:30 PM Tom Lane &lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">&quot;Olivier \
Poquet&quot; &lt;<a href="mailto:opoquet@plumdev.com" \
target="_blank">opoquet@plumdev.com</a>&gt; writes:<br> &gt; Looking at it in more \
detail, I found that the planner is assuming that I&#39;ll get millions of rows back \
even when I do a simple query that does an index scan on my partial index:<br> <br>
We don&#39;t look at partial-index predicates when trying to estimate the<br>
selectivity of a WHERE clause.   It&#39;s not clear to me whether that&#39;d be<br>
a useful thing to do, or whether it could be shoehorned into the system<br>
easily.   (One big problem is that while the index size could provide<br>
an upper bound, it&#39;s not apparent how to combine that knowledge with<br>
selectivities of unrelated conditions.   Also, it&#39;s riskier to extrapolate<br>
a current rowcount estimate from stale relpages/reltuples data for an<br>
index than it is for a table, because the index is less likely to scale<br>
up linearly.)<br>
<br>
                                    regards, tom lane<br>
</blockquote><div><br></div><div><br></div><div>Aren&#39;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.</div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]