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>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Why query plan is different?
Date: Tue, 11 Oct 2016 13:19:14 +0200
Message-ID: <9c1b7ddf-6d26-78e3-a83d-80930b10e58a@gmail.com> (raw)
In-Reply-To: <CAFj8pRBKLGJkyNdBdhg0CfwD+ZMTrC0xmqJBH64kBEJEYLHxJg@mail.gmail.com>
References: <bb1d234e-62ee-70e8-44ef-9b0919ec1268@wp.pl>
	<8aa28a9c-95c1-a8bb-e542-e7e4fd499c27@gmail.com>
	<CAFj8pRBKLGJkyNdBdhg0CfwD+ZMTrC0xmqJBH64kBEJEYLHxJg@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 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">&lt;<a moz-do-not-send="true"
                href="mailto:zawadaa@gmail.com" target="_blank">zawadaa@gmail.com</a>&gt;</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>
                      "  -&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: <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>
                      "        -&gt;  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>
                "  -&gt;  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>
                "        -&gt;  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>




view thread (7+ messages)  latest in thread

Message-ID: <9c1b7ddf-6d26-78e3-a83d-80930b10e58a@gmail.com>
Permalink:  ../9c1b7ddf-6d26-78e3-a83d-80930b10e58a@gmail.com/
Also on:    postgresql.org/message-id/9c1b7ddf-6d26-78e3-a83d-80930b10e58a@gmail.com

 · 

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
  Subject: Re: Why query plan is different?
  In-Reply-To: <9c1b7ddf-6d26-78e3-a83d-80930b10e58a@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