Re: Why query plan is different? - Mailing list pgsql-performance
From | Andrzej Zawadzki |
---|---|
Subject | Re: Why query plan is different? |
Date | |
Msg-id | 9c1b7ddf-6d26-78e3-a83d-80930b10e58a@gmail.com Whole thread Raw |
In response to | Re: Why query plan is different? (Pavel Stehule <pavel.stehule@gmail.com>) |
Responses |
Re: Why query plan is different?
|
List | pgsql-performance |
<div class="moz-cite-prefix">On 11.10.2016 03:47, Pavel Stehule wrote:<br /></div><blockquote cite="mid:CAFj8pRBKLGJkyNdBdhg0CfwD+ZMTrC0xmqJBH64kBEJEYLHxJg@mail.gmail.com"type="cite"><div dir="ltr"><br /><div class="gmail_extra"><br/><div class="gmail_quote">2016-10-10 23:17 GMT+02:00 Andrzej Zawadzki <span dir="ltr"><<a href="mailto:zawadaa@gmail.com"moz-do-not-send="true" target="_blank">zawadaa@gmail.com</a>></span>:<br /><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div bgcolor="#FFFFFF"text="#000000"><div><div class="h5"><div class="m_-861737590477819645moz-cite-prefix">On 10.10.2016 17:31,Andrzej Zawadzki wrote:<br /></div><blockquote type="cite"> 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 WHEREpesel = '22222222222' ORDER BY <a href="http://b.id" moz-do-not-send="true" target="_blank">b.id</a> 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 usingkredytob_pkey on public.kredytob b (cost=0.43..3244444.80 rows=113 width=4) (actual time=2574.034..2574.034 rows=1loops=1)"<br /> " Output: id"<br /> " Filter: (b.pesel = '22222222222'::bpchar)"<br /> " RowsRemoved 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: <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=14661read=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.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" hasa few more rows in table. ("Fast" is a copy from 1 am today).<br /> Why runtime is slower?<br /></blockquote><br /></div></div>I made another INDEX, without opclass:<br /><br /> CREATE INDEX kredytob_pesel_typkred_idx<br /> ON public.kredytob<br/> USING btree<br /> (pesel COLLATE pg_catalog."default", typkred);<br /><br /> after that: analyzekredytob;<br /><br /> And now:<br /> "Limit (cost=333.31..333.31 rows=1 width=4) (actual time=0.100..0.102 rows=1loops=1)"<br /> " Output: id"<br /> " Buffers: shared hit=8"<br /> " -> Sort (cost=333.31..333.59 rows=114width=4) (actual time=0.095..0.095 rows=1 loops=1)"<span class=""><br /> " Output: id"<br /> " SortKey: <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 /></span> " Buffers: shared hit=8"<br /> " -> Index Scan using kredytob_pesel_typkred_idxon public.kredytob b (cost=0.43..332.74 rows=114 width=4) (actual time=0.046..0.065 rows=5 loops=1)"<spanclass=""><br /> " Output: id"<br /> " Index Cond: (b.pesel = '22222222222'::bpchar)"<br/></span> " Buffers: shared hit=8"<br /> "Planning time: 0.438 ms"<br /> "Executiontime: 0.154 ms"<br /><br /> So, what is a reason that "SLOW" server doesn't like opclass index?<span class="HOEnZb"><fontcolor="#888888"><br /></font></span></div></blockquote><div><br /></div><div>what is default locales?<br/><br /></div></div></div></div></blockquote> LATIN2 - that's why I use opclass.<br /><br /> -- <br /> Andrzej<br/>
pgsql-performance by date: