Re: A limit clause can cause a poor index choice

On Tue, 19 May 2020 at 22:15, Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> Increase default_statistics_target, at least on that column, and see if
you get a much much better plan. I don't know where I got this query from
online, but here ya go. I'd be curious how frac_MCV in this changes when
default_statistics_target is more like 250 or 500 and the table is analyzed
again to reflect that change.

It chooses the fast plan for a limit of 10 if the stats target is
approaching the number of distinct customer_id values, which is 6000 for
this test table:

stats | frac_mcv | n_distinct | n_mcv | n_hist | correlation | l10 |
l100 | l1000
-------+-------------+------------+-------+--------+-------------+-----+------+-------
-1 | 0.015666666 | 5728 | 34 | 101 | 0.98172975 | f | f
| t
150 | 0.015022225 | 5821 | 38 | 151 | 0.9817175 | f | f
| t
250 | 0.04347998 | 5867 | 134 | 251 | 0.98155195 | f | t
| t
500 | 0.12606017 | 5932 | 483 | 501 | 0.98155344 | f | t
| t
750 | 0.18231618 | 5949 | 750 | 751 | 0.98166454 | f | t
| t
1000 | 0.2329197 | 5971 | 1000 | 1001 | 0.9816691 | f | t
| t
1500 | 0.3312785 | 5982 | 1500 | 1501 | 0.981609 | f | t
| t
3000 | 0.6179379 | 5989 | 3000 | 2989 | 0.981612 | f | t
| t
4000 | 0.8033856 | 5994 | 4000 | 1994 | 0.9816348 | f | t
| t
4500 | 0.8881603 | 5994 | 4500 | 1494 | 0.98160636 | f | t
| t
4800 | 0.9281193 | 5993 | 4800 | 1193 | 0.9816273 | f | t
| t
4900 | 0.9396781 | 5994 | 4900 | 1094 | 0.9816546 | f | t
| t
5000 | 0.9500147 | 5993 | 5000 | 993 | 0.9816481 | t | t
| t
6000 | 0.999714 | 5996 | 5923 | 73 | 0.98162216 | t | t
| t
10000 | 0.99995905 | 5998 | 5970 | 28 | 0.98164326 | t | t
| t