From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: James Coleman <jtc331@gmail.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: Less selective index chosen unexpectedly
Date: Tue, 18 May 2021 17:50:48 -0400
Message-ID: <20210518215048.GA22962@alvherre.pgsql> (raw)
In-Reply-To: <3375038.1621373660@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@alvh.no-ip.org, tgl@sss.pgh.pa.us, jtc331@gmail.com
Subject: Re: Less selective index chosen unexpectedly
In-Reply-To: <20210518215048.GA22962@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox