Search Postgresql Archives

Re: A limit clause can cause a poor index choice

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 19 May 2020 at 22:15, Michael Lewis <mlewis@xxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux