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 1nfasv-0002ca-Dl for pgsql-admin@arkaria.postgresql.org; Sat, 16 Apr 2022 05:22:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nfass-0001yU-Ja for pgsql-admin@arkaria.postgresql.org; Sat, 16 Apr 2022 05:22:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nfass-0001yK-6D for pgsql-admin@lists.postgresql.org; Sat, 16 Apr 2022 05:22:10 +0000 Received: from mail-qk1-x72d.google.com ([2607:f8b0:4864:20::72d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nfasn-0008VI-Ep for pgsql-admin@lists.postgresql.org; Sat, 16 Apr 2022 05:22:08 +0000 Received: by mail-qk1-x72d.google.com with SMTP id e128so5760964qkd.7 for ; Fri, 15 Apr 2022 22:22:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=sd584NVGxOF4FAmH7yhywsJ5oEIcpi78ZCUZas7EEhY=; b=qM7+3H747/pu7IMromNa9zogLiURqFJC5eYksmk4fncgro0/Kr0P6ymi/Fuf0XBILM 6JNT3YyDrtlXFJbSfIlaYP9B/bZdLPe4ozws00G8vHhI2Vr8gw4U9a/s9q8jSOB+QNn6 c6Mj/QEJp8a0sFIIt/PbcnPdiq63BI17kcsLjpTuHcK6XLAtar7DQkhle+7ub9ghysq1 uIabqnDyGh65oWn+v8foIXpEDopTa7M80ep4TXhLQLYAbjUBQbEimIBWVpVLueOnihof ouh+xFNXoOZCoLA56gqVNWRicmveQ8cwgOEKbSPHorQ+5bX58fov7lvCJ0vwJ2SlVJeq /rtg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=sd584NVGxOF4FAmH7yhywsJ5oEIcpi78ZCUZas7EEhY=; b=JQO3OBbIgNVeOLxfd1Q/hwR4u5SPKTcSdrwaxeRH/v3/JC4ACr67OwbwAe7eXZiWh4 Hi3mjrJ2WUeTp0ulefm/7k5lHlLJ5D9ltf9MpMRHaHro6ZxCKL2c5wUHkEHNhXxiLD5b +5Ox7ItZwRKDffbn88pm7svyMaTRgKdkU2rGca+jU+fe6cMTwFsl3saOKeZAd9V2tP/P rImrnDBoKliKTwDmqYK7LzvU0lYxoCfCWtOTWN82LsvxsF9ybNAO64PDnAo99+GSYZ7U WXJbMmlLrURg7gMUfdGfZSWtsN8lLjmZkO71IzcYvtrZkzkKehrw5J3xxGAViSjVwscx fsnw== X-Gm-Message-State: AOAM533felGdsP6RVGCQqvK5QPtvjiafd1sYBTuEnoNQtamlpsbuMNap J6wAmgF16mrEoM+b1LWsxnaCB0bwOzzFS8O4JGI= X-Google-Smtp-Source: ABdhPJyIsDRCCEn439AyCn3IBfaX8eCwC/6+hlghmkhEdceHpjEEc5jt/6Kl26EBK5K+Eg+6D75XyltQIa7j6qZWaWs= X-Received: by 2002:a37:bc1:0:b0:69d:ea33:7f2e with SMTP id 184-20020a370bc1000000b0069dea337f2emr1199742qkl.74.1650086523745; Fri, 15 Apr 2022 22:22:03 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Avinash Vallarapu Date: Sat, 16 Apr 2022 01:21:52 -0400 Message-ID: Subject: Re: postgresql-14 slow query To: Kenny Bachman Cc: pgsql-admin Content-Type: multipart/alternative; boundary="00000000000018b76405dcbeb55e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000018b76405dcbeb55e Content-Type: text/plain; charset="UTF-8" While I do not have much clarity into the schema and a lot of other insights, you might want to see my points below. On Fri, Apr 15, 2022 at 4:59 PM 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) > Planner estimates vs actual rows seems to have some variation. Firstly, have you manually updated statistics and verified if estimates can be almost near to actual ? VACUUM ANALYZE work.order_history; -> 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 > Again, I am not suggesting a query rewrite or a change in schema (indexing or etc) as I do not have much clarity and thus not discussing it here. However, setting a session level work_mem of 40MB should show some immediate difference. SET work_mem TO '40MB'; -> 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 > -- Regards, Avinash Vallarapu CEO, MigOps, Inc. www.migops.com --00000000000018b76405dcbeb55e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
While I do not have much clarity into the schema and = a lot of other insights, you might want to see my points below.

On Fri, Apr = 15, 2022 at 4:59 PM Kenny Bachman <kenny.bachman17@gmail.com> wrote:
Hello Team,

How can I tune this query? It got even slower when I created the inde= x for (state_id, order_id desc). The following explain analyze output is wi= thout=C2=A0an=C2=A0index. 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 =C2=A0AS c_date
FROM work.order_history WHE= RE (order_history.state_id =3D ANY (ARRAY[30, 51, 63, 136, 195, 233, 348]))= AND order_history.is_false =3D 0
ORDER BY order_history.order_id DESC;<= br>

EXPLAIN ANALYZE output:

=C2=A0Unique =C2=A0(cost=3D672007.46..1519683.55 rows=3D206423 width=3D1= 2) (actual time=3D1701.420..3439.095 rows=3D2049357 loops=3D1)
Planner estimates vs actual rows seems to have some va= riation.
Firstly, have you manually updated statistics and verifi= ed if estimates can be almost near to actual ?=C2=A0

VACUUM ANALYZE work.order_history;

=C2=A0 =C2=A0-> = =C2=A0Gather Merge =C2=A0(cost=3D672007.46..1502346.48 rows=3D6934827 width= =3D12) (actual time=3D1701.419..2989.243 rows=3D6891551 loops=3D1)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Planned: 4
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Workers Launched: 4
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->= =C2=A0Sort =C2=A0(cost=3D671007.40..675341.67 rows=3D1733707 width=3D12) (= actual time=3D1657.609..1799.723 rows=3D1378310 loops=3D5)
=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Key: order_id DESC
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: external merg= e =C2=A0Disk: 38960kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Worker 0: =C2=A0Sort Method: external merge =C2=A0Disk: 31488kB
= =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
=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
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= Worker 3: =C2=A0Sort Method: external merge =C2=A0Disk: 36152kB
Again, I am not suggesting a query rewrite=C2=A0or a = change in schema (indexing or etc) as I do not have much clarity and thus n= ot discussing it here.
However, setting a session level work_mem = of 40MB should show some immediate difference.

SET= work_mem TO '40MB';
<Run EXPLAIN ANALYZE again and ch= eck>

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0Parallel Seq Scan on order_history =C2=A0(cost=3D0.00..47= 3993.00 rows=3D1733707 width=3D12) (actual time=3D0.041..1211.485 rows=3D13= 78310 loops=3D5)
=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 (state_id =3D ANY ('= {30,51,63,136,195,233,348}'::integer[])))
=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
=C2=A0Planning Time: 0.405 ms
=C2=A0Execution Time: 3510.433= ms


--
Regards,
Avinash Vallar= apu
CEO,
MigOps, Inc.
--00000000000018b76405dcbeb55e--