Re: PostgreSQL 12.3 slow index scan chosen - Mailing list pgsql-performance

On Fri, Jun 19, 2020 at 04:59:15PM -0400, Tom Lane wrote:
>
> > Thank you for the information and suggestion. I tried bumping the statistics for the
> > objectcustomfieldvalues.objectid column to 2k, 5k and 10k followed by an analyze and
> > the query plan stayed the same. I also analyzed the article table
> > repeatedly and their was no change in the plan. The table articles only has 151 rows
> > while the objectcustomfieldvalues table has 19031909 rows. Any idea
> > about why it is so far off?
>
> What's the actual maximum value of objectcustomfieldvalues.objectid,
> and how does that compare to the endpoint value in the pg_stats
> histogram for that column?  If you've got one outlier in the table,
> it might get missed by ANALYZE.
>
>             regards, tom lane

Hi Tom,

max(objectcustomfieldvalues.objectid) = 28108423 and here is the
histogram for that column:


 schemaname |        tablename        | attname  | inherited | null_frac | avg_width | n_distinct |
                                              most_common_vals
          |
most_common_freqs                                                                                               |














                                histogram_bounds













                                                                                        | correlation |
most_common_elems| most_common_elem_freqs | elem_count_histogram  

------------+-------------------------+----------+-----------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------------+------------------------+----------------------
 public     | objectcustomfieldvalues | objectid | f         |         0 |         4 |     615227 |
{2521539,2621185,2269417,2847802,2956102,2397380,2623765,183974,2566321,2955862,3303717,3303979,3169130,2525623,1840685,2889335,190737,3188380,3303589}
|
{0.00012,0.000113333335,0.0001,9.3333336e-05,9.3333336e-05,8e-05,8e-05,7.3333336e-05,7.3333336e-05,7.3333336e-05,7.3333336e-05,7.3333336e-05,6.666667e-05,6e-05,4.6666668e-05,4.6666668e-05,4e-05,4e-05,4e-05}
|
{13,2134,3509,26547,34737,40152,46587,50105,52672,55353,57887,59711,61569,63460,65355,67312,69282,71624,73867,75957,77612,79489,81209,82909,84631,86414,88322,90288,92379,94079,95997,97857,99818,101665,103329,105219,107051,107897,109067,110861,112706,114406,116172,117998,119816,121652,124009,126170,128116,129934,132078,134032,135996,137776,139628,141620,143516,145342,147189,149198,151202,153037,154808,156492,158288,160054,161898,163653,165565,167635,169590,171793,173546,175286,177249,179324,181293,183134,185090,187036,188727,190379,191958,193496,195152,197104,199273,201063,202948,204836,206969,208730,210500,212108,213993,215950,217795,219622,221295,223425,225492,227812,229786,231604,233681,235666,237621,239273,241014,242929,244820,246835,248932,251080,253520,255509,257521,259358,261456,263416,265223,267034,268954,270791,272748,274775,276824,279028,280994,282867,284691,286480,288403,290118,291879,293724,295992,298195,300231,302005,303921,305829,307728,312187,322637,324637,326711,328410,330189,331775,333482,335258,337377,339324,341233,343491,345226,346956,348483,350291,352158,354138,356101,358109,360079,361881,364029,365859,367629,369643,371338,373086,375053,376983,378733,380404,382291,384249,386026,387804,390103,392037,394155,396146,397989,399995,401825,403596,405630,407367,409271,410961,412888,414877,416817,418590,420382,422393,424242,425999,427973,429975,431901,433971,435744,437793,439691,441941,443559,445134,446797,448650,450422,452053,453759,455479,457126,458948,460733,462434,464282,466002,467835,469652,471119,472773,474409,476020,477537,479555,481629,483237,484863,486409,488035,489791,491528,493298,495072,496836,498586,500354,502383,504174,505874,507521,509105,510761,512541,514306,515995,517715,519483,521248,522945,524539,526041,527706,529182,530744,532429,533948,535558,537359,539116,540863,542489,544262,546322,548161,549902,551405,553121,554802,556671,558536,560354,562347,564194,565897,567767,569405,571001,572912,574565,576328,578132,580065,581620,583221,585081,586608,588547,590356,592090,593962,595697,597285,598839,600508,602443,604227,605867,607468,609085,610797,612332,613921,615995,617851,619721,621524,623179,625233,626945,628751,630490,632141,633720,635495,637468,639500,641534,643506,645470,647717,649410,651203,653202,654833,656600,658617,660219,661861,663708,665443,667334,669170,670976,672711,674301,675804,677526,678907,680449,682221,684006,685693,687199,688838,690608,692189,693817,695615,697315,699054,700889,702630,704168,705826,707609,709307,710996,712999,714688,716716,718429,720331,722148,723922,725591,727357,729083,730848,732700,734386,736115,738141,739960,741521,743385,745319,747126,749002,750831,752764,754524,756279,758293,759958,761685,763366,764965,766557,768415,769979,771693,773446,775194,777188,779097,780810,782669,784398,786016,787695,789447,791420,793006,794838,796736,798655,800602,802817,804735,806387,808323,810183,811806,813674,815637,817375,819044,820907,822761,824890,826798,828762,830733,832618,834526,836655,838640,840602,842547,844741,846843,848834,850872,852630,854937,856685,858524,860329,862328,864262,866146,868297,870363,872379,874256,876114,877886,880210,882175,884423,886698,888682,890722,892866,895162,897253,899320,901578,903360,905209,907171,909164,911048,913052,915185,917251,919283,921343,923906,925787,929684,931600,933410,935385,937920,940047,942195,944602,946624,948614,951946,953948,955964,958135,1676537,2029763,2276887,2488636,2544458,2621609,2891726,3052758,3304313,3693956,27667772}
| 0.95726633 |                   |                        |  
(1 row)


Regards,
Ken