On Mon, Feb 2, 2026 at 8:53 AM yudhi s <[email protected]> wrote:
> > > On Mon, Feb 2, 2026 at 7:04 PM Ron Johnson <[email protected]> > wrote: > >> On Mon, Feb 2, 2026 at 6:39 AM yudhi s <[email protected]> >> wrote: >> >>> >>> >>> On Mon, Feb 2, 2026 at 3:17 AM Peter J. Holzer <[email protected]> wrote: >>> >>>> >>>> If you do have that many simultaneous accesses to the landing page, and >>>> you can't speed up the query significantly (I take it you've seen the >>>> suggestion to check whether there's an index on >>>> APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it >>>> for every user? I don't know what the query is supposed to do, but >>>> unless the "ent_id" is really a user id, it doesn't seem to be specific >>>> to the user. So maybe you can cache the result for a minute or an hour >>>> and show the same result to everybody who logs in during that time. >>>> >>>> >>>> >>> >>> There was no index on column tran_date , I created one and it's >>> making the query finish in ~200ms, a lot faster than in the past. Below is >>> the portion of the query and its plan which actually consumes most of the >>> resource and time post the new index creation. >>> >>> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492 >>> >>> 1) Now the part which takes time is the "nested loop" join on the >>> "ent_id" column. Can we do anything to make it much better/faster? >>> >>> 2) Also another question I had was, with this new index the table scan >>> of txn_tbl is now fully eliminated by the "Index Scan Backward" even i have >>> other columns from that table projected in the query, so how its getting >>> all those column values without visiting table but just that index scan >>> backward operation? >>> >> >> Reading through EXPLAIN output isn't always a mystery. >> >> Search for "actual time" and you'll find row 53, which is the "deepest" >> (most nested) row with the highest actual time. >> >> That tells you where the time is now spent, and what it's doing. >> >> >> > My apologies if i misunderstand the plan, But If I see, it's spending > ~140ms(140ms-6ms) i.e. almost all the time now, in performing the below > nested loop join. So my question was , is there any possibility to reduce > the resource consumption or response time further here? Hope my > understanding is correct here. > > -> Nested Loop (cost=266.53..1548099.38 rows=411215 width=20) (actual time= > *6.009..147.695* rows=1049 loops=1) > Join Filter: ((df.ent_id)::numeric = m.ent_id) > Rows Removed by Join Filter: 513436 > Buffers: shared hit=1939 > I don't see m.ent_id in the actual query. Did you only paste a portion of the query? Also, casting in a JOIN typically brutalizes the ability to use an index. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
