MIME-Version: 1.0
From: Kenny Bachman <kenny.bachman17@gmail.com>
Date: Fri, 15 Apr 2022 23:59:25 +0300
Message-ID: 
 <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
Subject: postgresql-14 slow query
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000025412b05dcb7b002"
Archived-At: 
 <https://www.postgresql.org/message-id/CAC0w7L%2BcxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A%40mail.gmail.com>
Precedence: bulk

--00000000000025412b05dcb7b002
Content-Type: text/plain; charset="UTF-8"

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

--00000000000025412b05dcb7b002
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"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=C2=A0an=C2=A0index. It take=
s 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 DIST=
INCT ON (order_history.order_id) order_id, order_history.creation_date =C2=
=A0AS c_date<br>FROM work.order_history WHERE (order_history.state_id =3D A=
NY (ARRAY[30, 51, 63, 136, 195, 233, 348])) AND order_history.is_false =3D =
0<br>ORDER BY order_history.order_id DESC;<br></div><div><br></div><div>EXP=
LAIN ANALYZE output:</div><div><br></div><div>=C2=A0Unique =C2=A0(cost=3D67=
2007.46..1519683.55 rows=3D206423 width=3D12) (actual time=3D1701.420..3439=
.095 rows=3D2049357 loops=3D1)<br>=C2=A0 =C2=A0-&gt; =C2=A0Gather Merge =C2=
=A0(cost=3D672007.46..1502346.48 rows=3D6934827 width=3D12) (actual time=3D=
1701.419..2989.243 rows=3D6891551 loops=3D1)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0Workers Planned: 4<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Laun=
ched: 4<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Sort =C2=A0(cost=
=3D671007.40..675341.67 rows=3D1733707 width=3D12) (actual time=3D1657.609.=
.1799.723 rows=3D1378310 loops=3D5)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0Sort Key: order_id DESC<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: external merge =C2=A0Disk: 38960kB<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Worker 0: =C2=A0S=
ort Method: external merge =C2=A0Disk: 31488kB<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Worker 1: =C2=A0Sort Method: external merge =
=C2=A0Disk: 36120kB<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0Worker 2: =C2=A0Sort Method: external merge =C2=A0Disk: 31368kB<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Worker 3: =C2=A0Sort Me=
thod: external merge =C2=A0Disk: 36152kB<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Parallel Seq Scan on order_history =C2=
=A0(cost=3D0.00..473993.00 rows=3D1733707 width=3D12) (actual time=3D0.041.=
.1211.485 rows=3D1378310 loops=3D5)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((is_false =3D 0) AND (sta=
te_id =3D ANY (&#39;{30,51,63,136,195,233,348}&#39;::integer[])))<br>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows =
Removed by Filter: 3268432<br>=C2=A0Planning Time: 0.405 ms<br>=C2=A0Execut=
ion Time: 3510.433 ms<br></div></div>

--00000000000025412b05dcb7b002--