QUERRY: WITH access_1 as ( SELECT template_id, status_id FROM role_access_rule a_rr JOIN access_rule a_r ON a_rr.rule_id=a_r.rule_id JOIN access_card_rule a_cr ON a_r.rule_id=a_cr.rule_id and a_cr.operation_code='R' JOIN person_role a_pr ON a_rr.role_code=a_pr.role_code AND a_pr.person_id=7676 ), access_2 as ( SELECT template_id, status_id FROM role_access_rule a_rr JOIN access_rule a_r ON a_rr.rule_id=a_r.rule_id AND a_rr.role_code IS NULL JOIN access_card_rule a_cr ON a_r.rule_id=a_cr.rule_id and a_cr.operation_code='R' ) -- WITH END select count(distinct c.CARD_ID), 0 from card c where card_id in (select DISTINCT avMain.number_value from card c inner join attribute_value vr on (vr.number_value = c.card_id) inner join card cr on (cr.card_id = vr.card_id) INNER JOIN attribute_value vs ON (vs.card_id = c.card_id) INNER JOIN attribute_value avParentÅxec on (avParentÅxec.card_id = c.card_id) INNER JOIN attribute_value avExec ON (avExec.card_id = cr.card_id ) INNER JOIN attribute_value avMain ON (avMain.card_id = c.card_id ) where c.template_id = 324 and c.status_id in (103,206) and vr.attribute_code ='ADMIN_702311' and cr.template_id = 1044 and cr.status_id = 206 AND vs.attribute_code IN ( 'JBR_INFD_SGNEX_LINK') AND vs.number_value = 7676 AND avParentÅxec.attribute_code IN ('JBR_INFD_EXEC_LINK') AND avExec.attribute_code = 'ADMIN_702335' AND avExec.number_value = avParentÅxec.number_value AND avMain.attribute_code = 'JBR_MAINDOC' and not exists (select 1 from attribute_value where card_id = cr.card_id and attribute_code = 'JBR_ARM_HIDE' and value_id = 1449) UNION select DISTINCT avMain.number_value from card c inner join attribute_value vr on (vr.number_value = c.card_id) inner join card cr on (cr.card_id = vr.card_id) inner join attribute_value avParentCoexec on (avParentCoexec.card_id = c.card_id) INNER JOIN attribute_value avCoexec ON (avCoexec.card_id = cr.card_id ) INNER JOIN attribute_value vs ON (vs.card_id = c.card_id) INNER JOIN attribute_value avMain ON (avMain.card_id = c.card_id ) JOIN attribute_value av_rep on c.card_id = av_rep.number_value and av_rep.attribute_code = 'ADMIN_702311' where c.template_id = 324 and c.status_id in (103,206) and vr.attribute_code ='ADMIN_702311' and cr.template_id = 1044 and cr.status_id = 206 AND avParentCoexec.attribute_code IN ('ADMIN_255974') AND avCoexec.attribute_code = 'ADMIN_702335' AND avCoexec.number_value = avParentCoexec.number_value AND vs.attribute_code IN ( 'JBR_INFD_EXEC_LINK') AND vs.number_value = 7676 AND avMain.attribute_code = 'JBR_MAINDOC' and not exists (select 1 from attribute_value where card_id = cr.card_id and attribute_code = 'JBR_ARM_HIDE' and value_id = 1449) group by cr.card_id, avMain.number_value having cr.card_id != min(av_rep.card_id) UNION select DISTINCT avMain.number_value from card c inner join attribute_value vr on (vr.number_value = c.card_id) inner join card cr on (cr.card_id = vr.card_id) inner join attribute_value ve on (ve.card_id = cr.card_id) INNER JOIN attribute_value avMain ON (avMain.card_id = c.card_id ) where c.template_id = 324 and c.status_id in (103,206) and vr.attribute_code ='ADMIN_702311' and cr.template_id = 1044 and cr.status_id = 206 AND ve.attribute_code IN ( 'JBR_RPT_DR_APPROVER') AND ve.number_value = 7676 AND avMain.attribute_code = 'JBR_MAINDOC' and not exists (select 1 from attribute_value where card_id = cr.card_id and attribute_code = 'JBR_ARM_HIDE' and value_id = 1449) UNION select c.card_id from card c inner join attribute_value vr on (vr.number_value = c.card_id) inner join card cr on (cr.card_id = vr.card_id) inner join attribute_value ve on (ve.card_id = c.card_id) where c.template_id = 1255 and c.status_id in (103,206) and vr.attribute_code ='ADMIN_702311' and cr.template_id = 1044 and cr.status_id = 206 and ve.attribute_code = 'JBR_INFD_SGNEX_LINK' and ve.number_value = 7676 and not exists (select 1 from attribute_value where card_id = cr.card_id and attribute_code = 'JBR_ARM_HIDE' and value_id = 1449) UNION select c.card_id from card c inner join attribute_value vr on (vr.number_value = c.card_id) inner join card cr on (cr.card_id = vr.card_id) inner join attribute_value avParentCoexec on (avParentCoexec.card_id = c.card_id) inner join attribute_value avCoexec on (avCoexec.card_id = cr.card_id ) inner join attribute_value ve on (ve.card_id = c.card_id) where c.template_id = 1255 and c.status_id in (103,206) and vr.attribute_code ='ADMIN_702311' and cr.template_id = 1044 and cr.status_id = 206 and avParentCoexec.attribute_code IN ('ADMIN_255974') and avCoexec.attribute_code = 'ADMIN_702335' and avCoexec.number_value = avParentCoexec.number_value and ve.attribute_code IN ('JBR_INFD_EXEC_LINK') and ve.number_value = 7676 and not exists (select 1 from attribute_value where card_id = cr.card_id and attribute_code = 'JBR_ARM_HIDE' and value_id = 1449) )and ( not EXISTS (SELECT act.card_id from card act where act.card_id=c.card_id and act.is_active = 0) OR EXISTS (SELECT role_code FROM person_role WHERE person_id=7676 AND role_code = 'A')) AND (EXISTS ( SELECT 1 FROM access_list a_l JOIN access_card_rule a_cr1 ON a_l.rule_id=a_cr1.rule_id WHERE a_cr1.operation_code='R' AND a_l.person_id=7676 AND c.card_id=a_l.card_id ) OR EXISTS ( SELECT 1 FROM access_1 ar WHERE (c.template_id=ar.template_id) AND (c.status_id =ar.status_id) ) OR EXISTS ( SELECT 1 FROM access_2 ar WHERE (c.template_id=ar.template_id) AND (c.status_id =ar.status_id) ) -- EXISTS ) -- AND 1: pg_statistic table: 928 kb, 1912 kb toast, 72 kb index time 464ms statistic_target=500 plan: "Aggregate (cost=3343056.71..3343056.72 rows=1 width=6)" " CTE access_1" " -> Nested Loop (cost=169.21..409.48 rows=164 width=10)" " Join Filter: (a_rr.rule_id = a_r.rule_id)" " -> Nested Loop (cost=168.93..334.26 rows=164 width=16)" " -> Hash Join (cost=168.65..237.13 rows=245 width=8)" " Hash Cond: ((a_pr.role_code)::text = (a_rr.role_code)::text)" " -> Index Scan using xif1person_role on person_role a_pr (cost=0.29..24.99 rows=28 width=14)" " Index Cond: (person_id = '7676'::numeric)" " -> Hash (cost=124.27..124.27 rows=3527 width=15)" " -> Seq Scan on role_access_rule a_rr (cost=0.00..124.27 rows=3527 width=15)" " -> Index Scan using access_card_rule_pkey on access_card_rule a_cr (cost=0.28..0.39 rows=1 width=8)" " Index Cond: (rule_id = a_rr.rule_id)" " Filter: (operation_code = 'R'::bpchar)" " -> Index Scan using access_rule_rule_idx on access_rule a_r (cost=0.28..0.45 rows=1 width=18)" " Index Cond: (rule_id = a_cr.rule_id)" " CTE access_2" " -> Nested Loop (cost=125.23..275.57 rows=36 width=10)" " Join Filter: (a_rr_1.rule_id = a_r_1.rule_id)" " -> Hash Join (cost=124.95..259.06 rows=36 width=16)" " Hash Cond: (a_cr_1.rule_id = a_rr_1.rule_id)" " -> Seq Scan on access_card_rule a_cr_1 (cost=0.00..119.39 rows=3831 width=8)" " Filter: (operation_code = 'R'::bpchar)" " -> Hash (cost=124.27..124.27 rows=54 width=8)" " -> Seq Scan on role_access_rule a_rr_1 (cost=0.00..124.27 rows=54 width=8)" " Filter: (role_code IS NULL)" " -> Index Scan using access_rule_rule_idx on access_rule a_r_1 (cost=0.28..0.45 rows=1 width=18)" " Index Cond: (rule_id = a_cr_1.rule_id)" " InitPlan 5 (returns $7)" " -> Bitmap Heap Scan on person_role (cost=9.21..13.23 rows=1 width=0)" " Recheck Cond: (((role_code)::text = 'A'::text) AND (person_id = '7676'::numeric))" " -> BitmapAnd (cost=9.21..9.21 rows=1 width=0)" " -> Bitmap Index Scan on xif2person_role (cost=0.00..4.46 rows=23 width=0)" " Index Cond: ((role_code)::text = 'A'::text)" " -> Bitmap Index Scan on xif1person_role (cost=0.00..4.50 rows=28 width=0)" " Index Cond: (person_id = '7676'::numeric)" " -> Nested Loop (cost=534448.10..3318912.35 rows=9378432 width=6)" " -> HashAggregate (cost=534447.53..535283.16 rows=83563 width=16)" " Group Key: avmain.number_value" " -> Append (cost=104.40..534238.63 rows=83563 width=16)" " -> HashAggregate (cost=104.40..104.41 rows=1 width=4)" " Group Key: avmain.number_value" " -> Nested Loop Anti Join (cost=4.93..104.39 rows=1 width=4)" " -> Nested Loop (cost=4.36..87.93 rows=1 width=10)" " -> Nested Loop (cost=3.79..80.71 rows=1 width=28)" " -> Nested Loop (cost=3.09..76.60 rows=1 width=26)" " -> Nested Loop (cost=2.53..73.02 rows=1 width=32)" " -> Nested Loop (cost=1.96..65.81 rows=1 width=22)" " -> Nested Loop (cost=1.26..54.23 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value vs (cost=0.70..8.88 rows=9 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_SGNEX_LINK'::text))" " -> Index Only Scan using card_and_status_idx on card c_1 (cost=0.56..5.03 rows=1 width=6)" " Index Cond: ((card_id = vs.card_id) AND (template_id = '324'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Only Scan using xif7attribute_value on attribute_value vr (cost=0.70..11.12 rows=45 width=10)" " Index Cond: ((number_value = c_1.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Scan using xif6attribute_value on attribute_value avexec (cost=0.57..7.21 rows=1 width=10)" " Index Cond: ((card_id = vr.card_id) AND ((attribute_code)::text = 'ADMIN_702335'::text))" " -> Index Only Scan using card_and_status_idx on card cr (cost=0.56..3.57 rows=1 width=6)" " Index Cond: ((card_id = vr.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value "avparentÅxec" (cost=0.70..4.09 rows=1 width=10)" " Index Cond: ((card_id = vr.number_value) AND (number_value = avexec.number_value) AND (attribute_code = 'JBR_INFD_EXEC_LINK'::text))" " -> Index Scan using xif6attribute_value on attribute_value avmain (cost=0.57..7.21 rows=1 width=10)" " Index Cond: ((card_id = vr.number_value) AND ((attribute_code)::text = 'JBR_MAINDOC'::text))" " -> Index Scan using xif6attribute_value on attribute_value (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Subquery Scan on "*SELECT* 2" (cost=532170.81..533841.99 rows=83559 width=16)" " -> HashAggregate (cost=532170.81..533006.40 rows=83559 width=16)" " Group Key: avmain_1.number_value" " -> HashAggregate (cost=530917.42..531961.91 rows=83559 width=16)" " Group Key: cr_1.card_id, avmain_1.number_value" " Filter: (cr_1.card_id <> min(av_rep.card_id))" " -> Hash Anti Join (cost=529140.94..530290.73 rows=83559 width=16)" " Hash Cond: (cr_1.card_id = attribute_value_1.card_id)" " -> Nested Loop (cost=5.06..99.27 rows=83560 width=16)" " -> Nested Loop (cost=4.36..88.48 rows=1 width=38)" " -> Nested Loop (cost=3.79..81.16 rows=1 width=28)" " -> Nested Loop (cost=3.09..76.98 rows=1 width=26)" " -> Nested Loop (cost=2.53..73.26 rows=1 width=32)" " -> Nested Loop (cost=1.96..65.93 rows=1 width=22)" " -> Nested Loop (cost=1.26..54.23 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value vs_1 (cost=0.70..8.88 rows=9 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_EXEC_LINK'::text))" " -> Index Only Scan using card_and_status_idx on card c_2 (cost=0.56..5.03 rows=1 width=6)" " Index Cond: ((card_id = vs_1.card_id) AND (template_id = '324'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Only Scan using xif7attribute_value on attribute_value vr_1 (cost=0.70..11.25 rows=45 width=10)" " Index Cond: ((number_value = c_2.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Scan using xif6attribute_value on attribute_value avcoexec (cost=0.57..7.31 rows=1 width=10)" " Index Cond: ((card_id = vr_1.card_id) AND ((attribute_code)::text = 'ADMIN_702335'::text))" " -> Index Only Scan using card_and_status_idx on card cr_1 (cost=0.56..3.71 rows=1 width=6)" " Index Cond: ((card_id = vr_1.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avparentcoexec (cost=0.70..4.17 rows=1 width=10)" " Index Cond: ((card_id = vr_1.number_value) AND (number_value = avcoexec.number_value) AND (attribute_code = 'ADMIN_255974'::text))" " -> Index Scan using xif6attribute_value on attribute_value avmain_1 (cost=0.57..7.31 rows=1 width=10)" " Index Cond: ((card_id = vr_1.number_value) AND ((attribute_code)::text = 'JBR_MAINDOC'::text))" " -> Index Only Scan using xif7attribute_value on attribute_value av_rep (cost=0.70..10.34 rows=45 width=10)" " Index Cond: ((number_value = vr_1.number_value) AND (attribute_code = 'ADMIN_702311'::text))" " -> Hash (cost=529129.52..529129.52 rows=509 width=6)" " -> Bitmap Heap Scan on attribute_value attribute_value_1 (cost=3649.79..529129.52 rows=509 width=6)" " Recheck Cond: ((attribute_code)::text = 'JBR_ARM_HIDE'::text)" " Filter: (value_id = '1449'::numeric)" " -> Bitmap Index Scan on attrivute_value_attr_code_idx (cost=0.00..3649.66 rows=152145 width=0)" " Index Cond: ((attribute_code)::text = 'JBR_ARM_HIDE'::text)" " -> HashAggregate (cost=107.78..107.79 rows=1 width=4)" " Group Key: avmain_2.number_value" " -> Nested Loop Anti Join (cost=3.54..107.78 rows=1 width=4)" " -> Nested Loop (cost=2.97..91.31 rows=1 width=10)" " -> Nested Loop (cost=2.40..84.31 rows=1 width=16)" " -> Nested Loop (cost=1.83..81.22 rows=1 width=10)" " -> Nested Loop (cost=1.26..72.69 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value ve (cost=0.70..8.96 rows=13 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_RPT_DR_APPROVER'::text))" " -> Index Only Scan using card_and_status_idx on card cr_2 (cost=0.56..4.89 rows=1 width=6)" " Index Cond: ((card_id = ve.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Scan using xif6attribute_value on attribute_value vr_2 (cost=0.57..8.52 rows=1 width=10)" " Index Cond: ((card_id = cr_2.card_id) AND ((attribute_code)::text = 'ADMIN_702311'::text))" " -> Index Only Scan using card_and_status_idx on card c_3 (cost=0.56..3.08 rows=1 width=6)" " Index Cond: ((card_id = vr_2.number_value) AND (template_id = '324'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Scan using xif6attribute_value on attribute_value avmain_2 (cost=0.57..6.99 rows=1 width=10)" " Index Cond: ((card_id = vr_2.number_value) AND ((attribute_code)::text = 'JBR_MAINDOC'::text))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_2 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_2.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Nested Loop Anti Join (cost=3.09..86.26 rows=1 width=6)" " -> Nested Loop (cost=2.52..69.79 rows=1 width=12)" " -> Nested Loop (cost=1.96..67.16 rows=1 width=12)" " -> Nested Loop (cost=1.26..54.23 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value ve_1 (cost=0.70..8.88 rows=9 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_SGNEX_LINK'::text))" " -> Index Only Scan using card_and_status_idx on card c_4 (cost=0.56..5.03 rows=1 width=6)" " Index Cond: ((card_id = ve_1.card_id) AND (template_id = '1255'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Only Scan using xif7attribute_value on attribute_value vr_3 (cost=0.70..12.48 rows=45 width=10)" " Index Cond: ((number_value = c_4.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using card_and_status_idx on card cr_3 (cost=0.56..2.62 rows=1 width=6)" " Index Cond: ((card_id = vr_3.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_3 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_3.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Nested Loop Anti Join (cost=4.36..98.14 rows=1 width=6)" " -> Nested Loop (cost=3.79..81.67 rows=1 width=12)" " -> Nested Loop (cost=3.23..78.29 rows=1 width=18)" " Join Filter: (c_5.card_id = vr_4.number_value)" " -> Nested Loop (cost=2.53..73.52 rows=1 width=24)" " -> Nested Loop (cost=1.83..62.84 rows=1 width=22)" " -> Nested Loop (cost=1.26..54.23 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value ve_2 (cost=0.70..8.88 rows=9 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_EXEC_LINK'::text))" " -> Index Only Scan using card_and_status_idx on card c_5 (cost=0.56..5.03 rows=1 width=6)" " Index Cond: ((card_id = ve_2.card_id) AND (template_id = '1255'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Scan using xif6attribute_value on attribute_value avparentcoexec_1 (cost=0.57..8.59 rows=1 width=10)" " Index Cond: ((card_id = c_5.card_id) AND ((attribute_code)::text = 'ADMIN_255974'::text))" " -> Index Only Scan using xif7attribute_value on attribute_value avcoexec_1 (cost=0.70..10.26 rows=42 width=10)" " Index Cond: ((number_value = avparentcoexec_1.number_value) AND (attribute_code = 'ADMIN_702335'::text))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value vr_4 (cost=0.70..4.76 rows=1 width=10)" " Index Cond: ((card_id = avcoexec_1.card_id) AND (number_value = avparentcoexec_1.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using card_and_status_idx on card cr_4 (cost=0.56..3.37 rows=1 width=6)" " Index Cond: ((card_id = vr_4.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_4 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_4.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Index Only Scan using card_and_status_idx on card c (cost=0.56..33.29 rows=1 width=6)" " Index Cond: (card_id = avmain.number_value)" " Filter: (((NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) OR $7) AND ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9) OR (alternatives: SubPlan 10 or hashed SubPlan 11)))" " SubPlan 3" " -> Index Only Scan using card_and_is_active_idx on card act (cost=0.56..6.77 rows=1 width=0)" " Index Cond: ((card_id = c.card_id) AND (is_active = '0'::numeric))" " SubPlan 4" " -> Index Only Scan using card_and_is_active_idx on card act_1 (cost=0.56..660442.94 rows=1 width=6)" " Index Cond: (is_active = '0'::numeric)" " SubPlan 6" " -> Nested Loop (cost=0.85..16.90 rows=1 width=0)" " -> Index Scan using access_list_person_and_card_idx on access_list a_l (cost=0.57..8.59 rows=1 width=8)" " Index Cond: ((c.card_id = card_id) AND (person_id = '7676'::numeric))" " -> Index Scan using access_card_rule_pkey on access_card_rule a_cr1 (cost=0.28..8.30 rows=1 width=8)" " Index Cond: (rule_id = a_l.rule_id)" " Filter: (operation_code = 'R'::bpchar)" " SubPlan 7" " -> Hash Join (cost=3394.59..388231.40 rows=82986 width=6)" " Hash Cond: (a_l_1.rule_id = a_cr1_1.rule_id)" " -> Bitmap Heap Scan on access_list a_l_1 (cost=3227.32..386770.36 rows=123710 width=14)" " Recheck Cond: (person_id = '7676'::numeric)" " -> Bitmap Index Scan on access_list_person_idx (cost=0.00..3196.39 rows=123710 width=0)" " Index Cond: (person_id = '7676'::numeric)" " -> Hash (cost=119.39..119.39 rows=3831 width=8)" " -> Seq Scan on access_card_rule a_cr1_1 (cost=0.00..119.39 rows=3831 width=8)" " Filter: (operation_code = 'R'::bpchar)" " SubPlan 8" " -> CTE Scan on access_1 ar (cost=0.00..4.10 rows=1 width=0)" " Filter: ((c.template_id = template_id) AND (c.status_id = status_id))" " SubPlan 9" " -> CTE Scan on access_1 ar_1 (cost=0.00..3.28 rows=164 width=28)" " SubPlan 10" " -> CTE Scan on access_2 ar_2 (cost=0.00..0.90 rows=1 width=0)" " Filter: ((c.template_id = template_id) AND (c.status_id = status_id))" " SubPlan 11" " -> CTE Scan on access_2 ar_3 (cost=0.00..0.72 rows=36 width=28)" 2: pg_statistic table size: 928 kb, 10 MB toast, 72 kb index time 14,3 sec statistic_target = 10000 plan: "Aggregate (cost=3420269.65..3420269.66 rows=1 width=6)" " CTE access_1" " -> Nested Loop (cost=169.21..427.42 rows=179 width=10)" " Join Filter: (a_rr.rule_id = a_r.rule_id)" " -> Nested Loop (cost=168.93..345.32 rows=179 width=16)" " -> Hash Join (cost=168.65..239.47 rows=267 width=8)" " Hash Cond: ((a_pr.role_code)::text = (a_rr.role_code)::text)" " -> Index Scan using xif1person_role on person_role a_pr (cost=0.29..25.63 rows=29 width=14)" " Index Cond: (person_id = '7676'::numeric)" " -> Hash (cost=124.27..124.27 rows=3527 width=15)" " -> Seq Scan on role_access_rule a_rr (cost=0.00..124.27 rows=3527 width=15)" " -> Index Scan using access_card_rule_pkey on access_card_rule a_cr (cost=0.28..0.39 rows=1 width=8)" " Index Cond: (rule_id = a_rr.rule_id)" " Filter: (operation_code = 'R'::bpchar)" " -> Index Scan using access_rule_rule_idx on access_rule a_r (cost=0.28..0.45 rows=1 width=18)" " Index Cond: (rule_id = a_cr.rule_id)" " CTE access_2" " -> Nested Loop (cost=125.23..275.57 rows=36 width=10)" " Join Filter: (a_rr_1.rule_id = a_r_1.rule_id)" " -> Hash Join (cost=124.95..259.06 rows=36 width=16)" " Hash Cond: (a_cr_1.rule_id = a_rr_1.rule_id)" " -> Seq Scan on access_card_rule a_cr_1 (cost=0.00..119.39 rows=3831 width=8)" " Filter: (operation_code = 'R'::bpchar)" " -> Hash (cost=124.27..124.27 rows=54 width=8)" " -> Seq Scan on role_access_rule a_rr_1 (cost=0.00..124.27 rows=54 width=8)" " Filter: (role_code IS NULL)" " -> Index Scan using access_rule_rule_idx on access_rule a_r_1 (cost=0.28..0.45 rows=1 width=18)" " Index Cond: (rule_id = a_cr_1.rule_id)" " InitPlan 5 (returns $7)" " -> Bitmap Heap Scan on person_role (cost=9.42..13.43 rows=1 width=0)" " Recheck Cond: ((person_id = '7676'::numeric) AND ((role_code)::text = 'A'::text))" " -> BitmapAnd (cost=9.42..9.42 rows=1 width=0)" " -> Bitmap Index Scan on xif1person_role (cost=0.00..4.51 rows=29 width=0)" " Index Cond: (person_id = '7676'::numeric)" " -> Bitmap Index Scan on xif2person_role (cost=0.00..4.66 rows=49 width=0)" " Index Cond: ((role_code)::text = 'A'::text)" " -> Nested Loop (cost=689985.36..3396108.82 rows=9377764 width=6)" " -> HashAggregate (cost=689984.80..690787.80 rows=80300 width=16)" " Group Key: avmain.number_value" " -> Append (cost=560.09..689784.05 rows=80300 width=16)" " -> HashAggregate (cost=560.09..560.10 rows=1 width=4)" " Group Key: avmain.number_value" " -> Nested Loop Anti Join (cost=5.18..560.09 rows=1 width=4)" " -> Nested Loop (cost=4.61..543.62 rows=1 width=10)" " -> Nested Loop (cost=3.92..539.27 rows=1 width=28)" " -> Nested Loop (cost=3.22..535.17 rows=1 width=26)" " -> Nested Loop (cost=2.66..531.58 rows=1 width=32)" " -> Nested Loop (cost=1.96..527.23 rows=1 width=22)" " -> Nested Loop (cost=1.26..522.04 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value vs (cost=0.70..26.37 rows=107 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_SGNEX_LINK'::text))" " -> Index Only Scan using card_and_status_idx on card c_1 (cost=0.56..4.62 rows=1 width=6)" " Index Cond: ((card_id = vs.card_id) AND (template_id = '324'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Only Scan using xif7attribute_value on attribute_value vr (cost=0.70..5.16 rows=3 width=10)" " Index Cond: ((number_value = c_1.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avexec (cost=0.70..4.34 rows=1 width=10)" " Index Cond: ((card_id = vr.card_id) AND (attribute_code = 'ADMIN_702335'::text))" " -> Index Only Scan using card_and_status_idx on card cr (cost=0.56..3.58 rows=1 width=6)" " Index Cond: ((card_id = vr.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value "avparentÅxec" (cost=0.70..4.09 rows=1 width=10)" " Index Cond: ((card_id = vr.number_value) AND (number_value = avexec.number_value) AND (attribute_code = 'JBR_INFD_EXEC_LINK'::text))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avmain (cost=0.70..4.34 rows=1 width=10)" " Index Cond: ((card_id = vr.number_value) AND (attribute_code = 'JBR_MAINDOC'::text))" " -> Index Scan using xif6attribute_value on attribute_value (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Subquery Scan on "*SELECT* 2" (cost=686563.25..688169.17 rows=80296 width=16)" " -> HashAggregate (cost=686563.25..687366.21 rows=80296 width=16)" " Group Key: avmain_1.number_value" " -> HashAggregate (cost=685358.81..686362.51 rows=80296 width=16)" " Group Key: cr_1.card_id, avmain_1.number_value" " Filter: (cr_1.card_id <> min(av_rep.card_id))" " -> Nested Loop Anti Join (cost=5.88..684756.59 rows=80296 width=16)" " -> Nested Loop (cost=5.31..561.71 rows=80299 width=16)" " -> Nested Loop (cost=4.61..557.23 rows=1 width=38)" " -> Nested Loop (cost=3.92..552.80 rows=1 width=28)" " Join Filter: (vr_1.number_value = avparentcoexec.card_id)" " -> Nested Loop (cost=3.22..548.62 rows=1 width=26)" " -> Nested Loop (cost=2.66..544.89 rows=1 width=32)" " -> Nested Loop (cost=1.96..540.46 rows=1 width=22)" " -> Nested Loop (cost=1.26..535.27 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value vs_1 (cost=0.70..26.41 rows=109 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_EXEC_LINK'::text))" " -> Index Only Scan using card_and_status_idx on card c_2 (cost=0.56..4.66 rows=1 width=6)" " Index Cond: ((card_id = vs_1.card_id) AND (template_id = '324'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Only Scan using xif7attribute_value on attribute_value vr_1 (cost=0.70..5.16 rows=3 width=10)" " Index Cond: ((number_value = c_2.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avcoexec (cost=0.70..4.42 rows=1 width=10)" " Index Cond: ((card_id = vr_1.card_id) AND (attribute_code = 'ADMIN_702335'::text))" " -> Index Only Scan using card_and_status_idx on card cr_1 (cost=0.56..3.72 rows=1 width=6)" " Index Cond: ((card_id = vr_1.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Only Scan using xif7attribute_value on attribute_value avparentcoexec (cost=0.70..4.17 rows=1 width=10)" " Index Cond: ((number_value = avcoexec.number_value) AND (attribute_code = 'ADMIN_255974'::text))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avmain_1 (cost=0.70..4.42 rows=1 width=10)" " Index Cond: ((card_id = vr_1.number_value) AND (attribute_code = 'JBR_MAINDOC'::text))" " -> Index Only Scan using xif7attribute_value on attribute_value av_rep (cost=0.70..4.45 rows=3 width=10)" " Index Cond: ((number_value = vr_1.number_value) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_1 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_1.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> HashAggregate (cost=774.94..774.95 rows=1 width=4)" " Group Key: avmain_2.number_value" " -> Nested Loop Anti Join (cost=3.79..774.94 rows=1 width=4)" " -> Nested Loop (cost=3.22..758.47 rows=1 width=10)" " -> Nested Loop (cost=2.52..754.29 rows=1 width=16)" " -> Nested Loop (cost=1.96..751.19 rows=1 width=10)" " -> Nested Loop (cost=1.26..746.09 rows=1 width=12)" " -> Index Only Scan using xif7attribute_value on attribute_value ve (cost=0.70..35.06 rows=153 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_RPT_DR_APPROVER'::text))" " -> Index Only Scan using card_and_status_idx on card cr_2 (cost=0.56..4.64 rows=1 width=6)" " Index Cond: ((card_id = ve.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value vr_2 (cost=0.70..5.09 rows=1 width=10)" " Index Cond: ((card_id = cr_2.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using card_and_status_idx on card c_3 (cost=0.56..3.09 rows=1 width=6)" " Index Cond: ((card_id = vr_2.number_value) AND (template_id = '324'::numeric))" " Filter: (status_id = ANY ('{103,206}'::numeric[]))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avmain_2 (cost=0.70..4.17 rows=1 width=10)" " Index Cond: ((card_id = vr_2.number_value) AND (attribute_code = 'JBR_MAINDOC'::text))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_2 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_2.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Nested Loop Anti Join (cost=86.77..135.36 rows=1 width=6)" " -> Nested Loop (cost=86.19..118.89 rows=1 width=12)" " -> Nested Loop (cost=85.63..116.25 rows=1 width=12)" " Join Filter: (c_4.card_id = vr_3.number_value)" " -> Merge Join (cost=84.93..110.98 rows=1 width=12)" " Merge Cond: (c_4.card_id = ve_1.card_id)" " -> Sort (cost=84.24..84.28 rows=19 width=6)" " Sort Key: c_4.card_id" " -> Index Scan using template_and_status_idx on card c_4 (cost=0.56..83.83 rows=19 width=6)" " Index Cond: ((template_id = '1255'::numeric) AND (status_id = ANY ('{103,206}'::numeric[])))" " -> Index Only Scan using xif7attribute_value on attribute_value ve_1 (cost=0.70..26.37 rows=107 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_SGNEX_LINK'::text))" " -> Index Only Scan using xif7attribute_value on attribute_value vr_3 (cost=0.70..5.23 rows=3 width=10)" " Index Cond: ((number_value = ve_1.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using card_and_status_idx on card cr_3 (cost=0.56..2.63 rows=1 width=6)" " Index Cond: ((card_id = vr_3.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_3 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_3.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Nested Loop Anti Join (cost=88.16..144.44 rows=1 width=6)" " -> Nested Loop (cost=87.59..127.97 rows=1 width=12)" " Join Filter: (vr_4.number_value = avparentcoexec_1.card_id)" " -> Nested Loop (cost=86.89..123.94 rows=1 width=26)" " -> Nested Loop (cost=86.33..120.56 rows=1 width=32)" " -> Nested Loop (cost=85.63..116.29 rows=1 width=22)" " Join Filter: (c_5.card_id = vr_4.number_value)" " -> Merge Join (cost=84.93..111.02 rows=1 width=12)" " Merge Cond: (c_5.card_id = ve_2.card_id)" " -> Sort (cost=84.24..84.28 rows=19 width=6)" " Sort Key: c_5.card_id" " -> Index Scan using template_and_status_idx on card c_5 (cost=0.56..83.83 rows=19 width=6)" " Index Cond: ((template_id = '1255'::numeric) AND (status_id = ANY ('{103,206}'::numeric[])))" " -> Index Only Scan using xif7attribute_value on attribute_value ve_2 (cost=0.70..26.41 rows=109 width=6)" " Index Cond: ((number_value = '7676'::numeric) AND (attribute_code = 'JBR_INFD_EXEC_LINK'::text))" " -> Index Only Scan using xif7attribute_value on attribute_value vr_4 (cost=0.70..5.23 rows=3 width=10)" " Index Cond: ((number_value = ve_2.card_id) AND (attribute_code = 'ADMIN_702311'::text))" " -> Index Only Scan using xif7attribute_value_v3 on attribute_value avcoexec_1 (cost=0.70..4.26 rows=1 width=10)" " Index Cond: ((card_id = vr_4.card_id) AND (attribute_code = 'ADMIN_702335'::text))" " -> Index Only Scan using card_and_status_idx on card cr_4 (cost=0.56..3.38 rows=1 width=6)" " Index Cond: ((card_id = vr_4.card_id) AND (status_id = '206'::numeric) AND (template_id = '1044'::numeric))" " -> Index Only Scan using xif7attribute_value on attribute_value avparentcoexec_1 (cost=0.70..4.01 rows=1 width=10)" " Index Cond: ((number_value = avcoexec_1.number_value) AND (attribute_code = 'ADMIN_255974'::text))" " -> Index Scan using xif6attribute_value on attribute_value attribute_value_4 (cost=0.57..8.52 rows=1 width=6)" " Index Cond: ((card_id = cr_4.card_id) AND ((attribute_code)::text = 'JBR_ARM_HIDE'::text))" " Filter: (value_id = '1449'::numeric)" " -> Index Only Scan using card_and_status_idx on card c (cost=0.56..33.67 rows=1 width=6)" " Index Cond: (card_id = avmain.number_value)" " Filter: (((NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) OR $7) AND ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9) OR (alternatives: SubPlan 10 or hashed SubPlan 11)))" " SubPlan 3" " -> Index Only Scan using card_and_is_active_idx on card act (cost=0.56..6.78 rows=1 width=0)" " Index Cond: ((card_id = c.card_id) AND (is_active = '0'::numeric))" " SubPlan 4" " -> Index Only Scan using card_and_is_active_idx on card act_1 (cost=0.56..660503.66 rows=1 width=6)" " Index Cond: (is_active = '0'::numeric)" " SubPlan 6" " -> Nested Loop (cost=0.85..16.90 rows=1 width=0)" " -> Index Scan using access_list_person_and_card_idx on access_list a_l (cost=0.57..8.59 rows=1 width=8)" " Index Cond: ((c.card_id = card_id) AND (person_id = '7676'::numeric))" " -> Index Scan using access_card_rule_pkey on access_card_rule a_cr1 (cost=0.28..8.30 rows=1 width=8)" " Index Cond: (rule_id = a_l.rule_id)" " Filter: (operation_code = 'R'::bpchar)" " SubPlan 7" " -> Hash Join (cost=3537.20..402741.87 rows=86673 width=6)" " Hash Cond: (a_l_1.rule_id = a_cr1_1.rule_id)" " -> Bitmap Heap Scan on access_list a_l_1 (cost=3369.92..401223.34 rows=129207 width=14)" " Recheck Cond: (person_id = '7676'::numeric)" " -> Bitmap Index Scan on access_list_person_idx (cost=0.00..3337.62 rows=129207 width=0)" " Index Cond: (person_id = '7676'::numeric)" " -> Hash (cost=119.39..119.39 rows=3831 width=8)" " -> Seq Scan on access_card_rule a_cr1_1 (cost=0.00..119.39 rows=3831 width=8)" " Filter: (operation_code = 'R'::bpchar)" " SubPlan 8" " -> CTE Scan on access_1 ar (cost=0.00..4.48 rows=1 width=0)" " Filter: ((c.template_id = template_id) AND (c.status_id = status_id))" " SubPlan 9" " -> CTE Scan on access_1 ar_1 (cost=0.00..3.58 rows=179 width=28)" " SubPlan 10" " -> CTE Scan on access_2 ar_2 (cost=0.00..0.90 rows=1 width=0)" " Filter: ((c.template_id = template_id) AND (c.status_id = status_id))" " SubPlan 11" " -> CTE Scan on access_2 ar_3 (cost=0.00..0.72 rows=36 width=28)"