On Mon, Feb 2, 2026 at 3:43 PM yudhi s <[email protected]> wrote:
> > On Tue, Feb 3, 2026 at 1:01 AM Ron Johnson <[email protected]> > wrote: > >> On Mon, Feb 2, 2026 at 1:39 PM yudhi s <[email protected]> >> wrote: >> >>> On Mon, Feb 2, 2026 at 8:57 PM Ron Johnson <[email protected]> >>> wrote: >>> >>>> >>>>> 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. >>>> >>>> >>>> Thank you. >>> Actually i tried executing the first two CTE where the query was >>> spending most of the time and teh alias has changed. >>> >> >> We need to see everything, not just what you think is relevant. >> >> >>> Also here i have changed the real table names before putting it here, >>> hope that is fine. >>> However , i verified the data type of the ent_id column in "ent" its >>> "int8" and in table "txn_tbl" is "numeric 12", so do you mean to say this >>> difference in the data type is causing this high response time during the >>> nested loop join? My understanding was it will be internally castable >>> without additional burden. Also, even i tried creating an index on the >>> "(df.ent_id)::numeric" >>> its still reulting into same plan and response time. >>> >> >> If you'd shown the "\d" table definitions like Adrian asked two days ago, >> we'd know what indexes are on each table, and not have to beg you to >> dispense dribs and drabs of information. >> >> > I am unable to run "\d" from the dbeaver sql worksheet. However, I have > fetched the DDL for the three tables and their selected columns, used in > the smaller version of the query and its plan , which I recently updated. > > https://gist.github.com/databasetech0073/e4290b085f8f974e315fb41bdc47a1f3 > > https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492 > Lines 30-32 are where most of the time and effort are taken. I can't be certain, but changing APP_schema.ent.ent_id from NUMERIC to int8 (with a CHECK constraint to, well, constrain it to 12 digits, if really necessary) is something I'd test. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
