pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrzej Zawadzki <zawadaa@wp.pl>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Why query plan is different?
Date: Mon, 10 Oct 2016 17:31:28 +0200
Message-ID: <bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl> (raw)
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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>
    "  -&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>




view thread (7+ messages)  latest in thread

Message-ID: <bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl>
Permalink:  ../bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl/
Also on:    postgresql.org/message-id/bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl

 · 

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@wp.pl
  Subject: Re: Why query plan is different?
  In-Reply-To: <bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl>

* 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