I am struggling with an insert statement which used to work fine upto last week. It doesnt seem to complete and keeps on running forever without throwing an error. When its running I see locks being acquired on table INCLUDED_ENTITIES.
What I am trying to achieve is there are some exclusions defined in table AGREEMENT_PRODUCT_EXCLUSIONS at product_type. I am trying to find all those products from KNOWN_PRODUCTS for a given caseid and determining all those product_type which are not excluded. For example, known_products, exclusions and agreements are listed below. All known_products fall in category Der and will have caseid 10001 but PT1 is excluded. So the final outcome should be PT2 & PT3 be included for caseid 10001 with category Der.
product_type date
PT1 2024-01-07
PT2 2024-01-07
PT3 2024-01-07
product_type caseid date
PT1 10001 2024-01-07
entity branch cpty_entity cpty_branch caseid product_category date
1 1 101 1 10001 Der 2024-01-07
1 1 101 2 10002 Der 2024-01-07
1 1 101 3 10003 Der 2024-01-07
with BUSINESS_DATE as (
select date(max(DATA_DATE)) as DATA_DATE from KNOWN_PRODUCTS
)
, CASEIDS as (
select CASEID
from AGREEMENT_PRODUCT_EXCLUSIONS CAPE
where date(DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
group by CASEID
)
, CASEID_PRODUCT as (
select C.CASEID
,KP.PRODUCT_TYPE
,rank() over (order by C.CASEID, KP.PRODUCT_TYPE) as ID
,case
when KP.PRODUCT_TYPE = 'R1' then 'Rep'
when KP.PRODUCT_TYPE = 'S1' then 'Sec'
else 'Der'
end as PRODUCT_CATEGORY
from CASEIDS C cross JOIN KNOWN_PRODUCTS KP
where date(KP.DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
and KP.PRODUCT_TYPE != '()'
)
, INCLUDED_PRODUCT_TYPE as (
select AC.CASEID, AC.PRODUCT_TYPE, AC.PRODUCT_CATEGORY
from CASEID_PRODUCT AC
left join AGREEMENT_PRODUCT_EXCLUSIONS E on AC.PRODUCT_TYPE = E.PRODUCT_TYPE
and AC.CASEID = E.CASEID
and date(E.DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
where E.CASEID is null and E.PRODUCT_TYPE is null
)
, FINAL_DATA as (
select CA.ENTITY
, CA.BRANCH
, CA.CPTY_ENTITY
, CA.CPTY_BRANCH
, IC.PRODUCT_TYPE
, IC.PRODUCT_CATEGORY
from INCLUDED_PRODUCT_TYPE IC
join AGREEMENTS CA on CA.CASEID = IC.CASEID
and CA.PRODUCT_CATEGORY = IC.PRODUCT_CATEGORY
where date(CA.DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
group by CA.ENTITY
, CA.BRANCH
, CA.CPTY_ENTITY
, CA.CPTY_BRANCH
, IC.PRODUCT_TYPE
, IC.PRODUCT_CATEGORY
)
insert into INCLUDED_ENTITIES(ENTITY, BRANCH, CPTY_ENTITY, CPTY_BRANCH, PRODUCT_TYPE, PRODUCT_CATEGORY)
select ENTITY
, BRANCH
, CPTY_ENTITY
, CPTY_BRANCH
, PRODUCT_TYPE
, PRODUCT_CATEGORY
from FINAL_DATA;
Any idea what could be going on here? Thanks in advance.
AK
Update 1 Explain
1. Insert on included_entities as included_entities
2. Aggregate
3. Seq Scan on known_products as known_products
4. Subquery Scan
5. Group
6. CTE Scan
7. CTE Scan
8. Sort
9. Nested Loop Anti Join
Join Filter: ((ac.product_type = (e.product_type)::text) AND (ac.caseid = e.caseid))
10. Hash Inner Join
Hash Cond: ((ca.caseid = ac.caseid) AND (ca.product_category = ac.product_category))
11. Seq Scan on agreements as ca
Filter: ((data_date)::date = $2)
12. Hash
13. Subquery Scan
14. Nested Loop Inner Join
15. CTE Scan
16. Seq Scan on known_products as kp
Filter: ((product_type <> '()'::text) AND ((data_date)::date = $3))
17. Group
18. CTE Scan
19. Sort
20. Seq Scan on agreement_product_exclusions as cape
Filter: ((data_date)::date = $4)
21. Seq Scan on agreement_product_exclusions as e
Filter: ((data_date)::date = $1)
Total rows: 1 of 1
Query complete 00:00:00.144
Ln 38, Col 30
I managed to resolve this by rewriting the query completely. There was an intermediate step where lots of duplicates where encountered which required a group by. Here is the updated query.