0

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:

                                      Table "public.test_monika"
 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   | 
5
  • @laurenz You comments have been very helpful in earlier question. Please suggest here. Highly appreciated – user3143770 Jan 3 at 9:41
  • 2
    What is the problem? The execution time is only 0.033ms. How fast do you need that to be? – a_horse_with_no_name Jan 3 at 10:36
  • Can't you analyze the table to see if that improves pg_stats? – Gerard H. Pille Jan 3 at 15:05
  • Can't reproduce this at all. Since it is dummy table, please how us how to populate it (with a random number generator?). – jjanes Jan 3 at 16:38
  • You need to run the EXPLAIN (ANALYZE) on the production table, not on a dummy table. – Laurenz Albe Jan 7 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.