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 1vrjox-003R1M-2a for pgsql-general@arkaria.postgresql.org; Sun, 15 Feb 2026 21:38:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vrjow-002TiH-2N for pgsql-general@arkaria.postgresql.org; Sun, 15 Feb 2026 21:38:26 +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 1vrjow-002Ti9-0v for pgsql-general@lists.postgresql.org; Sun, 15 Feb 2026 21:38:26 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vrjot-00000000mxK-33te for pgsql-general@lists.postgresql.org; Sun, 15 Feb 2026 21:38:25 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-65a35fc8007so4305856a12.1 for ; Sun, 15 Feb 2026 13:38:24 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771191502; cv=none; d=google.com; s=arc-20240605; b=eZUQwktElpAv3TOjOHD2usut1o5tYE5oJwO5nWsMhHO63ThzKv6EJoU0lAafmd8TFo V3Y+tHSZpm/X0nuQAgvQ6/3sLClu1n5v/6Q5AeBtUXioF0Jqh5gzDoOsrcvFfWjI7GQM mvnwQQ29cTmB+kt26AV+7PC0yx/Ydte77DSZJfjEiqjT0uvxBqkfpOYf9ythpFK/Ben3 ibDOd/BHGWrAaPlU6pHdzuPt7j/DkppEt1JFsxXkNiR78HOH9LU1ucTSfo8NDoQwUeKV g7+EA2U8kFPdfmUMUD9NaCmNndqmUe3hrcYOUBLFspp9Dap3T/KrPFeXHydx8YWKsvau gDvA== 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=7i1CMBRN17yzIZYTdhp/gMy8rRVarXm9dEHCWD8pHwo=; fh=4gyGqs8ojW8OSSnBFtwu1thIyWketpSGDEtEjaIl6Zw=; b=lj7vHrbnkFkGBdzoWD4ZHoK6dokkCxrOlqBvJ9rG3q8LqA7rKpGt7NAyXcGmCrYpjz 9479mKgRz9parCBRSMZOlEKF6eHAh/sSPma+QUeUA3Ip3AFCAG4lS0uLCERNshl5B/u2 08ScvhvEsCriJbJE/9v/6an6NOVhAPIPkoYhZqvI0ILlsF/6qQLFUdctdUG7KubofPIc dcStrVRos0hFWD3fEKPPBI6jFiNNkDlMfu6yoGroJKKv5MylNHlhbCFO3tPuQ4iVW2ER 7lzbWd3nWeODBP7Z2Pfz2TZRym5Wpa/ne8o1IIqKTarKgyvCBgky+OyHZUzC8a9JiTjn rENg==; 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=1771191502; x=1771796302; 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=7i1CMBRN17yzIZYTdhp/gMy8rRVarXm9dEHCWD8pHwo=; b=TBlDVGNfFn6akeIYF/SpiGxQpDJmBPSGv6NDRlhug0T8XhJcQxfHftIB5D500TbIZJ cwzSZw21amMFmhgVqGrKmMqcFu/JCp1LrwmUsWzpx6/uIwbNSMX5QXoXumAiG5hXAFG+ pJUxAprt+U9xnLBIZ9Lz8Ewc3azmRs5Kv2I7F9eTmqmbgzWVvT+6WCST0pvVC3NKkPH1 /hU+d20OsIz5BmdzUYwZ4zqq89xOCa5wTeYSu+96gsP4LJjny5JVl1kV6QB0OACsJmqh qgvq7uK/2dcq85mqiEoBcgiKqY46f7wmxCO4U6VRkmg71aHT8HHNL7pzEkXKBWppLZXT g86Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771191502; x=1771796302; 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=7i1CMBRN17yzIZYTdhp/gMy8rRVarXm9dEHCWD8pHwo=; b=TTl3jlQSbdjY0u6WY5AgE8qXMIVQ+xOzd6NJUc9NxVJUNQWK7Y1gQNKBXtklMn11gE hZPwPMRYdiY4UQIPtgC7BHv8e76gLrf5J6qiNxCg5GKdw5xxeBYBIHvV2tU1zBGvdjB0 bAXhDEcSPSAHHiyhc+aGfj/Dlfz1vrfLosREG6nFzGx1KKeu6ANnF8s8Z7yvTt1+rA0e 3ii3DUP9N7sJeybg3Iu9Nhn10b5u1VmlnSAxpBCfB3NR8yavaSnuBvCPaIjK9dfzbPbK lKPVCD8wqOMJuZPAGW27F9KO51nH0tSYIBoJMG6EcKYCZ3YkqX5P9xzt+KnZuAPYPiuZ TZUA== X-Forwarded-Encrypted: i=1; AJvYcCW5coEdyMJhrDakDtjzwyo5rJimZF0tsHEuhYk/3qHMFF90B0Bgm1QlUSQnHzPXNCw/ccAmdxgr4VZaifFk@lists.postgresql.org X-Gm-Message-State: AOJu0YyUwHgYstwr5dXCyS9/NQZkjr1HBKhv/W2Zz8zCwapiOP8RUqex KiWmxMjT8QoX3TYI576YX9d3GyXe/WCRAUGixN7rkJItMJf+7SocrudbR15bzHLBqYLGi2qzTiM T7faO6eEgsG3pLoLrQx2H8XTDmi9wpe0= X-Gm-Gg: AZuq6aKiFrMHewYy32EdB6iDkWuY0LGIfAb+tDeCmGYOIIFpp5H3w10lWQCWDWvjuny BhT2aM9BqYcEgd9yNH0j1wZhFakWhHZP9X7zvD3a/uzKFyw8a5HWORNFEMwJlwL7YhEM7XhLEQB QUhCOOpaA5GgCNz+gkrrFemcgFeav7Ay9X7qAigLRBYW0WYixvSMCfHizruLuVNPY1clH1KHn3f r9gRv8fqMavlxflnJP8PGe0bfkZ6+gKbOBtc2WJQ24tsPOteOGq3FSsZXNHvvgC1xZlorNwd1eZ X3UsWEIMNWja8xQXfqp+uVjDnLv1kIq6Jnzt2UbZ X-Received: by 2002:a05:6402:51d0:b0:658:377e:f05d with SMTP id 4fb4d7f45d1cf-65bb13a2776mr3746755a12.24.1771191501585; Sun, 15 Feb 2026 13:38:21 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Nisarg Patel Date: Sun, 15 Feb 2026 16:38:10 -0500 X-Gm-Features: AaiRm52wZUmhnVJKdaS5v59k6VWE6LtKrYN_kW9cYfCUY9S-MWkj0W0Pp8wORzE Message-ID: Subject: Re: Question on execution plan and suitable index To: Adrian Klaver Cc: yudhi s , pgsql-general Content-Type: multipart/alternative; boundary="00000000000047ff92064ae3a82f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000047ff92064ae3a82f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I would definitely recommend to focus on this section: 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 You can certainly try composite index or partial index for order_type. Thank you, Nisarg On Sun, Feb 15, 2026, 3:51=E2=80=AFPM Adrian Klaver wrote: > On 2/15/26 11:04, yudhi s wrote: > > Hi, > > 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". > > For a start: > > 1) Supply the complete schema for the tables involved. > > 2) Also what is the minor version you are using e.g the x in 17.x? > > I also recommend reading: > > https://wiki.postgresql.org/wiki/Slow_Query_Questions > > > > > *Below is the query and its complete plan:- * > > https://gist.github.com/databasetech0073/ > > f564ac23ee35d1f0413980fe4d00efa9 > 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 colum= n > > (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 > > index 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 > > it fine to keep it as non leading? > > > > -> Nested Loop (cost=3D50.06..2791551.71 rows=3D3148 width=3D19) (act= ual > > time=3D280.735..7065.313 rows=3D57943 loops=3D3) > > Buffers: shared hit=3D10014901 > > -> Hash Join (cost=3D49.49..1033247.35 rows=3D36729 width=3D8) (act= ual > > 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=3D36= 72860 > > width=3D16) (actual time=3D139.883..3152.627 rows=3D2944671 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 > > > > > > Regards > > Yudhi > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > --00000000000047ff92064ae3a82f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I would definitely recommend to focus on this sectio= n:
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

You can= certainly try composite index or partial index for order_type.

Thank you,
N= isarg


On Sun, Feb 15, 2026, 3:51=E2=80=AFPM Adrian= Klaver <adrian.klaver@akla= ver.com> wrote:
On 2/15/26 1= 1:04, yudhi s wrote:
> Hi,
> 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 <= br> > addressed. i.e. "Full scan of table "orders" and Nested= loop with
> event_audit_log table".

For a start:

1) Supply the complete schema for the tables involved.

2) Also what is the minor version you are using e.g the x in 17.x?

I also recommend reading:

https://wiki.postgresql.org/wiki/Slo= w_Query_Questions

>
> *Below is the query and its complete plan:- *
> https://gist.github.com/databasetech0073/
> f564ac23ee35d1f0413980fe4d00efa9 <https://gist.github.com= /
> databasetech0073/f564ac23ee35d1f0413980fe4d00efa9>
>
> I am a bit new to the indexing strategy in postgres. My question is, <= br> > what suitable index should we create to cater these above?
>
> 1)For table event_audit_log:- Should we create composite Index on colu= mn
> (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
> index 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 i= s
> it fine to keep it as non leading?
>
> -> =C2=A0Nested Loop =C2=A0(cost=3D50.06..2791551.71 rows=3D3148 wi= dth=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..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
> -> =C2=A0Parallel Seq Scan on orders ord =C2=A0(cost=3D0.00..102287= 2.54 rows=3D3672860
> width=3D16) (actual time=3D139.883..3152.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
>
>
> Regards
> Yudhi


--
Adrian Klaver
adrian.klaver@aklaver.com


--00000000000047ff92064ae3a82f--