<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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>
Slow plan:<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_pkey on 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 Removed by Filter: 433609"<br>
" Buffers: shared hit=316132 read=110001"<br>
"Planning time: 0.414 ms"<br>
"Execution time: 2574.139 ms"<br>
<br>
<br>
Fast plan:<br>
"Limit (cost=115240.66..115240.66 rows=1 width=4) (actual
time=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
on public.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 is almost 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>
</body>
</html>