QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=1054186.23..1054631.36 rows=4496 width=67) (actual time=4902.250..4979.060 rows=7653 loops=1) Hash Cond: ("outer".question_id = "inner".uid) -> Subquery Scan tab (cost=1054123.62..1054457.09 rows=13339 width=24) (actual time=4896.963..4933.896 rows=7657 loops=1) -> Unique (cost=1054123.62..1054323.70 rows=13339 width=24) (actual time=4896.948..4915.498 rows=7657 loops=1) -> Sort (cost=1054123.62..1054156.96 rows=13339 width=24) (actual time=4896.944..4903.402 rows=7717 loops=1) Sort Key: answer_id, client_id, question_id, recordset_id, date_effective -> Append (cost=0.00..1053209.67 rows=13339 width=24) (actual time=279.091..4841.605 rows=7717 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..64034.15 rows=10540 width=24) (actual time=279.089..4419.371 rows=161 loops=1) -> Seq Scan on da_answer a (cost=0.00..63928.75 rows=10540 width=24) (actual time=279.080..4418.808 rows=161 loops=1) Filter: ((date_effective <= 9999999999::double precision) AND (inactive <> 1) AND (subplan)) SubPlan -> Function Scan on svp_getparentproviderids (cost=0.00..15.00 rows=5 width=4) (actual time=0.203..0.203 rows=0 loops=21089) Filter: (svp_getparentproviderids = $1) -> Subquery Scan "*SELECT* 2" (cost=988627.58..989175.52 rows=2799 width=24) (actual time=290.730..417.720 rows=7556 loops=1) -> Hash Join (cost=988627.58..989147.53 rows=2799 width=24) (actual time=290.722..395.739 rows=7556 loops=1) Hash Cond: ("outer".main_id = "inner".uid) -> Subquery Scan sec (cost=987913.23..988002.59 rows=5957 width=4) (actual time=203.862..225.462 rows=7567 loops=1) -> Unique (cost=987913.23..987943.02 rows=5957 width=4) (actual time=203.851..215.834 rows=7567 loops=1) -> Sort (cost=987913.23..987928.12 rows=5957 width=4) (actual time=203.843..207.273 rows=7567 loops=1) Sort Key: main_id -> Append (cost=160.61..987539.72 rows=5957 width=4) (actual time=35.798..187.293 rows=7567 loops=1) -> Subquery Scan "*SELECT* 1" (cost=160.61..164.53 rows=46 width=4) (actual time=35.796..35.923 rows=35 loops=1) -> Hash Join (cost=160.61..164.07 rows=46 width=4) (actual time=35.791..35.868 rows=35 loops=1) Hash Cond: ("outer".svp_getparentproviderids = "inner".provider_id) -> HashAggregate (cost=15.00..15.00 rows=200 width=4) (actual time=0.491..0.492 rows=1 loops=1) -> Function Scan on svp_getparentproviderids (cost=0.00..12.50 rows=1000 width=4) (actual time=0.473..0.474 rows=1 loops=1) -> Hash (cost=145.50..145.50 rows=46 width=8) (actual time=35.256..35.256 rows=0 loops=1) -> Index Scan using in_da_dr_type_provider on da_data_restrict (cost=0.00..145.50 rows=46 width=8) (actual time=0.041..26.627 rows=7280 loops=1) Index Cond: (type_id = 2) -> Subquery Scan "*SELECT* 2" (cost=0.00..53.69 rows=1 width=4) (actual time=0.030..0.030 rows=0 loops=1) -> Nested Loop (cost=0.00..53.68 rows=1 width=4) (actual time=0.028..0.028 rows=0 loops=1) -> Index Scan using in_da_data_rec_except_provider_id on da_data_restrict_except_closed dr (cost=0.00..50.65 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1) Index Cond: (except_provider_id = 1) Filter: (type_id = 2) -> Index Scan using da_answer_pkey on da_answer sa (cost=0.00..3.02 rows=1 width=4) (never executed) Index Cond: ("outer".main_id = sa.uid) Filter: ((restricted = 1) AND (restricted_closed_except = 1) AND (covered_by_roi = 1)) -> Subquery Scan "*SELECT* 3" (cost=0.00..986638.62 rows=678 width=4) (actual time=0.346..77.393 rows=1841 loops=1) -> Index Scan using in_da_answer_restricted_open_except on da_answer sa (cost=0.00..986631.84 rows=678 width=4) (actual time=0.342..74.614 rows=1841 loops=1) Index Cond: (restricted_open_except = 1) Filter: ((restricted = 0) AND (covered_by_roi = 1) AND (NOT (subplan))) SubPlan -> Nested Loop IN Join (cost=0.00..227.09 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1841) Join Filter: ("outer".except_provider_id = "inner".svp_getparentproviderids) -> Index Scan using in_da_data_reo_main_id on da_data_restrict_except_open dr (cost=0.00..212.09 rows=1 width=8) (actual time=0.009..0.014 rows=2 loops=1841) Index Cond: (main_id = $0) Filter: (type_id = 2) -> Function Scan on svp_getparentproviderids (cost=0.00..12.50 rows=1000 width=4) (actual time=0.002..0.003 rows=1 loops=3793) -> Subquery Scan "*SELECT* 4" (cost=0.00..682.87 rows=5232 width=4) (actual time=0.064..69.107 rows=5691 loops=1) -> Seq Scan on da_answer sa (cost=0.00..630.55 rows=5232 width=4) (actual time=0.059..61.052 rows=5691 loops=1) Filter: ((restricted = 0) AND (restricted_open_except = 0) AND (covered_by_roi = 1)) -> Hash (cost=630.55..630.55 rows=9922 width=24) (actual time=86.699..86.699 rows=0 loops=1) -> Seq Scan on da_answer a (cost=0.00..630.55 rows=9922 width=24) (actual time=0.043..73.232 rows=10062 loops=1) Filter: ((covered_by_roi = 1) AND (date_effective <= 9999999999::double precision) AND (inactive <> 1)) -> Hash (cost=61.69..61.69 rows=369 width=47) (actual time=5.241..5.241 rows=0 loops=1) -> Seq Scan on da_question q (cost=0.00..61.69 rows=369 width=47) (actual time=0.026..4.071 rows=1087 loops=1) Filter: ((min_access_level <= 4000) OR (min_access_level IS NULL)) Total runtime: 4986.508 ms (58 rows) count ------- 21117 (1 row)