My mental model of the EXISTS clause must be off. This snippet appears at the end of a series of WITH clauses I suspect are irrelevant:
with etc etc ... , cids as
(select distinct c.id from ddr2 c
join claim_entries ce on ce.claim_id = c.id
where (c.assigned_ddr = 879
or exists (select 1 from ddr_cdt dc
where
dc.sys_user_id = 879
and dc.document_type = c.document_type
-- makes it faster: and (dc.cdt_code is null or dc.cdt_code = ce.cpt_code)
)))
select count(*) from cids
If I uncomment the bit where it says "make it faster" I get decent response and the graphical analyze display shows the expected user+doctype+cdtcode index is being used (and nice thin lines suggesting efficient lookup).
As it is, the analyze display shows the expected user+doctype index* being used but the lines are fat, and performance is an exponential disaster.
* I created the (to me ) redundant user+doctype index trying to get Postgres to Do the Right Thing(tm), but I can see that was not the issue.
I presume the reason performance drops off a cliff is because there can be 9000 cdt_codes for one user+doctype, but I was hoping EXISTS would just look to see if there was at least one row matching user+doctype and return its decision. I have tried select *, select 1, and limit 1 on the nested select to no avail.
Am I just doing something wrong? I am a relative noob. Is there some other hint I can give the planner?
Thx, ken
