pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andrzej Zawadzki <zawadaa@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
To: Andrzej Zawadzki <zawadaa@wp.pl>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Why query plan is different?
Date: Mon, 10 Oct 2016 22:51:07 +0200
Message-ID: <fdcd84d1-9b51-39eb-f316-f41220cc7d38@gmail.com> (raw )
In-Reply-To: <CAFj8pRCFZGWtu5NuJ7pcVmXECTiC5wHfYJ7VjhgObcEz+AAzxw@mail.gmail.com >
References: <bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl >
<CAFj8pRCFZGWtu5NuJ7pcVmXECTiC5wHfYJ7VjhgObcEz+AAzxw@mail.gmail.com >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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>
view thread (7+ messages) latest in thread
Message-ID: <fdcd84d1-9b51-39eb-f316-f41220cc7d38@gmail.com>
Permalink: ../fdcd84d1-9b51-39eb-f316-f41220cc7d38@gmail.com/
Also on: postgresql.org/message-id/fdcd84d1-9b51-39eb-f316-f41220cc7d38@gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zawadaa@gmail.com, pavel.stehule@gmail.com, zawadaa@wp.pl
Subject: Re: Why query plan is different?
In-Reply-To: <fdcd84d1-9b51-39eb-f316-f41220cc7d38@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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