Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nfe5L-00006i-Pr for pgsql-admin@arkaria.postgresql.org; Sat, 16 Apr 2022 08:47:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nfe5I-0003y5-OM for pgsql-admin@arkaria.postgresql.org; Sat, 16 Apr 2022 08:47:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nfe5I-0003xv-6T for pgsql-admin@lists.postgresql.org; Sat, 16 Apr 2022 08:47:12 +0000 Received: from mail.pscs.co.uk ([178.159.9.185]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nfe5E-0006Zq-Gl for pgsql-admin@lists.postgresql.org; Sat, 16 Apr 2022 08:47:10 +0000 Authentication-Results: mail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=pscs Received: from lmail.pscs.co.uk ([192.168.150.1]) by mail.pscs.co.uk ([192.168.150.185] running VPOP3) with ESMTPSA (TLSv1.3 TLS_AES_256_GCM_SHA384); Sat, 16 Apr 2022 09:47:04 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pscs.co.uk; q=dns/txt; s=lmail; h=From:To:Date:Message-ID:In-Reply-To:References:Subject:MIME-Version :Content-Type:Cc:Content-Transfer-Encoding:Reply-to:Sender; t=1650098514; x=1650703314; bh=qOSB4A8yBDncP2DzL8ctIsBnjt07LNMTGk246P5aaoE=; b=oLdDzO5E8Nkj7NhIT2boLxvpEo2CESP9VkG0YokIxM/mApi3pSqtl6T92v+TgGHdoiX6quTv rtB1d6gn+xXpZHPuJ7V1py64k0GV/l1SwWDDXy3ilDlEEej/KH80qFZ+AydW8D72S1q0E4WYfS 2XIASb71Iv/sgry46v5u4e+Fo= Authentication-Results: lmail.pscs.co.uk; spf=none; auth=pass (plain) smtp.auth=paul Received: from [10.144.61.180] ([82.132.240.51] (82-132-240-51.dab.02.net)) by lmail.pscs.co.uk ([192.168.150.70] running VPOP3) with ESMTPSA (TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384); Sat, 16 Apr 2022 09:41:53 +0100 From: Paul Smith To: Kenny Bachman , Date: Sat, 16 Apr 2022 09:41:50 +0100 Message-ID: <180318918b0.2923.9bfe8bcc586ac955e423c0e3d5444448@pscs.co.uk> In-Reply-To: References: User-Agent: AquaMail/1.35.0 (build: 103500130) Subject: Re: postgresql-14 slow query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="18031891beb4d7929234b14143" X-Authenticated-Sender: paul X-Server: VPOP3 Enterprise V8.3 - Registered X-Organisation: Paul Smith Computer Services X-VPOP3Tester: 12 345 X-Authenticated-Sender: pscs List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --18031891beb4d7929234b14143 Content-Type: text/plain; format=flowed; charset="us-ascii" Content-Transfer-Encoding: 8bit "for fun" try doing Set enable_seqscan=off And try the queries again. This will discourage it from doing sequential scans and use indexes if possible. If it still does a sequential scan, then there's some reason it can't use the indexes. If it uses indexes now, then the planner must think that the sequential scan would be quicker On 15 April 2022 22:00:07 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 -- Paul Smith Computer Services Tel: 01484 855800 Vat No: GB 685 6987 53 Sign up for news & updates at http://www.pscs.co.uk/go/subscribe --18031891beb4d7929234b14143 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
"for fun" try doing 

Set enable_seqscan=3Doff

<= /div>
And try the queries again. This will discourage it = from doing sequential scans and use indexes if possible. If it still does = a sequential scan, then there's some reason it can't use the indexes. If i= t uses indexes now, then the planner must think that the sequential scan w= ould be quicker 

On 15 April 2022 22:00:07 Kenny Bachman <kenny.bachman17@gmai= l.com> 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 t= akes 13 seconds if I create that index. Could you help me?

<= /div>
Thank you so much for your help.

SELECT= DISTINCT ON (order_history.order_id) order_id, order_history.creation_dat= e  AS c_date
FROM work.order_history WHERE (order_history.state_id= =3D ANY (ARRAY[30, 51, 63, 136, 195, 233, 348])) AND order_history.is_fal= se =3D 0
ORDER BY order_history.order_id DESC;

=
EXPLAIN ANALYZE output:

 Unique  (= cost=3D672007.46..1519683.55 rows=3D206423 width=3D12) (actual time=3D1701= .420..3439.095 rows=3D2049357 loops=3D1)
   ->  Gathe= r Merge  (cost=3D672007.46..1502346.48 rows=3D6934827 width=3D12) (ac= tual time=3D1701.419..2989.243 rows=3D6891551 loops=3D1)
    =      Workers Planned: 4
        &nbs= p;Workers Launched: 4
         ->  Sor= t  (cost=3D671007.40..675341.67 rows=3D1733707 width=3D12) (actual ti= me=3D1657.609..1799.723 rows=3D1378310 loops=3D5)
      =          Sort Key: order_id DESC
   =            Sort Method: external merge &nbs= p;Disk: 38960kB
               = Worker 0:  Sort Method: external merge  Disk: 31488kB
  =              Worker 1:  Sort Metho= d: external merge  Disk: 36120kB
         = ;      Worker 2:  Sort Method: external merge  Di= sk: 31368kB
               Work= er 3:  Sort Method: external merge  Disk: 36152kB
  &nbs= p;            ->  Parallel Seq Scan = on order_history  (cost=3D0.00..473993.00 rows=3D1733707 width=3D12) = (actual time=3D0.041..1211.485 rows=3D1378310 loops=3D5)
    =                  Filter: ((is= _false =3D 0) AND (state_id =3D ANY ('{30,51,63,136,195,233,348}'::integer= [])))
                  &n= bsp;  Rows Removed by Filter: 3268432
 Planning Time: 0.405 m= s
 Execution Time: 3510.433 ms


=09 =09
--

=09 =09
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53

=09 =09
--18031891beb4d7929234b14143--