Re: Less selective index chosen unexpectedly

On Tue, May 18, 2021 at 1:36 PM James Coleman <jtc331@gmail.com> wrote:
> The attached test case demonstrates the full problem at least some of the time--I've not been able to make it
deterministic,but I'd say it shows the wrong plan choice (resulting in ~5k unnecessarily processed and filtered rows)
roughly2/3 of the time on my laptop against PG11 (what we're running on in production). Against ~master I able to
reproducethe wrong plan choice, but not the large number of filtered rows until I modified the second INSERT's case
statementto use "n.i > 5000" instead of "n.i < 25000" -- I assume this is due to some combination of index
deduplication/suffixtruncation. Interestingly with those changes it seems to be more deterministic against ~master than
theoriginal repro case against 11. 

That's expected with 12 because the old "getting tired" behavior to
deal with pages full of duplicates in !heapkeyspace indexes adds
non-determinism (to every index tuple insertion involving an incoming
item that already has many duplicates in the index). I actually relied
on the determinism when I wrote my own index space utilization test
suite for the Postgres 12 and Postgres 13 work. With a little care you
can expect to get exactly the same index with a test case involving
serial inserts.

--
Peter Geoghegan