Date: Tue, 18 May 2021 17:50:48 -0400
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: James Coleman <jtc331@gmail.com>, pgsql-bugs@postgresql.org
Subject: Re: Less selective index chosen unexpectedly
Message-ID: <20210518215048.GA22962@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <3375038.1621373660@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20210518215048.GA22962%40alvherre.pgsql>
Precedence: bulk

On 2021-May-18, Tom Lane wrote:

> The only thing I see that's really going wrong here is marginally
> inaccurate stats, especially right after a big insertion that's
> not reflected into the stats yet.  I'm not sure there's much to
> improve there.  You could increase the stats target some more,
> though of course that just pushes out the size of table where
> the issue will appear.

I think the real winner would be a mechanism to incrementally analyze
tables, so that it updates the existing stats by sampling only blocks
that have new data, and "somehow" merge that into the existing
statistics.  You could have such a process run much more frequently than
standard analyze, because the cost is [supposed to be] smaller.

Of course, the big problem with this idea is how would you merge/update
the stats at all in the first place.

... ah, it appears there have been attempts at this already:
https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.63.5414&rep=rep1&type=pdf

-- 
Álvaro Herrera       Valdivia, Chile
"Ed is the standard text editor."
      http://groups.google.com/group/alt.religion.emacs/msg/8d94ddab6a9b0ad3