Hi,<br /> Today, I noticed strange situation:<br /><br /> The same query run on different servers has very different
plan:<br/><br /> Q: SELECT b.* FROM kredytob b WHERE pesel = '22222222222' ORDER BY b.id DESC LIMIT 1 <br /><br />
Slowplan:<br /><br /> "Limit (cost=0.43..28712.33 rows=1 width=4) (actual time=2574.041..2574.044 rows=1 loops=1)"<br
/>" Output: id"<br /> " Buffers: shared hit=316132 read=110001"<br /> " -> Index Scan Backward using
kredytob_pkeyon public.kredytob b (cost=0.43..3244444.80 rows=113 width=4) (actual time=2574.034..2574.034 rows=1
loops=1)"<br/> " Output: id"<br /> " Filter: (b.pesel = '22222222222'::bpchar)"<br /> " Rows
Removedby Filter: 433609"<br /> " Buffers: shared hit=316132 read=110001"<br /> "Planning time: 0.414 ms"<br />
"Executiontime: 2574.139 ms"<br /><br /><br /> Fast plan:<br /> "Limit (cost=115240.66..115240.66 rows=1 width=4)
(actualtime=463.275..463.276 rows=1 loops=1)"<br /> " Output: id"<br /> " Buffers: shared hit=14661 read=4576"<br />
" -> Sort (cost=115240.66..115240.94 rows=112 width=4) (actual time=463.271..463.271 rows=1 loops=1)"<br />
" Output: id"<br /> " Sort Key: b.id DESC"<br /> " Sort Method: top-N heapsort Memory: 25kB"<br />
" Buffers: shared hit=14661 read=4576"<br /> " -> Index Scan using kredytob_pesel_typkred_opclass_idx
onpublic.kredytob b (cost=0.43..115240.10 rows=112 width=4) (actual time=311.347..463.183 rows=5 loops=1)"<br />
" Output: id"<br /> " Index Cond: (b.pesel = '22222222222'::bpchar)"<br /> "
Buffers:shared hit=14661 read=4576"<br /> "Planning time: 0.383 ms"<br /> "Execution time: 463.324 ms"<br /><br /> Data
isalmost equal - "slow" has a few more rows in table. ("Fast" is a copy from 1 am today).<br /> Why runtime is
slower?<br/><br /> -- <br /> Andrzej Zawadzki<br />
