Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54511=pgsql-performance-ar@postgresql.org>)
	id 1btcYE-0001za-Nu
	for pgsql-performance@arkaria.postgresql.org; Mon, 10 Oct 2016 15:31:38 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54511=pgsql-performance-ar@postgresql.org>)
	id 1btcYE-0004yt-2r
	for pgsql-performance@arkaria.postgresql.org; Mon, 10 Oct 2016 15:31:38 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <zawadaa@wp.pl>)
	id 1btcYC-0004y7-SU
	for pgsql-performance@postgresql.org; Mon, 10 Oct 2016 15:31:37 +0000
Received: from mx3.wp.pl ([212.77.101.10])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <zawadaa@wp.pl>)
	id 1btcYA-0000j0-0o
	for pgsql-performance@postgresql.org; Mon, 10 Oct 2016 15:31:35 +0000
Received: (wp-smtpd smtp.wp.pl 21275 invoked from network);
 10 Oct 2016 17:31:28 +0200
Received: from bramka-zew.dfqs.pl (HELO [192.168.20.69])
 (zawadaa@wp.pl@[194.126.222.22])
          (envelope-sender <zawadaa@wp.pl>)
          by smtp.wp.pl (WP-SMTPD) with ECDHE-RSA-AES256-SHA encrypted SMTP
          for <pgsql-performance@postgresql.org>; 10 Oct 2016 17:31:28 +0200
To: postgres performance list <pgsql-performance@postgresql.org>
From: Andrzej Zawadzki <zawadaa@wp.pl>
Subject: Why query plan is different?
Message-ID: <bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl>
Date: Mon, 10 Oct 2016 17:31:28 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit
X-WP-MailID: 0de5e7bbebaa917e76267833921cfce0
X-WP-AV: skaner antywirusowy poczty Nowej Poczty
X-WP-SPAM: NO 0000000 [QbNh]                               
X-Pg-Spam-Score: -0.5 (/)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

<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>
    "  -&gt;  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>
    "  -&gt;  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>
    "        -&gt;  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>