Re: Fwd: extend statistics help reduce index scan a lot of shared buffer hits.

On 2/27/24 14:58, James Pang wrote:
> Postgresql 14.8, Redhat8. looks like have to create extend statistics
> on indexed and joined columns to make join filters pushed down to secondary
> index scan in nestloop, and the shared buffer hits show big difference.
>
> is it expected ?
>

It's hard to say what exactly is happening in the example query (I'd
have to do some debugging, but that's impossible without a reproducer),
but I think it's mostly expected.

My guess is that without the stats the optimizer sees this:

-> Index Scan using idx_mtgattndlstext_mmmuuid_uid on mtgxxxxxxxext b
(cost=0.56..2.78 rows=1 width=133) (actual time=0.016..0.698 rows=2142
loops=2142)

and so decides there's no point in pushing down more conditions to the
index scan (because it already returns just 1 row). But there's some
sort of correlation / skew, and it returns 2142 rows.

With the extended stats it realizes pushing down more conditions makes
sense, because doing that in index scan is cheaper than having to read
the heap pages etc. So it does that.

So yeah, this seems exactly the improvement you'd expect from extended
stats. Why do you think this would not be expected?

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company