Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jmOUG-0006Ax-K0 for pgsql-performance@arkaria.postgresql.org; Fri, 19 Jun 2020 21:23:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jmOUE-0006HT-1l for pgsql-performance@arkaria.postgresql.org; Fri, 19 Jun 2020 21:23:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jmOUD-0006HI-Ph for pgsql-performance@lists.postgresql.org; Fri, 19 Jun 2020 21:23:45 +0000 Received: from aart.rice.edu ([168.7.56.48]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jmOU7-0000V6-Vh for pgsql-performance@lists.postgresql.org; Fri, 19 Jun 2020 21:23:45 +0000 Received: by aart.rice.edu (Postfix, from userid 18612) id A8C22427F342; Fri, 19 Jun 2020 16:23:38 -0500 (CDT) Date: Fri, 19 Jun 2020 16:23:38 -0500 From: Kenneth Marshall To: Tom Lane Cc: pgsql-performance@lists.postgresql.org Subject: Re: PostgreSQL 12.3 slow index scan chosen Message-ID: <20200619212338.GM1497@aart.rice.edu> References: <20200619194914.GI1497@aart.rice.edu> <1755412.1592597470@sss.pgh.pa.us> <20200619204950.GL1497@aart.rice.edu> <1757512.1592600355@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable In-Reply-To: <1757512.1592600355@sss.pgh.pa.us> User-Agent: Mutt/1.5.21 (2010-09-15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Fri, Jun 19, 2020 at 04:59:15PM -0400, Tom Lane wrote: >=20 > > Thank you for the information and suggestion. I tried bumping the stati= stics for the > > objectcustomfieldvalues.objectid column to 2k, 5k and 10k followed by a= n 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? >=20 > 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. >=20 > regards, tom lane Hi Tom, max(objectcustomfieldvalues.objectid) =3D 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_e= lems | most_common_elem_freqs | elem_count_histogram=20 ------------+-------------------------+----------+-----------+-----------+-= ----------+------------+---------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------+-----------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------+---------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------+-------------+--------------= -----+------------------------+---------------------- public | objectcustomfieldvalues | objectid | f | 0 | = 4 | 615227 | {2521539,2621185,2269417,2847802,2956102,2397380,2= 623765,183974,2566321,2955862,3303717,3303979,3169130,2525623,1840685,28893= 35,190737,3188380,3303589} | {0.00012,0.000113333335,0.0001,9.3333336e-05,9= =2E3333336e-05,8e-05,8e-05,7.3333336e-05,7.3333336e-05,7.3333336e-05,7.3333= 336e-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,5788= 7,59711,61569,63460,65355,67312,69282,71624,73867,75957,77612,79489,81209,8= 2909,84631,86414,88322,90288,92379,94079,95997,97857,99818,101665,103329,10= 5219,107051,107897,109067,110861,112706,114406,116172,117998,119816,121652,= 124009,126170,128116,129934,132078,134032,135996,137776,139628,141620,14351= 6,145342,147189,149198,151202,153037,154808,156492,158288,160054,161898,163= 653,165565,167635,169590,171793,173546,175286,177249,179324,181293,183134,1= 85090,187036,188727,190379,191958,193496,195152,197104,199273,201063,202948= ,204836,206969,208730,210500,212108,213993,215950,217795,219622,221295,2234= 25,225492,227812,229786,231604,233681,235666,237621,239273,241014,242929,24= 4820,246835,248932,251080,253520,255509,257521,259358,261456,263416,265223,= 267034,268954,270791,272748,274775,276824,279028,280994,282867,284691,28648= 0,288403,290118,291879,293724,295992,298195,300231,302005,303921,305829,307= 728,312187,322637,324637,326711,328410,330189,331775,333482,335258,337377,3= 39324,341233,343491,345226,346956,348483,350291,352158,354138,356101,358109= ,360079,361881,364029,365859,367629,369643,371338,373086,375053,376983,3787= 33,380404,382291,384249,386026,387804,390103,392037,394155,396146,397989,39= 9995,401825,403596,405630,407367,409271,410961,412888,414877,416817,418590,= 420382,422393,424242,425999,427973,429975,431901,433971,435744,437793,43969= 1,441941,443559,445134,446797,448650,450422,452053,453759,455479,457126,458= 948,460733,462434,464282,466002,467835,469652,471119,472773,474409,476020,4= 77537,479555,481629,483237,484863,486409,488035,489791,491528,493298,495072= ,496836,498586,500354,502383,504174,505874,507521,509105,510761,512541,5143= 06,515995,517715,519483,521248,522945,524539,526041,527706,529182,530744,53= 2429,533948,535558,537359,539116,540863,542489,544262,546322,548161,549902,= 551405,553121,554802,556671,558536,560354,562347,564194,565897,567767,56940= 5,571001,572912,574565,576328,578132,580065,581620,583221,585081,586608,588= 547,590356,592090,593962,595697,597285,598839,600508,602443,604227,605867,6= 07468,609085,610797,612332,613921,615995,617851,619721,621524,623179,625233= ,626945,628751,630490,632141,633720,635495,637468,639500,641534,643506,6454= 70,647717,649410,651203,653202,654833,656600,658617,660219,661861,663708,66= 5443,667334,669170,670976,672711,674301,675804,677526,678907,680449,682221,= 684006,685693,687199,688838,690608,692189,693817,695615,697315,699054,70088= 9,702630,704168,705826,707609,709307,710996,712999,714688,716716,718429,720= 331,722148,723922,725591,727357,729083,730848,732700,734386,736115,738141,7= 39960,741521,743385,745319,747126,749002,750831,752764,754524,756279,758293= ,759958,761685,763366,764965,766557,768415,769979,771693,773446,775194,7771= 88,779097,780810,782669,784398,786016,787695,789447,791420,793006,794838,79= 6736,798655,800602,802817,804735,806387,808323,810183,811806,813674,815637,= 817375,819044,820907,822761,824890,826798,828762,830733,832618,834526,83665= 5,838640,840602,842547,844741,846843,848834,850872,852630,854937,856685,858= 524,860329,862328,864262,866146,868297,870363,872379,874256,876114,877886,8= 80210,882175,884423,886698,888682,890722,892866,895162,897253,899320,901578= ,903360,905209,907171,909164,911048,913052,915185,917251,919283,921343,9239= 06,925787,929684,931600,933410,935385,937920,940047,942195,944602,946624,94= 8614,951946,953948,955964,958135,1676537,2029763,2276887,2488636,2544458,26= 21609,2891726,3052758,3304313,3693956,27667772} | 0.95726633 | = | |=20 (1 row) Regards, Ken