Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Doug Reynolds <mav@wastegate.net>
Mime-Version: 1.0 (1.0)
Subject: Re: postgresql-14 slow query
Date: Fri, 15 Apr 2022 22:10:33 +0000
Message-ID: 
 <010001802f472693-b1f90032-4f2d-4ba4-8554-4732c19ca814-000000@email.amazonses.com>
References: 
 <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
Cc: pgsql-admin@lists.postgresql.org
In-Reply-To: 
 <CAC0w7L+cxDkvgeM9vA_pPTAQ0pMo4PLuh9s-rCyR9AoLnYTL3A@mail.gmail.com>
To: Kenny Bachman <kenny.bachman17@gmail.com>
Feedback-ID: 
 1.us-east-1.L+CISTGIImABHDx8bBKKEgULZ36xzLwmtjped/xJNPU=:AmazonSES
Archived-At: 
 <https://www.postgresql.org/message-id/010001802f472693-b1f90032-4f2d-4ba4-8554-4732c19ca814-000000%40email.amazonses.com>
Precedence: bulk

I would move the WHERE filter into a subquery and then run the ORDER BY/DIST=
INCT on the subquery result.  I like to avoid DISTINCT on queries using an  A=
NTIJOIN or SEMIJOIN if possible.  It is hard to recommend without knowing yo=
ur data.

Sent from my iPhone

> On Apr 15, 2022, at 4:59 PM, Kenny Bachman <kenny.bachman17@gmail.com> wro=
te:
>=20
> =EF=BB=BF
> Hello Team,
>=20
> 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 a=
n index. It takes 13 seconds if I create that index. Could you help me?
>=20
> Thank you so much for your help.
>=20
> SELECT DISTINCT ON (order_history.order_id) order_id, order_history.creati=
on_date  AS c_date
> FROM work.order_history WHERE (order_history.state_id =3D ANY (ARRAY[30, 5=
1, 63, 136, 195, 233, 348])) AND order_history.is_false =3D 0
> ORDER BY order_history.order_id DESC;
>=20
> EXPLAIN ANALYZE output:
>=20
>  Unique  (cost=3D672007.46..1519683.55 rows=3D206423 width=3D12) (actual t=
ime=3D1701.420..3439.095 rows=3D2049357 loops=3D1)
>    ->  Gather Merge  (cost=3D672007.46..1502346.48 rows=3D6934827 width=3D=
12) (actual time=3D1701.419..2989.243 rows=3D6891551 loops=3D1)
>          Workers Planned: 4
>          Workers Launched: 4
>          ->  Sort  (cost=3D671007.40..675341.67 rows=3D1733707 width=3D12)=
 (actual time=3D1657.609..1799.723 rows=3D1378310 loops=3D5)
>                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=3D0.00..47399=
3.00 rows=3D1733707 width=3D12) (actual time=3D0.041..1211.485 rows=3D137831=
0 loops=3D5)
>                      Filter: ((is_false =3D 0) AND (state_id =3D ANY ('{30=
,51,63,136,195,233,348}'::integer[])))
>                      Rows Removed by Filter: 3268432
>  Planning Time: 0.405 ms
>  Execution Time: 3510.433 ms