Re: Strange Behaviour with multicolumn indexes

On 2019-09-12 21:04:25 +0200, Peter J. Holzer wrote:
> On 2019-09-12 12:54:55 -0400, Tom Lane wrote:
> > It's not taking the partial-index filter into account in that, I
> > suspect, which skews the results in this case --- but that would be
> > hard to account for accurately.
>
> Hmm. Wouldn't that be a problem for partial indexes in general? They
> usually cover only a small portion of the table and if the selectivity
> is computed relative to the whole table the result may be way off.
>
> I think it should be possible to adjust for a "WHERE column IS NOT NULL"
> filter, because null_frac is in the statistics. For the general case you
> would need an estimate of the number of rows covered by the index, which
> I don't think we have.

Looking through the source I see that the planner does estimate the
number of tuples in the index.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>