pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy query plan is different?
7+ messages / 3 participants
[nested] [flat]
* Why query plan is different?
@ 2016-10-10 15:31 Andrzej Zawadzki <zawadaa@wp.pl>
2016-10-10 17:09 ` Re: Why query plan is different? Pavel Stehule <pavel.stehule@gmail.com>
2016-10-10 21:17 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Andrzej Zawadzki @ 2016-10-10 15:31 UTC (permalink / raw)
To: pgsql-performance
<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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why query plan is different?
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
@ 2016-10-10 17:09 ` Pavel Stehule <pavel.stehule@gmail.com>
2016-10-10 20:51 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2016-10-10 17:09 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@wp.pl>; +Cc: pgsql-performance
2016-10-10 17:31 GMT+02:00 Andrzej Zawadzki <zawadaa@wp.pl>:
> Hi,
> Today, I noticed strange situation:
>
> The same query run on different servers has very different plan:
>
> Q: SELECT b.* FROM kredytob b WHERE pesel = '22222222222' ORDER BY b.id
> DESC LIMIT 1
>
> Slow plan:
>
> "Limit (cost=0.43..28712.33 rows=1 width=4) (actual
> time=2574.041..2574.044 rows=1 loops=1)"
> " Output: id"
> " Buffers: shared hit=316132 read=110001"
> " -> 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)"
> " Output: id"
> " Filter: (b.pesel = '22222222222'::bpchar)"
> " Rows Removed by Filter: 433609"
>
here is backward index scan with - lot of rows is thrown
Rows Removed by Filter: 433609"
probably index definition on these servers are different
regards
Pavel
> " Buffers: shared hit=316132 read=110001"
> "Planning time: 0.414 ms"
> "Execution time: 2574.139 ms"
>
>
> Fast plan:
> "Limit (cost=115240.66..115240.66 rows=1 width=4) (actual
> time=463.275..463.276 rows=1 loops=1)"
> " Output: id"
> " Buffers: shared hit=14661 read=4576"
> " -> Sort (cost=115240.66..115240.94 rows=112 width=4) (actual
> time=463.271..463.271 rows=1 loops=1)"
> " Output: id"
> " Sort Key: b.id DESC"
> " Sort Method: top-N heapsort Memory: 25kB"
> " Buffers: shared hit=14661 read=4576"
> " -> 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)"
> " Output: id"
> " Index Cond: (b.pesel = '22222222222'::bpchar)"
> " Buffers: shared hit=14661 read=4576"
> "Planning time: 0.383 ms"
> "Execution time: 463.324 ms"
>
> Data is almost equal - "slow" has a few more rows in table. ("Fast" is a
> copy from 1 am today).
> Why runtime is slower?
>
> --
> Andrzej Zawadzki
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why query plan is different?
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
2016-10-10 17:09 ` Re: Why query plan is different? Pavel Stehule <pavel.stehule@gmail.com>
@ 2016-10-10 20:51 ` Andrzej Zawadzki <zawadaa@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrzej Zawadzki @ 2016-10-10 20:51 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Andrzej Zawadzki <zawadaa@wp.pl>; +Cc: pgsql-performance
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<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 moz-do-not-send="true"
href="mailto:zawadaa@wp.pl" 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">
<div bgcolor="#FFFFFF"> 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 <a moz-do-not-send="true"
href="http://b.id"; 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 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>
</div>
</blockquote>
<div><br>
</div>
<div>here is backward index scan with - lot of rows is
thrown <br>
<br>
Rows Removed by Filter: 433609" <br>
<br>
</div>
<div>probably index definition on these servers are
different<br>
</div>
</div>
</div>
</div>
</blockquote>
<br>
No! That's binary copy of whole database.<br>
Index are the same!<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.kredytob b (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_idx on
public.kredytob b (cost=0.43..115240.10 rows=112 width=4) (actual
time=378.737..836.208 rows=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, index is 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">
<div class="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:1px solid
rgb(204,204,204);padding-left:1ex">
<div bgcolor="#FFFFFF"> " 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 moz-do-not-send="true"
href="http://b.id"; 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.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?<span class="gmail-HOEnZb"><font
color="#888888"><br>
<br>
-- <br>
Andrzej Zawadzki<br>
</font></span></div>
</blockquote>
</div>
<br>
</div>
</div>
</blockquote>
<p><br>
</p>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why query plan is different?
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
@ 2016-10-10 21:17 ` Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-11 01:47 ` Re: Why query plan is different? Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Andrzej Zawadzki @ 2016-10-10 21:17 UTC (permalink / raw)
To: pgsql-performance
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 10.10.2016 17:31, Andrzej Zawadzki
wrote:<br>
</div>
<blockquote cite="mid:bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl"
type="cite">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
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>
</blockquote>
<br>
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: analyze kredytob;<br>
<br>
And now:<br>
"Limit (cost=333.31..333.31 rows=1 width=4) (actual
time=0.100..0.102 rows=1 loops=1)"<br>
" Output: id"<br>
" Buffers: shared hit=8"<br>
" -> Sort (cost=333.31..333.59 rows=114 width=4) (actual
time=0.095..0.095 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=8"<br>
" -> Index Scan using kredytob_pesel_typkred_idx on
public.kredytob b (cost=0.43..332.74 rows=114 width=4) (actual
time=0.046..0.065 rows=5 loops=1)"<br>
" Output: id"<br>
" Index Cond: (b.pesel = '22222222222'::bpchar)"<br>
" Buffers: shared hit=8"<br>
"Planning time: 0.438 ms"<br>
"Execution time: 0.154 ms"<br>
<br>
So, what is a reason that "SLOW" server doesn't like opclass index?<br>
<br>
-- <br>
Andrzej<br>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why query plan is different?
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
2016-10-10 21:17 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
@ 2016-10-11 01:47 ` Pavel Stehule <pavel.stehule@gmail.com>
2016-10-11 11:19 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2016-10-11 01:47 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@gmail.com>; +Cc: pgsql-performance
2016-10-10 23:17 GMT+02:00 Andrzej Zawadzki <zawadaa@gmail.com>:
> On 10.10.2016 17:31, Andrzej Zawadzki wrote:
>
> Hi,
> Today, I noticed strange situation:
>
> The same query run on different servers has very different plan:
>
> Q: SELECT b.* FROM kredytob b WHERE pesel = '22222222222' ORDER BY b.id
> DESC LIMIT 1
>
> Slow plan:
>
> "Limit (cost=0.43..28712.33 rows=1 width=4) (actual
> time=2574.041..2574.044 rows=1 loops=1)"
> " Output: id"
> " Buffers: shared hit=316132 read=110001"
> " -> 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)"
> " Output: id"
> " Filter: (b.pesel = '22222222222'::bpchar)"
> " Rows Removed by Filter: 433609"
> " Buffers: shared hit=316132 read=110001"
> "Planning time: 0.414 ms"
> "Execution time: 2574.139 ms"
>
>
> Fast plan:
> "Limit (cost=115240.66..115240.66 rows=1 width=4) (actual
> time=463.275..463.276 rows=1 loops=1)"
> " Output: id"
> " Buffers: shared hit=14661 read=4576"
> " -> Sort (cost=115240.66..115240.94 rows=112 width=4) (actual
> time=463.271..463.271 rows=1 loops=1)"
> " Output: id"
> " Sort Key: b.id DESC"
> " Sort Method: top-N heapsort Memory: 25kB"
> " Buffers: shared hit=14661 read=4576"
> " -> 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)"
> " Output: id"
> " Index Cond: (b.pesel = '22222222222'::bpchar)"
> " Buffers: shared hit=14661 read=4576"
> "Planning time: 0.383 ms"
> "Execution time: 463.324 ms"
>
> Data is almost equal - "slow" has a few more rows in table. ("Fast" is a
> copy from 1 am today).
> Why runtime is slower?
>
>
> I made another INDEX, without opclass:
>
> CREATE INDEX kredytob_pesel_typkred_idx
> ON public.kredytob
> USING btree
> (pesel COLLATE pg_catalog."default", typkred);
>
> after that: analyze kredytob;
>
> And now:
> "Limit (cost=333.31..333.31 rows=1 width=4) (actual time=0.100..0.102
> rows=1 loops=1)"
> " Output: id"
> " Buffers: shared hit=8"
> " -> Sort (cost=333.31..333.59 rows=114 width=4) (actual
> time=0.095..0.095 rows=1 loops=1)"
> " Output: id"
> " Sort Key: b.id DESC"
> " Sort Method: top-N heapsort Memory: 25kB"
> " Buffers: shared hit=8"
> " -> Index Scan using kredytob_pesel_typkred_idx on
> public.kredytob b (cost=0.43..332.74 rows=114 width=4) (actual
> time=0.046..0.065 rows=5 loops=1)"
> " Output: id"
> " Index Cond: (b.pesel = '22222222222'::bpchar)"
> " Buffers: shared hit=8"
> "Planning time: 0.438 ms"
> "Execution time: 0.154 ms"
>
> So, what is a reason that "SLOW" server doesn't like opclass index?
>
what is default locales?
Pavel
>
> --
> Andrzej
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why query plan is different?
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
2016-10-10 21:17 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-11 01:47 ` Re: Why query plan is different? Pavel Stehule <pavel.stehule@gmail.com>
@ 2016-10-11 11:19 ` Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-11 11:21 ` Re: Why query plan is different? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andrzej Zawadzki @ 2016-10-11 11:19 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<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 moz-do-not-send="true"
href="mailto:zawadaa@gmail.com" target="_blank">zawadaa@gmail.com</a>></span>:<br>
<blockquote class="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 WHERE pesel =
'22222222222' ORDER BY <a moz-do-not-send="true"
href="http://b.id"; 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 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: <a moz-do-not-send="true"
href="http://b.id"; 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.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>
</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: analyze kredytob;<br>
<br>
And now:<br>
"Limit (cost=333.31..333.31 rows=1 width=4) (actual
time=0.100..0.102 rows=1 loops=1)"<br>
" Output: id"<br>
" Buffers: shared hit=8"<br>
" -> Sort (cost=333.31..333.59 rows=114 width=4)
(actual time=0.095..0.095 rows=1 loops=1)"<span class=""><br>
" Output: id"<br>
" Sort Key: <a moz-do-not-send="true"
href="http://b.id"; 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_idx on public.kredytob b
(cost=0.43..332.74 rows=114 width=4) (actual
time=0.046..0.065 rows=5 loops=1)"<span class=""><br>
" Output: id"<br>
" Index Cond: (b.pesel =
'22222222222'::bpchar)"<br>
</span> " Buffers: shared hit=8"<br>
"Planning time: 0.438 ms"<br>
"Execution time: 0.154 ms"<br>
<br>
So, what is a reason that "SLOW" server doesn't like
opclass index?<span class="HOEnZb"><font color="#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>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why query plan is different?
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
2016-10-10 21:17 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-11 01:47 ` Re: Why query plan is different? Pavel Stehule <pavel.stehule@gmail.com>
2016-10-11 11:19 ` Re: Why query plan is different? Andrzej Zawadzki <zawadaa@gmail.com>
@ 2016-10-11 11:21 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2016-10-11 11:21 UTC (permalink / raw)
To: Andrzej Zawadzki <zawadaa@gmail.com>; +Cc: pgsql-performance
2016-10-11 13:19 GMT+02:00 Andrzej Zawadzki <zawadaa@gmail.com>:
> On 11.10.2016 03:47, Pavel Stehule wrote:
>
>
>
> 2016-10-10 23:17 GMT+02:00 Andrzej Zawadzki <zawadaa@gmail.com>:
>
>> On 10.10.2016 17:31, Andrzej Zawadzki wrote:
>>
>> Hi,
>> Today, I noticed strange situation:
>>
>> The same query run on different servers has very different plan:
>>
>> Q: SELECT b.* FROM kredytob b WHERE pesel = '22222222222' ORDER BY b.id
>> DESC LIMIT 1
>>
>> Slow plan:
>>
>> "Limit (cost=0.43..28712.33 rows=1 width=4) (actual
>> time=2574.041..2574.044 rows=1 loops=1)"
>> " Output: id"
>> " Buffers: shared hit=316132 read=110001"
>> " -> 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)"
>> " Output: id"
>> " Filter: (b.pesel = '22222222222'::bpchar)"
>> " Rows Removed by Filter: 433609"
>> " Buffers: shared hit=316132 read=110001"
>> "Planning time: 0.414 ms"
>> "Execution time: 2574.139 ms"
>>
>>
>> Fast plan:
>> "Limit (cost=115240.66..115240.66 rows=1 width=4) (actual
>> time=463.275..463.276 rows=1 loops=1)"
>> " Output: id"
>> " Buffers: shared hit=14661 read=4576"
>> " -> Sort (cost=115240.66..115240.94 rows=112 width=4) (actual
>> time=463.271..463.271 rows=1 loops=1)"
>> " Output: id"
>> " Sort Key: b.id DESC"
>> " Sort Method: top-N heapsort Memory: 25kB"
>> " Buffers: shared hit=14661 read=4576"
>> " -> 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)"
>> " Output: id"
>> " Index Cond: (b.pesel = '22222222222'::bpchar)"
>> " Buffers: shared hit=14661 read=4576"
>> "Planning time: 0.383 ms"
>> "Execution time: 463.324 ms"
>>
>> Data is almost equal - "slow" has a few more rows in table. ("Fast" is a
>> copy from 1 am today).
>> Why runtime is slower?
>>
>>
>> I made another INDEX, without opclass:
>>
>> CREATE INDEX kredytob_pesel_typkred_idx
>> ON public.kredytob
>> USING btree
>> (pesel COLLATE pg_catalog."default", typkred);
>>
>> after that: analyze kredytob;
>>
>> And now:
>> "Limit (cost=333.31..333.31 rows=1 width=4) (actual time=0.100..0.102
>> rows=1 loops=1)"
>> " Output: id"
>> " Buffers: shared hit=8"
>> " -> Sort (cost=333.31..333.59 rows=114 width=4) (actual
>> time=0.095..0.095 rows=1 loops=1)"
>> " Output: id"
>> " Sort Key: b.id DESC"
>> " Sort Method: top-N heapsort Memory: 25kB"
>> " Buffers: shared hit=8"
>> " -> Index Scan using kredytob_pesel_typkred_idx on
>> public.kredytob b (cost=0.43..332.74 rows=114 width=4) (actual
>> time=0.046..0.065 rows=5 loops=1)"
>> " Output: id"
>> " Index Cond: (b.pesel = '22222222222'::bpchar)"
>> " Buffers: shared hit=8"
>> "Planning time: 0.438 ms"
>> "Execution time: 0.154 ms"
>>
>> So, what is a reason that "SLOW" server doesn't like opclass index?
>>
>
> what is default locales?
>
> LATIN2 - that's why I use opclass.
>
Is it this local in both cases?
Regards
Pavel
>
> --
> Andrzej
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2016-10-11 11:21 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-10 15:31 Why query plan is different? Andrzej Zawadzki <zawadaa@wp.pl>
2016-10-10 17:09 ` Pavel Stehule <pavel.stehule@gmail.com>
2016-10-10 20:51 ` Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-10 21:17 ` Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-11 01:47 ` Pavel Stehule <pavel.stehule@gmail.com>
2016-10-11 11:19 ` Andrzej Zawadzki <zawadaa@gmail.com>
2016-10-11 11:21 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox