I have a table test_monika with 300k records. Simple query is not using index test_monika_rank_idx instead it is using primary key index test_monika_pkey. Actual rows returned are 1. The planner with current execution plan thinks that the estimated rows are 299996. I am using statistics of 1000 which are maximum. How to resolve this issue.
Current table is dummy table but we had the similar issue in production server. All of our production server runs with default_statistics_Target of 100, never faced any issue except few days back for one query.
Postgresql engine version is 10.
Explain plan is :
postgres=# explain analyze select * from test_monika where rank<=300001 and rank>=300000 order by id limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..0.46 rows=1 width=22) (actual time=0.012..0.012 rows=1 loops=1)
-> Index Scan using test_monika_pkey on test_monika (cost=0.42..10631.42 **rows=299996** width=22) (actual time=0.012..0.013 rows=1 loops=1)
Filter: ((rank <= 300001) AND (rank >= 300000))
Planning time: 0.227 ms
Execution time: 0.033 ms
(5 rows)
Below is the schema:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
name | character varying(40) | | | | extended | |
rank | integer | | | | plain | 1000 |
Indexes:
"test_monika_pkey" PRIMARY KEY, btree (id)
"test_monika_rank_idx" btree (rank)
postgres-#
ALTER TABLE test_monika ALTER rank SET STATISTICS 1000;
postgres=# select * from pg_stats WHERE tablename = ‘test_monika’;
schemaname | public
tablename | test_monika
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,3000,6000,9000,12000,15000,18000,21000,24000,27000,30000,33000,36000,39000,42000,45000,48000,51000,54000,57000,60000,63000,66000,69000,72000,75000,78000,81000,84000,87000,90000,93000,96000,99000,102000,105000,108000,111000,114000,117000,120000,123000,126000,129000,132000,135000,138000,141000,144000,147000,150000,153000,156000,159000,162000,165000,168000,171000,174000,177000,180000,183000,186000,189000,192000,195000,198000,201000,204000,207000,210000,213000,216000,219000,222000,225000,228000,231000,234000,237000,240000,243000,246000,249000,252000,255000,258000,261000,264000,267000,270000,273000,276000,279000,282000,285000,288000,291000,294000,297000,300000}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-( RECORD 2 )-
schemaname | public
tablename | test_monika
attname | name
inherited | f
null_frac | 0.999913
avg_width | 14
n_distinct | -8.66652e-05
most_common_vals |
most_common_freqs |
histogram_bounds | {ABCDEFGHIJKLMNOPQRSTUVWXYZ,BCDEFGHIJKLMNOPQRSTUVWXYZ,CDEFGHIJKLMNOPQRSTUVWXYZ,DEFGHIJKLMNOPQRSTUVWXYZ,EFGHIJKLMNOPQRSTUVWXYZ,FGHIJKLMNOPQRSTUVWXYZ,GHIJKLMNOPQRSTUVWXYZ,HIJKLMNOPQRSTUVWXYZ,IJKLMNOPQRSTUVWXYZ,JKLMNOPQRSTUVWXYZ,KLMNOPQRSTUVWXYZ,LMNOPQRSTUVWXYZ,MNOPQRSTUVWXYZ,NOPQRSTUVWXYZ,OPQRSTUVWXYZ,PQRSTUVWXYZ,QRSTUVWXYZ,RSTUVWXYZ,STUVWXYZ,TUVWXYZ,UVWXYZ,VWXYZ,WXYZ,XYZ,YZ,Z}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-( RECORD 3 )
schemaname | public
tablename | test_monika
attname | rank
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {6,305,605,905,1205,1505,1805,2105,2405,2705,3005,3305,3605,3905,4205,4505,4805,5105,5405,5705,6005,6305,6605,6905,7205,7505,7805,8105,8405,8705,9005,9305,9605,9905,10205,10505,10805,11105,11405,11705,12005,12305,12605,12905,13205,13505,13805,14105,14405,14705,15005,15305,15605,15905,16205,16505,16805,17105,17405,17705,18005,18305,18605,18905,19205,19505,19805,20105,20405,20705,21005,21305,21605,21905,22205,22505,22805,23105,23405,23705,24005,24305,24605,24905,25205,25505,25805,26105,26405,26705,27005,27305,27605,27905,28205,28505,28805,29105,29405,29705,30005,30305,30605,30905,31205,31505,31805,32105,32405,32705,33005,33305,33605,33905,34205,34505,34805,35105,35405,35705,36005,36305,36605,36905,37205,37505,37805,38105,38405,38705,39005,39305,39605,39905,40205,40505,40805,41105,41405,41705,42005,42305,42605,42905,43205,43505,43805,44105,44405,44705,45005,45305,45605,45905,46205,46505,46805,47105,47405,47705,48005,48305,48605,48905,49205,49505,49805,50105,50405,50705,51005,51305,51605,51905,52205,52505,52805,53105,53405,53705,54005,54305,54605,54905,55205,55505,55805,56105,56405,56705,57005,57305,57605,57905,58205,58505,58805,59105,59405,59705,60005,60305,60605,60905,61205,61505,61805,62105,62405,62705,63005,63305,63605,63905,64205,64505,64805,65105,65405,65705,66005,66305,66605,66905,67205,67505,67805,68105,68405,68705,69005,69305,69605,69905,70205,70505,70805,71105,71405,71705,72005,72305,72605,72905,73205,73505,73805,74105,74405,74705,75005,75305,75605,75905,76205,76505,76805,77105,77405,77705,78005,78305,78605,78905,79205,79505,79805,80105,80405,80705,81005,81305,81605,81905,82205,82505,82805,83105,83405,83705,84005,84305,84605,84905,85205,85505,85805,86105,86405,86705,87005,87305,87605,87905,88205,88505,88805,89105,89405,89705,90005,90305,90605,90905,91205,91505,91805,92105,92405,92705,93005,93305,93605,93905,94205,94505,94805,95105,95405,95705,96005,96305,96605,96905,97205,97505,97805,98105,98405,98705,99005,99305,99605,99905,100205,100505,100805,101105,101405,101705,102005,102305,102605,102905,103205,103505,103805,104105,104405,104705,105005,105305,105605,105905,106205,106505,106805,107105,107405,107705,108005,108305,108605,108905,109205,109505,109805,110105,110405,110705,111005,111305,111605,111905,112205,112505,112805,113105,113405,113705,114005,114305,114605,114905,115205,115505,115805,116105,116405,116705,117005,117305,117605,117905,118205,118505,118805,119105,119405,119705,120005,120305,120605,120905,121205,121505,121805,122105,122405,122705,123005,123305,123605,123905,124205,124505,124805,125105,125405,125705,126005,126305,126605,126905,127205,127505,127805,128105,128405,128705,129005,129305,129605,129905,130205,130505,130805,131105,131405,131705,132005,132305,132605,132905,133205,133505,133805,134105,134405,134705,135005,135305,135605,135905,136205,136505,136805,137105,137405,137705,138005,138305,138605,138905,139205,139505,139805,140105,140405,140705,141005,141305,141605,141905,142205,142505,142805,143105,143405,143705,144005,144305,144605,144905,145205,145505,145805,146105,146405,146705,147005,147305,147605,147905,148205,148505,148805,149105,149405,149705,150005,150305,150605,150905,151205,151505,151805,152105,152405,152705,153005,153305,153605,153905,154205,154505,154805,155105,155405,155705,156005,156305,156605,156905,157205,157505,157805,158105,158405,158705,159005,159305,159605,159905,160205,160505,160805,161105,161405,161705,162005,162305,162605,162905,163205,163505,163805,164105,164405,164705,165005,165305,165605,165905,166205,166505,166805,167105,167405,167705,168005,168305,168605,168905,169205,169505,169805,170105,170405,170705,171005,171305,171605,171905,172205,172505,172805,173105,173405,173705,174005,174305,174605,174905,175205,175505,175805,176105,176405,176705,177005,177305,177605,177905,178205,178505,178805,179105,179405,179705,180005,180305,180605,180905,181205,181505,181805,182105,182405,182705,183005,183305,183605,183905,184205,184505,184805,185105,185405,185705,186005,186305,186605,186905,187205,187505,187805,188105,188405,188705,189005,189305,189605,189905,190205,190505,190805,191105,191405,191705,192005,192305,192605,192905,193205,193505,193805,194105,194405,194705,195005,195305,195605,195905,196205,196505,196805,197105,197405,197705,198005,198305,198605,198905,199205,199505,199805,200105,200405,200705,201005,201305,201605,201905,202205,202505,202805,203105,203405,203705,204005,204305,204605,204905,205205,205505,205805,206105,206405,206705,207005,207305,207605,207905,208205,208505,208805,209105,209405,209705,210005,210305,210605,210905,211205,211505,211805,212105,212405,212705,213005,213305,213605,213905,214205,214505,214805,215105,215405,215705,216005,216305,216605,216905,217205,217505,217805,218105,218405,218705,219005,219305,219605,219905,220205,220505,220805,221105,221405,221705,222005,222305,222605,222905,223205,223505,223805,224105,224405,224705,225005,225305,225605,225905,226205,226505,226805,227105,227405,227705,228005,228305,228605,228905,229205,229505,229805,230105,230405,230705,231005,231305,231605,231905,232205,232505,232805,233105,233405,233705,234005,234305,234605,234905,235205,235505,235805,236105,236405,236705,237005,237305,237605,237905,238205,238505,238805,239105,239405,239705,240005,240305,240605,240905,241205,241505,241805,242105,242405,242705,243005,243305,243605,243905,244205,244505,244805,245105,245405,245705,246005,246305,246605,246905,247205,247505,247805,248105,248405,248705,249005,249305,249605,249905,250205,250505,250805,251105,251405,251705,252005,252305,252605,252905,253205,253505,253805,254105,254405,254705,255005,255305,255605,255905,256205,256505,256805,257105,257405,257705,258005,258305,258605,258905,259205,259505,259805,260105,260405,260705,261005,261305,261605,261905,262205,262505,262805,263105,263405,263705,264005,264305,264605,264905,265205,265505,265805,266105,266405,266705,267005,267305,267605,267905,268205,268505,268805,269105,269405,269705,270005,270305,270605,270905,271205,271505,271805,272105,272405,272705,273005,273305,273605,273905,274205,274505,274805,275105,275405,275705,276005,276305,276605,276905,277205,277505,277805,278105,278405,278705,279005,279305,279605,279905,280205,280505,280805,281105,281405,281705,282005,282305,282605,282905,283205,283505,283805,284105,284405,284705,285005,285305,285605,285905,286205,286505,286805,287105,287405,287705,288005,288305,288605,288905,289205,289505,289805,290105,290405,290705,291005,291305,291605,291905,292205,292505,292805,293105,293405,293705,294005,294305,294605,294905,295205,295505,295805,296105,296405,296705,297005,297305,297605,297905,298205,298505,298805,299105,299405,299705,300005}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |