Re: Why query plan is different? - Mailing list pgsql-performance
From | Andrzej Zawadzki |
---|---|
Subject | Re: Why query plan is different? |
Date | |
Msg-id | fdcd84d1-9b51-39eb-f316-f41220cc7d38@gmail.com Whole thread Raw |
In response to | Re: Why query plan is different? (Pavel Stehule <pavel.stehule@gmail.com>) |
List | pgsql-performance |
<div class="moz-cite-prefix">On 10.10.2016 19:09, Pavel Stehule wrote:<br /></div><blockquote cite="mid:CAFj8pRCFZGWtu5NuJ7pcVmXECTiC5wHfYJ7VjhgObcEz+AAzxw@mail.gmail.com"type="cite"><div dir="ltr"><br /><div class="gmail_extra"><br/><div class="gmail_quote">2016-10-10 17:31 GMT+02:00 Andrzej Zawadzki <span dir="ltr"><<a href="mailto:zawadaa@wp.pl"moz-do-not-send="true" target="_blank">zawadaa@wp.pl</a>></span>:<br /><blockquote class="gmail_quote"style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><divbgcolor="#FFFFFF"> Hi,<br /> Today, I noticed strange situation:<br /><br /> The samequery run on different servers has very different plan:<br /><br /> Q: SELECT b.* FROM kredytob b WHERE pesel = '22222222222' ORDER BY <a href="http://b.id" moz-do-not-send="true" target="_blank">b.id</a> 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 /></div></blockquote><div><br /></div><div>here is backward index scan with - lot of rows isthrown <br /><br /> Rows Removed by Filter: 433609" <br /><br /></div><div>probably index definition on these servers aredifferent<br /></div></div></div></div></blockquote><br /> No! That's binary copy of whole database.<br /> Index are thesame!<br /> But, when I ask database without "ORDER..."<br /> (SELECT b.id FROM kredytob b WHERE pesel = '22222222222';)<br/> then:<br /><br /> "SLOW"<br /><br /> "Index Scan using kredytob_pesel_typkred_opclass_idx on public.kredytobb (cost=0.43..115349.30 rows=113 width=4) (actual time=233.767..392.710 rows=5 loops=1)"<br /> " Output:id"<br /> " Index Cond: (b.pesel = '22222222222'::bpchar)"<br /> " Buffers: shared hit=19259"<br /> "Planning time:0.254 ms"<br /> "Execution time: 392.761 ms"<br /><br /> "FAST"<br /><br /> "Index Scan using kredytob_pesel_typkred_opclass_idxon public.kredytob b (cost=0.43..115240.10 rows=112 width=4) (actual time=378.737..836.208rows=5 loops=1)"<br /> " Output: id"<br /> " Index Cond: (b.pesel = '22222222222'::bpchar)"<br />" Buffers: shared read=19237"<br /> "Planning time: 0.568 ms"<br /> "Execution time: 836.261 ms"<br /><br /> So, indexis used in both queries but when is "ORDER" then everything change...<br /> Why?<br /><br /><br /><blockquote cite="mid:CAFj8pRCFZGWtu5NuJ7pcVmXECTiC5wHfYJ7VjhgObcEz+AAzxw@mail.gmail.com"type="cite"><div dir="ltr"><div class="gmail_extra"><divclass="gmail_quote"><div><br /></div><div>regards<br /><br /></div><div>Pavel<br /></div><div><br/></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1pxsolid rgb(204,204,204);padding-left:1ex"><div bgcolor="#FFFFFF"> " Buffers: sharedhit=316132 read=110001"<br /> "Planning time: 0.414 ms"<br /> "Execution time: 2574.139 ms"<br /><br /><br /> Fastplan:<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: <a href="http://b.id"moz-do-not-send="true" target="_blank">b.id</a> DESC"<br /> " Sort Method: top-N heapsort Memory:25kB"<br /> " Buffers: shared hit=14661 read=4576"<br /> " -> Index Scan using kredytob_pesel_typkred_<wbr/>opclass_idx on public.kredytob b (cost=0.43..115240.10 rows=112 width=4) (actual time=311.347..463.183rows=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 /> "Executiontime: 463.324 ms"<br /><br /> Data is almost equal - "slow" has a few more rows in table. ("Fast" is a copy from1 am today).<br /> Why runtime is slower?<span class="gmail-HOEnZb"><font color="#888888"><br /><br /> -- <br /> AndrzejZawadzki<br /></font></span></div></blockquote></div><br /></div></div></blockquote><p><br />
pgsql-performance by date: