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 1nfZpc-0006TM-5S for pgsql-admin@arkaria.postgresql.org; Sat, 16 Apr 2022 04:14:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nfZpa-0007w4-39 for pgsql-admin@arkaria.postgresql.org; Sat, 16 Apr 2022 04:14:42 +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 1nfZpZ-0007vu-1U for pgsql-admin@lists.postgresql.org; Sat, 16 Apr 2022 04:14:41 +0000 Received: from mail-lf1-x134.google.com ([2a00:1450:4864:20::134]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nfZpW-0004cQ-DV for pgsql-admin@lists.postgresql.org; Sat, 16 Apr 2022 04:14:39 +0000 Received: by mail-lf1-x134.google.com with SMTP id y32so16586581lfa.6 for ; Fri, 15 Apr 2022 21:14:38 -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=AJLLpmZuvbvIeLO0RrwvJD17nQHWj/MwZYpfHfLSvug=; b=I0v3AM6PYCRg4SvMN9U/AK6KKUZBYHhZYkmB4V1/V8OZ+OLBMaRY3RYRoPCx6tuU1e c8SbncMuKg299C/kUngfqx19N1unfV1I21QJLKkWmK0hPwHkbDARlkng4Qb7vOQXp2ln Uu9Q1/i6ZHgD6uQYnqUeYUMtpek++Pb7PNgE75Gon8KilhI8Y6zgUYV/oQglvHRyvKSl A5nXJKOO8Tlr55UJYda6xh1hi4P7NaCzjBRr8amOlXPkFHfOoO7hqlgaJeWQqmDd1ytk E1bJMm62PJPeLj1KGMW6ISDYFZ8kAlXpbA51N6eLHunBm6JRXfSy6A7/jKjPi86iq63e nHPA== 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=AJLLpmZuvbvIeLO0RrwvJD17nQHWj/MwZYpfHfLSvug=; b=CMu9Ri0CrUuwWwEvNmDX9dmcj99qbmumz1DeMYh799wjifuYzhM/KgqUFnwvIZE2zi EgBhqN/eHhUJzGDwv86/1CHiqmXQMz4yKMVOsPG+elTFk4PhGq2pQOUKRQoKEcWw7qks e9BUxttd8CFtgyB6JHDEn1qe4gcYBgqCuOMexPX6dfDo7sftx+jemJIU3onH3x4Jaahk sO4A7XjsgV8tDidJUh2IeXkV9IalKbeNHiq6cK6sU9ZQWiVOR72wDpn/zT4e/uoCdn6j 7V2vfZ/ZxQQlyIa5DbaOrufnnFfNLjoiBhxgHxC9GuwGOWH3VFpcxrl+gGb20xk1undi mOXw== X-Gm-Message-State: AOAM532II+PyVLoUHJu2LgMo8fzkkRXx9rw3zkLI5Y7hvf2kfPOwGKRz IxgF04eF6uromq6YZOqcxilIRP8Vg3bl91j6g0U= X-Google-Smtp-Source: ABdhPJz14Db5vI1mPIywEErduA+TlW+zPbrzXk5f4REY1wldjH8yJOgCFaRNJvqaaAnPW0xY/y5CimKqocOrMn1cE98= X-Received: by 2002:a05:6512:318a:b0:44a:be25:7082 with SMTP id i10-20020a056512318a00b0044abe257082mr1363565lfe.439.1650082475857; Fri, 15 Apr 2022 21:14:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Bhupendra Babu Date: Fri, 15 Apr 2022 21:14:25 -0700 Message-ID: Subject: Re: postgresql-14 slow query To: Kenny Bachman Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d2dba205dcbdc3b8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d2dba205dcbdc3b8 Content-Type: text/plain; charset="UTF-8" Add is_false as 2nd column in index. So your index will be state_id,is_false ,ordrr_id with or without desc. Since your plan shows lot of data to filter. New index wi help filter less rows. Of course you need to check and analyze the volum of how your data is organized. On Fri, Apr 15, 2022, 1: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) > -> 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 > --000000000000d2dba205dcbdc3b8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Add is_false as 2nd column in index.
So your index will be state_id,is_false ,ordrr_id with or without= desc.
Since your plan shows lot of data to filter. = New index wi help filter less rows. Of course you need to check and analyze= the volum of how your data is organized.

On Fri, Apr 15, 2022, 1= :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 index for (state_id, order_id desc).= The following explain analyze output is without=C2=A0an=C2=A0index. It tak= es 13 seconds if I create that index. Could you help me?

Thank you so much for your help.

SELECT DIS= TINCT ON (order_history.order_id) order_id, order_history.creation_date =C2= =A0AS c_date
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
ORDER BY order_history.order_id DESC;

EXP= LAIN ANALYZE output:

=C2=A0Unique =C2=A0(cost=3D67= 2007.46..1519683.55 rows=3D206423 width=3D12) (actual time=3D1701.420..3439= .095 rows=3D2049357 loops=3D1)
=C2=A0 =C2=A0-> =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)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Workers Planned: 4
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Laun= ched: 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 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
=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=A0Worker 3: =C2=A0Sort Me= thod: external merge =C2=A0Disk: 36152kB
=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..473993.00 rows=3D1733707 width=3D12) (actual time=3D0.041.= .1211.485 rows=3D1378310 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 (sta= te_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=A0Execut= ion Time: 3510.433 ms
--000000000000d2dba205dcbdc3b8--