Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vrw2b-00DjZ4-2J for pgsql-general@arkaria.postgresql.org; Mon, 16 Feb 2026 10:41:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vrw1b-001TVL-1D for pgsql-general@arkaria.postgresql.org; Mon, 16 Feb 2026 10:40:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vrw1a-001TTw-2x for pgsql-general@lists.postgresql.org; Mon, 16 Feb 2026 10:40:19 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vrw1X-00000000sDu-3ltS for pgsql-general@lists.postgresql.org; Mon, 16 Feb 2026 10:40:18 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-65c01595082so1064922a12.3 for ; Mon, 16 Feb 2026 02:40:16 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771238415; cv=none; d=google.com; s=arc-20240605; b=KJvOrAPe9fW3i+srTpZevhe7OGk2lmg3Mxi4++3UmwlzC+mbrSz0No6r1Pywkb7X3C R7/nOZoavgju0V9CK3S0UU1A030LQBtAsKmtMhZTkMLlDxbSRacLlb4QqinJ5IXWzMf2 E8zWPTxbLEj51tf1fYX1+KA+dSJyq8taJCwYzh6xKK2fkH4T0vOPfzV/BcqIjEMzCQpm pyozmiZVAelhquM1ubQKis+iPy1aFoD/auv0rqWQ1rPAsOkxQOyNgxr0fvxRzOTHkJ4N 5oo3mxXuZiXlFA+c98W4oxh1eHovOYz56WYTm89u4BVaSUASTrH9raEH1L4l7XVk4a/5 0j3Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=EZtHVD30ai03Yb/zgd9yeQJLZXH8uW+AjqeIY82Mjzo=; fh=wLWSUki3RvY4XywMwgmguYD8LLUUQQeH47gnBfBxI24=; b=S/E2Dww4jgmnifsgcsSUa5aH96aNgcLO4lkQAb/+yR7LRvkcv3lfoDg8a/OUZIBdwC XjUpuqG7d4+3O01t/eBAfZjBbXBWW+agppXyBFuNQ4yP4jkDNbU9UbYR7TBXOxeaxVf9 geJtwdGTTWoRl88flyN0Xs42ttTzzyz21zEM6Fv4lbMti1w/4auA8ZYJNxg7SrUd3Jrg yR2uVqRGiLrpTmtcuTFA1/1UAKAWGWQtSpW8WZhJth7EWVo0YyAHos8h6U48McJ37P1c 4JFwMSwpIuJ1gxBdm06KhPbli5nhlE+3NhfVBp4FiKJ4ZCp/zRyG0Ar5v65ahcZCbz7K nN/g==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1771238415; x=1771843215; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=EZtHVD30ai03Yb/zgd9yeQJLZXH8uW+AjqeIY82Mjzo=; b=hfrxL0r22vAtzXhp8onMOT2jkeRKXPtwXGJQgzLDjJAfWlejtMZ2/0GO84kwFO4mcH FjWyTOhqM0zuY4WvFgMBZii50RSWoZDgsgkk9fmKdWWLJG9SzXR+kBuaOoCdBGq2JKFj 5Kdyx0HLFghlKMHDbfC8mxioPHOcboDFdDEbGKYho46uPm3kBoHDtgtWnpYLMhYyxpVU MiC96edkrfGUx7Fq8SJEEvsj5LvCJXHQRyeBqxynyuW+R+hg6q6FD+oN52ljMWK569Vn f8cZq/+UhAdS73jjsdXSNG8OAxFUU6dzKRC7GPAyi634lDjuuloq9mI0js0ke/5dmquE FBeA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771238415; x=1771843215; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=EZtHVD30ai03Yb/zgd9yeQJLZXH8uW+AjqeIY82Mjzo=; b=IjoMxClKp/lOOWWELunaSB+Hklx5pZDMpzRNpCxsPFFmp+HA2fJReuunLY/OH50/W0 iHbStLvBLTU9lJB9qIDbz+6G0WRxDjomQt+ZihGp1kFhWULmnkSqbAPMxYHnv2K1oYxO 6GLr2jWf9zg0iCW0gzb4R5UJhFiIyIFMHoY9njAX9vhVH/7XqpYDrLpgmkZy9BGjWawe aOuimEVW2vzZCasU82SedDyQDMHU/fNwL8U0zBKUhofKTKUGG2FS45PtwGYLuMMrfnAe Mf/TnsfYE5y44b7KkYA/LzFyvSN4uirWnNAUcArN4yJit7jU94FF2+++cvF/ilr8KfQa wOug== X-Forwarded-Encrypted: i=1; AJvYcCU41FKhlVxMguLZEc1/wxJNRs7EnNCaWIYvMK0Phy3x3Yze4GYa6DY7Vz0LsRA3nYwlxRFgnzvL0GIZL7eZ@lists.postgresql.org X-Gm-Message-State: AOJu0YwJ94fHVy5ftjEzrsZNgCvgf2k80uhJ/mppuTmwdV3JZA1Nint6 10g6S934+wdnY3d0xFt/wepBMRkPxRpQU2k5xR06tnSgZ0S5iDVZFQJaPM08NsDwdyEX/vHsl+O EljSLnDkb8rJtAJzHouIknJcG3I2RJCf3tBZj X-Gm-Gg: AZuq6aJKu6Qd61fu/UZbh7/6tzFEi/EFv3CcKyGJeZzuJuHXcKZuomWJ2CibOpIZVYo /rnBg0ppA+fddsRgzNzVK5YmbgT2UxlR+khhvGYCHshGPBCdq3l/m86r00+5sHrz6w99x1hYFb/ bJ88td6+QOtB496xOrgHAW86xCoqz7mc3Q2wxzNepPKqhFNkf2uF7gqRYYLOkb5iSdTz/uP6gth 94bSYfwDQU5XCQPiQnxVtO6CwMIxWqQiYUZHVLvd/TOe3NszOHWMqE1Fve5M371cC0FfORLoMgV cqi5LIFCMlWFLBU3i/j0MAVlI0tnziz+FK85J9+aHg== X-Received: by 2002:a05:6402:2747:b0:65c:4f5:281a with SMTP id 4fb4d7f45d1cf-65c04f5323dmr1479685a12.27.1771238415143; Mon, 16 Feb 2026 02:40:15 -0800 (PST) MIME-Version: 1.0 References: <818d0359d8b629a80b55b2e068dab958fc8e0a2a.camel@cybertec.at> <2fa62200cf92dc03dff20f8a42e45bb30fc40f35.camel@cybertec.at> In-Reply-To: <2fa62200cf92dc03dff20f8a42e45bb30fc40f35.camel@cybertec.at> From: yudhi s Date: Mon, 16 Feb 2026 16:09:58 +0530 X-Gm-Features: AaiRm50_sywYbxc-8gmChE2kksQN6AoGDXPLUmILczqvbin3agkFqafzwANThsA Message-ID: Subject: Re: Question on execution plan and suitable index To: Laurenz Albe Cc: Ron Johnson , Adrian Klaver , Nisarg Patel , pgsql-general Content-Type: multipart/alternative; boundary="0000000000008c152d064aee94be" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008c152d064aee94be Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Feb 16, 2026 at 3:24=E2=80=AFPM Laurenz Albe wrote: > On Mon, 2026-02-16 at 14:43 +0530, yudhi s wrote: > > On Mon, Feb 16, 2026 at 2:29=E2=80=AFPM Laurenz Albe > wrote: > > > On Mon, 2026-02-16 at 00:34 +0530, yudhi s wrote: > > > > It's postgres version 17. We are having a critical UI query which > runs for ~7 seconds+. The requirement is to bring down the response time > within ~1 sec. Now in this plan , If i read this correctly, the below > section is consuming a significant amount of resources and should be > addressed. i.e. "Full scan of table "orders" and Nested loop with > event_audit_log table". > > > > > > > > Below is the query and its complete plan:- > > > > > https://gist.github.com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9 > > > > > > > > I am a bit new to the indexing strategy in postgres. My question is= , > what suitable index should we create to cater these above? > > > > > > > > 1)For table event_audit_log:- Should we create composite Index on > column (request_id,created_at,event_comment_text) or should we create the > covering index i.e. just on two column (request_id,created_at) with > "include" clause for "event_comment_text". How and when the covering inde= x > indexes should be used here in postgres. Want to understand from experts? > > > > 2)Similarly for table orders:- Should we create a covering index on > column (entity_id,due_date,order_type) with include clause > (firm_dspt_case_id). Or just a composite index > (entity_id,due_date,order_type). > > > > 3)Whether the column used as range operator (here created_at or > due_date) should be used as leading column in the composite index or is i= t > fine to keep it as non leading? > > > > > > > > -> Nested Loop (cost=3D50.06..2791551.71 rows=3D3148 width=3D19) = (actual > time=3D280.735..7065.313 rows=3D57943 loops=3D3) > > > > Buffers: shared hit=3D10014901 > > > > -> Hash Join (cost=3D49.49..1033247.35 rows=3D36729 width=3D8) = (actual > time=3D196.407..3805.755 rows=3D278131 loops=3D3) > > > > Hash Cond: ((ord.entity_id)::numeric =3D e.entity_id) > > > > Buffers: shared hit=3D755352 > > > > -> Parallel Seq Scan on orders ord (cost=3D0.00..1022872.54 > rows=3D3672860 width=3D16) (actual time=3D139.883..3152.627 rows=3D294467= 1 loops=3D3) > > > > Filter: ((due_date >=3D '2024-01-01'::date) AND (due_date <=3D > '2024-04-01'::date) AND (order_type =3D ANY ('{TYPE_A,TYPE_B}'::text[]))) > > > > Rows Removed by Filter: 6572678 > > > > Buffers: shared hit=3D755208 > > > > > > You are selecting a lot of rows, so the query will never be really > cheap. > > > But I agree that an index scan should be a win. > > > > > > If the condition on "order_type" is always the same, a partial index > is ideal: > > > > > > CREATE INDEX ON orders (due_date) WHERE order_type IN ('TYPE_A', > 'TYPE_B'); > > > > > > Otherwise, I'd create two indexes: one on "order_type" and one on > "due_date". > > > > Version is 17.7. Below is the table definitions as i pulled from Dbeave= r > tool:- > > > > > https://gist.github.com/databasetech0073/f22d95de18dc3f1fa54af13e7fd2ce9e > > > > The Order_type will be TYPE_A and TYPE_B in most of the cases. And belo= w > is the distribution. > > So , it looks like the index on this column will not help much. Correct > me if I'm wrong. > > > > TYPE_A 25 Million > > TYPE_B 2 Million > > TYPE_C 700K > > TYPE_D 200K > > TYPE_E 6k > > No, you are right about that. > > > I am wondering why the already existing index on column "due_date" of > table "order" is not > > getting used by the optimizer? Should we also add the column "entity_id= " > to the index too? > > Seeing that your execution plan is incomplete, it is hard to say anything > about that. > The scans of "entities" are missing, as is the UNION. > > > And, Yes there are differences in data types of the "entity_id" for > columns of table "order" > > and "entity". We need to fix that after analyzing the data. > > > > Also the highlighted Nested loop above shows ~10M shared hits (which > will be ~70GB+ if we > > consider one hit as an 8K block). So does that mean , apart from the > Full scan on the "order" > > table , the main resource consuming factor here is the scanning of > "event_audit_log". > > Correct. > > Yours, > Laurenz Albe > Hi, I have updated the plan below. While trying to replace actual binds and the objects with sample names some lines got missed initially it seems. https://gist.github.com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9 --0000000000008c152d064aee94be Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Mon, Feb 16, 202= 6 at 3:24=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2026-02-16 at 14:43 +0530, yudhi s= wrote:
> On Mon, Feb 16, 2026 at 2:29=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Mon, 2026-02-16 at 00:34 +0530, yudhi s wrote:
> > > It's postgres version 17. We are having a critical UI qu= ery which runs for ~7 seconds+. The requirement is to bring down the respon= se time within ~1 sec. Now in this plan , If i read this correctly, the bel= ow section is consuming a significant amount of resources and should be add= ressed. i.e. "Full scan of table "orders" and Nested loop wi= th event_audit_log table".
> > >
> > > Below is the query and its complete plan:-=C2=A0
> > > https://gist= .github.com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9
> > >
> > > I am a bit new to the indexing strategy in postgres. My ques= tion is, what suitable index should we create to cater these above?
> > >
> > > 1)For table event_audit_log:- Should we create composite Ind= ex on column (request_id,created_at,event_comment_text) or should we create= the covering index i.e. just on two column (request_id,created_at) with &q= uot;include" clause for "event_comment_text". How and when t= he covering index indexes should be used here in postgres. Want to understa= nd from experts?=C2=A0
> > > 2)Similarly for table orders:- Should we create a covering i= ndex on column (entity_id,due_date,order_type) with include clause (firm_ds= pt_case_id). Or just a composite index (entity_id,due_date,order_type).
> > > 3)Whether the column used as range operator (here created_at= or due_date) should be used as leading column in the composite index or is= it fine to keep it as non leading?
> > >
> > > -> =C2=A0Nested Loop =C2=A0(cost=3D50.06..2791551.71 rows= =3D3148 width=3D19) (actual time=3D280.735..7065.313 rows=3D57943 loops=3D3= )
> > > =C2=A0=C2=A0Buffers: shared hit=3D10014901
> > > =C2=A0=C2=A0-> =C2=A0Hash Join =C2=A0(cost=3D49.49..10332= 47.35 rows=3D36729 width=3D8) (actual time=3D196.407..3805.755 rows=3D27813= 1 loops=3D3)
> > > =C2=A0Hash Cond: ((ord.entity_id)::numeric =3D e.entity_id)<= br> > > > =C2=A0Buffers: shared hit=3D755352
> > > =C2=A0-> =C2=A0Parallel Seq Scan on orders ord =C2=A0(cos= t=3D0.00..1022872.54 rows=3D3672860 width=3D16) (actual time=3D139.883..315= 2.627 rows=3D2944671 loops=3D3)
> > > =C2=A0=C2=A0Filter: ((due_date >=3D '2024-01-01':= :date) AND (due_date <=3D '2024-04-01'::date) AND (order_type = =3D ANY ('{TYPE_A,TYPE_B}'::text[])))
> > > =C2=A0=C2=A0Rows Removed by Filter: 6572678
> > > =C2=A0=C2=A0Buffers: shared hit=3D755208
> >
> > You are selecting a lot of rows, so the query will never be reall= y cheap.
> > But I agree that an index scan should be a win.
> >
> > If the condition on "order_type" is always the same, a = partial index is ideal:
> >
> > =C2=A0 =C2=A0CREATE INDEX ON orders (due_date) WHERE order_type I= N ('TYPE_A', 'TYPE_B');
> >
> > Otherwise, I'd create two indexes: one on "order_type&qu= ot; and one on "due_date".
>
> Version is 17.7. Below is the table definitions as i pulled from Dbeav= er tool:-
>
> https://gist.github.co= m/databasetech0073/f22d95de18dc3f1fa54af13e7fd2ce9e=C2=A0
>
> The Order_type will be TYPE_A and TYPE_B in most of the cases. And bel= ow is the distribution.
> So , it looks like the index on this column will not help much. Correc= t me if I'm wrong.
>
> TYPE_A=C2=A0 25 Million
> TYPE_B=C2=A0 2 Million
> TYPE_C=C2=A0 700K
> TYPE_D=C2=A0 200K
> TYPE_E=C2=A0 6k

No, you are right about that.

> I am wondering why the already=C2=A0existing index on column "due= _date" of table "order" is not
> getting used by the optimizer? Should we also add the column "ent= ity_id" to the index too?

Seeing that your execution plan is incomplete, it is hard to say anything a= bout that.
The scans of "entities" are missing, as is the UNION.

> And, Yes there are differences in data types of the "entity_id&qu= ot; for columns of table "order"
> and "entity". We need to fix that after analyzing the data.<= br> >
> Also the highlighted Nested loop above shows ~10M shared hits (which w= ill be ~70GB+ if we
> consider one hit as an 8K block). So does that mean , apart from the F= ull scan on the "order"
> table , the main resource consuming factor here is the scanning of &qu= ot;event_audit_log".

Correct.

Yours,
Laurenz Albe

H= i,
I have updated the plan below.= While trying to replace actual binds and the objects with sample=C2=A0name= s some lines got missed initially=C2=A0it seems.

--0000000000008c152d064aee94be--