Content-Type: multipart/alternative;
 boundary="------------kwdO8ITAm7tWfWUVpIPzMwlK"
Message-ID: <078855a8-bab5-b3ac-ddaf-00ff405b6835@gmail.com>
Date: Fri, 15 Apr 2022 18:07:16 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.8.0
Subject: Re: postgresql-14 slow query
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/078855a8-bab5-b3ac-ddaf-00ff405b6835%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------kwdO8ITAm7tWfWUVpIPzMwlK
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 4/15/22 16:59, Kenny Bachman wrote:
> Hello Team,
>
> How can I tune this query? It got even slower when I created the index 
> for (state_id, order_id desc). The following explain analyze output is 
> without an index. It takes 13 seconds if I create that index. Could 
> you help me?
>
> Thank you so much for your help.
>
> SELECT DISTINCT ON (order_history.order_id) order_id, 
> order_history.creation_date  AS c_date
> FROM work.order_history WHERE (order_history.state_id = ANY (ARRAY[30, 
> 51, 63, 136, 195, 233, 348])) AND order_history.is_false = 0
> ORDER BY order_history.order_id DESC;
>
> EXPLAIN ANALYZE output:
>
>  Unique  (cost=672007.46..1519683.55 rows=206423 width=12) (actual 
> time=1701.420..3439.095 rows=2049357 loops=1)
>    ->  Gather Merge  (cost=672007.46..1502346.48 rows=6934827 
> width=12) (actual time=1701.419..2989.243 rows=6891551 loops=1)
>          Workers Planned: 4
>          Workers Launched: 4
>          ->  Sort  (cost=671007.40..675341.67 rows=1733707 width=12) 
> (actual time=1657.609..1799.723 rows=1378310 loops=5)
>                Sort Key: order_id DESC
>                Sort Method: external merge  Disk: 38960kB
>                Worker 0:  Sort Method: external merge  Disk: 31488kB
>                Worker 1:  Sort Method: external merge  Disk: 36120kB
>                Worker 2:  Sort Method: external merge  Disk: 31368kB
>                Worker 3:  Sort Method: external merge  Disk: 36152kB
>                ->  Parallel Seq Scan on order_history 
>  (cost=0.00..473993.00 rows=1733707 width=12) (actual 
> time=0.041..1211.485 rows=1378310 loops=5)
>                      Filter: ((is_false = 0) AND (state_id = ANY 
> ('{30,51,63,136,195,233,348}'::integer[])))
>                      Rows Removed by Filter: 3268432
>  Planning Time: 0.405 ms
>  Execution Time: 3510.433 ms

Parallel sequential scan of 1.7M rows, followed by a sort, all done in 
3.5 sec? Doesn't look slow to me. Would indexing state_id be an option?

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------kwdO8ITAm7tWfWUVpIPzMwlK
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 4/15/22 16:59, Kenny Bachman wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hello Team,
        <div><br>
        </div>
        <div>How can I tune this query? It got even slower when I
          created the index for (state_id, order_id desc). The following
          explain analyze output is without an index. It takes 13
          seconds if I create that index. Could you help me?</div>
        <div><br>
        </div>
        <div>Thank you so much for your help.</div>
        <div><br>
        </div>
        <div>SELECT DISTINCT ON (order_history.order_id) order_id,
          order_history.creation_date  AS c_date<br>
          FROM work.order_history WHERE (order_history.state_id = ANY
          (ARRAY[30, 51, 63, 136, 195, 233, 348])) AND
          order_history.is_false = 0<br>
          ORDER BY order_history.order_id DESC;<br>
        </div>
        <div><br>
        </div>
        <div>EXPLAIN ANALYZE output:</div>
        <div><br>
        </div>
        <div> Unique  (cost=672007.46..1519683.55 rows=206423 width=12)
          (actual time=1701.420..3439.095 rows=2049357 loops=1)<br>
             -&gt;  Gather Merge  (cost=672007.46..1502346.48
          rows=6934827 width=12) (actual time=1701.419..2989.243
          rows=6891551 loops=1)<br>
                   Workers Planned: 4<br>
                   Workers Launched: 4<br>
                   -&gt;  Sort  (cost=671007.40..675341.67 rows=1733707
          width=12) (actual time=1657.609..1799.723 rows=1378310
          loops=5)<br>
                         Sort Key: order_id DESC<br>
                         Sort Method: external merge  Disk: 38960kB<br>
                         Worker 0:  Sort Method: external merge  Disk:
          31488kB<br>
                         Worker 1:  Sort Method: external merge  Disk:
          36120kB<br>
                         Worker 2:  Sort Method: external merge  Disk:
          31368kB<br>
                         Worker 3:  Sort Method: external merge  Disk:
          36152kB<br>
                         -&gt;  Parallel Seq Scan on order_history
           (cost=0.00..473993.00 rows=1733707 width=12) (actual
          time=0.041..1211.485 rows=1378310 loops=5)<br>
                               Filter: ((is_false = 0) AND (state_id =
          ANY ('{30,51,63,136,195,233,348}'::integer[])))<br>
                               Rows Removed by Filter: 3268432<br>
           Planning Time: 0.405 ms<br>
           Execution Time: 3510.433 ms<br>
        </div>
      </div>
    </blockquote>
    <p>Parallel sequential scan of 1.7M rows, followed by a sort, all
      done in 3.5 sec? Doesn't look slow to me. Would indexing state_id
      be an option?<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------kwdO8ITAm7tWfWUVpIPzMwlK--