Skip to content

Instantly share code, notes, and snippets.

@NicoleCarpenter

NicoleCarpenter/query-plan-product-selection-query-builder

Created October 31, 2017 20:47
Show Gist options
  • Save NicoleCarpenter/1ad5039b20062c211e0a87c7845db916 to your computer and use it in GitHub Desktop.
Save NicoleCarpenter/1ad5039b20062c211e0a87c7845db916 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query-plan-product-selection-query-builder
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2502086.94..2502095.57 rows=3452 width=365) (actual time=71896.412..71908.046 rows=11198 loops=1)
Sort Key: a.policy_reference, c.trans_at DESC, ('Payment'::text)
Sort Method: quicksort Memory: 3339kB
-> HashAggregate (cost=2501815.04..2501849.56 rows=3452 width=283) (actual time=71732.986..71750.909 rows=11198 loops=1)
Group Key: a.id, a.id, (NULL::integer), a.policy_reference, (NULL::text), ('Payment'::text), (NULL::text), ('Credit Card Failure Day 4'::text), (((((((((c.name || ', '::text) || c.media_name) || ' '::text) || c.cc) || ' exp '::text) || c.exp) || ', Payment Amt: '::text) || c.amount)), (''::text), p.tracking, c.trans_at, (NULL::timestamp without time zone), (NULL::timestamp without time zone), a.created_at, (NULL::text), a.flex_type, (COALESCE(p.product_name, a.product))
-> Append (cost=861.58..2501659.70 rows=3452 width=283) (actual time=367.226..71696.287 rows=11198 loops=1)
-> Nested Loop (cost=861.58..32334.47 rows=46 width=123) (actual time=367.223..2522.643 rows=303 loops=1)
-> Hash Join (cost=861.16..27966.56 rows=9246 width=102) (actual time=366.793..2264.004 rows=9246 loops=1)
Hash Cond: (a.id = c.id)
-> Seq Scan on actual_events a (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.005..1210.368 rows=908385 loops=1)
-> Hash (cost=745.59..745.59 rows=9246 width=37) (actual time=38.244..38.244 rows=9246 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 791kB
-> Seq Scan on trust_commerce_payment_failure_actual_events c (cost=0.00..745.59 rows=9246 width=37) (actual time=0.024..24.046 rows=9246 loops=1)
Filter: (count = 4)
Rows Removed by Filter: 19121
-> Index Scan using policies_policy_reference_key on policies p (cost=0.42..0.46 rows=1 width=32) (actual time=0.025..0.025 rows=0 loops=9246)
Index Cond: (policy_reference = a.policy_reference)
Filter: (COALESCE(product_name, a.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=844.66..31664.35 rows=39 width=123) (actual time=351.874..2327.528 rows=250 loops=1)
-> Hash Join (cost=844.24..27936.09 rows=7892 width=102) (actual time=292.695..2149.359 rows=7892 loops=1)
Hash Cond: (a_1.id = c_1.id)
-> Seq Scan on actual_events a_1 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.005..1127.612 rows=908385 loops=1)
-> Hash (cost=745.59..745.59 rows=7892 width=37) (actual time=30.239..30.239 rows=7892 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 630kB
-> Seq Scan on trust_commerce_payment_failure_actual_events c_1 (cost=0.00..745.59 rows=7892 width=37) (actual time=0.917..17.994 rows=7892 loops=1)
Filter: (count = 6)
Rows Removed by Filter: 20475
-> Index Scan using policies_policy_reference_key on policies p_1 (cost=0.42..0.46 rows=1 width=32) (actual time=0.020..0.020 rows=0 loops=7892)
Index Cond: (policy_reference = a_1.policy_reference)
Filter: (COALESCE(product_name, a_1.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=815.73..30519.14 rows=28 width=123) (actual time=566.895..2463.130 rows=151 loops=1)
-> Hash Join (cost=815.31..27884.03 rows=5578 width=102) (actual time=354.297..2279.098 rows=5578 loops=1)
Hash Cond: (a_2.id = c_2.id)
-> Seq Scan on actual_events a_2 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.004..1200.476 rows=908385 loops=1)
-> Hash (cost=745.59..745.59 rows=5578 width=37) (actual time=23.833..23.833 rows=5578 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 463kB
-> Seq Scan on trust_commerce_payment_failure_actual_events c_2 (cost=0.00..745.59 rows=5578 width=37) (actual time=0.494..15.165 rows=5578 loops=1)
Filter: (count = 10)
Rows Removed by Filter: 22789
-> Index Scan using policies_policy_reference_key on policies p_2 (cost=0.42..0.46 rows=1 width=32) (actual time=0.027..0.027 rows=0 loops=5578)
Index Cond: (policy_reference = a_2.policy_reference)
Filter: (COALESCE(product_name, a_2.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..26935.64 rows=18 width=123) (actual time=6.098..129.895 rows=100 loops=1)
-> Nested Loop (cost=0.42..25218.42 rows=3635 width=102) (actual time=0.292..51.281 rows=3635 loops=1)
-> Seq Scan on trust_commerce_payment_failure_actual_events c_3 (cost=0.00..745.59 rows=3635 width=37) (actual time=0.242..13.296 rows=3635 loops=1)
Filter: (count = 15)
Rows Removed by Filter: 24732
-> Index Scan using actual_events_pkey on actual_events a_3 (cost=0.42..6.72 rows=1 width=69) (actual time=0.005..0.006 rows=1 loops=3635)
Index Cond: (id = c_3.id)
-> Index Scan using policies_policy_reference_key on policies p_3 (cost=0.42..0.46 rows=1 width=32) (actual time=0.019..0.019 rows=0 loops=3635)
Index Cond: (policy_reference = a_3.policy_reference)
Filter: (COALESCE(product_name, a_3.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..16554.21 rows=10 width=123) (actual time=16.205..77.220 rows=53 loops=1)
-> Nested Loop (cost=0.42..15601.83 rows=2016 width=102) (actual time=5.862..32.201 rows=2016 loops=1)
-> Seq Scan on trust_commerce_payment_failure_actual_events c_4 (cost=0.00..745.59 rows=2016 width=37) (actual time=5.843..11.173 rows=2016 loops=1)
Filter: (count = 20)
Rows Removed by Filter: 26351
-> Index Scan using actual_events_pkey on actual_events a_4 (cost=0.42..7.36 rows=1 width=69) (actual time=0.005..0.006 rows=1 loops=2016)
Index Cond: (id = c_4.id)
-> Index Scan using policies_policy_reference_key on policies p_4 (cost=0.42..0.46 rows=1 width=32) (actual time=0.020..0.020 rows=0 loops=2016)
Index Cond: (policy_reference = a_4.policy_reference)
Filter: (COALESCE(product_name, a_4.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..11981.00 rows=7 width=142) (actual time=1.231..54.126 rows=42 loops=1)
-> Nested Loop (cost=0.42..11284.72 rows=1474 width=121) (actual time=0.058..20.298 rows=1475 loops=1)
-> Seq Scan on eft_payment_failure_actual_events e (cost=0.00..49.74 rows=1474 width=56) (actual time=0.021..2.232 rows=1475 loops=1)
-> Index Scan using actual_events_pkey on actual_events a_5 (cost=0.42..7.61 rows=1 width=69) (actual time=0.006..0.007 rows=1 loops=1475)
Index Cond: (id = e.id)
-> Index Scan using policies_policy_reference_key on policies p_5 (cost=0.42..0.46 rows=1 width=32) (actual time=0.020..0.020 rows=0 loops=1475)
Index Cond: (policy_reference = a_5.policy_reference)
Filter: (COALESCE(product_name, a_5.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..16420.62 rows=10 width=142) (actual time=3.928..71.697 rows=49 loops=1)
-> Nested Loop (cost=0.42..15431.47 rows=2094 width=121) (actual time=0.033..26.288 rows=2058 loops=1)
-> Seq Scan on final_credit_card_failure_actual_events c_5 (cost=0.00..71.94 rows=2094 width=56) (actual time=0.019..3.690 rows=2058 loops=1)
-> Index Scan using actual_events_pkey on actual_events a_6 (cost=0.42..7.33 rows=1 width=69) (actual time=0.005..0.006 rows=1 loops=2058)
Index Cond: (id = c_5.id)
-> Index Scan using policies_policy_reference_key on policies p_6 (cost=0.42..0.46 rows=1 width=32) (actual time=0.019..0.019 rows=0 loops=2058)
Index Cond: (policy_reference = a_6.policy_reference)
Filter: (COALESCE(product_name, a_6.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1
-> Nested Loop (cost=0.84..5150.89 rows=3 width=98) (actual time=0.006..0.006 rows=0 loops=1)
-> Nested Loop (cost=0.42..4867.50 rows=600 width=77) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on flash_app_submitted_actual_events f (cost=0.00..16.00 rows=600 width=12) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using actual_events_pkey on actual_events a_7 (cost=0.42..8.08 rows=1 width=69) (never executed)
Index Cond: (id = f.id)
-> Index Scan using policies_policy_reference_key on policies p_7 (cost=0.42..0.46 rows=1 width=32) (never executed)
Index Cond: (policy_reference = a_7.policy_reference)
Filter: (COALESCE(product_name, a_7.product) = 'Hybrid All Cause'::text)
-> Subquery Scan on "*SELECT* 9" (cost=2012394.77..2012461.67 rows=2230 width=365) (actual time=42571.810..42573.175 rows=351 loops=1)
-> HashAggregate (cost=2012394.77..2012417.07 rows=2230 width=196) (actual time=42571.804..42572.424 rows=351 loops=1)
Group Key: e_1.id, (NULL::integer), e_1.id, e_1.policy_reference, e_1.provider_order_number, (CASE WHEN (e_1.flex_type = 'FLEX::Schedule::Events::ImagesReceivedExpected'::text) THEN 'Agent Ordered Paramed'::text ELSE requirement_type_code_to_name(e_1.requirement_type_code) END), (NULL::character varying), d.description, (NULL::text), (CASE WHEN (e_1.late_at IS NOT NULL) THEN 'Late'::text ELSE 'Pending'::text END), p_8.tracking, e_1.expected_at, e_1.late_at, e_1.requested_schedule_at, e_1.created_at, e_1.flex_type, (NULL::text), (COALESCE(p_8.product_name, ''::text))
-> Append (cost=10055.41..2012294.42 rows=2230 width=196) (actual time=827.179..42570.076 rows=351 loops=1)
-> Hash Join (cost=10055.41..22370.94 rows=261 width=162) (actual time=827.176..861.419 rows=9 loops=1)
Hash Cond: (e_1.flex_type = d.flex_type)
-> Hash Left Join (cost=10054.30..22300.34 rows=261 width=128) (actual time=825.057..859.137 rows=9 loops=1)
Hash Cond: (e_1.policy_reference = p_8.policy_reference)
Filter: (COALESCE(p_8.product_name, ''::text) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 5995
-> Seq Scan on expected_events e_1 (cost=0.00..8115.78 rows=52262 width=107) (actual time=0.209..132.615 rows=6004 loops=1)
Filter: ((clearing_event_id IS NULL) AND (fulfilling_event_id IS NULL) AND (leftover_marking_event_id IS NULL))
Rows Removed by Filter: 247215
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=661.986..661.986 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2279kB
-> Seq Scan on policies p_8 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.008..309.698 rows=226039 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=82) (actual time=0.053..0.053 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on expected_event_descriptions d (cost=0.00..1.05 rows=5 width=82) (actual time=0.026..0.031 rows=5 loops=1)
-> Hash Join (cost=74600.55..115249.57 rows=1149 width=199) (actual time=6246.860..7817.556 rows=225 loops=1)
Hash Cond: (e_2.id = a_8.id)
-> Seq Scan on exam_one_actual_events e_2 (cost=0.00..38061.48 rows=610348 width=88) (actual time=0.022..837.042 rows=619235 loops=1)
-> Hash (cost=74579.45..74579.45 rows=1688 width=115) (actual time=6168.678..6168.678 rows=239 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 50kB
-> Hash Join (cost=28208.22..74579.45 rows=1688 width=115) (actual time=3730.113..6168.169 rows=239 loops=1)
Hash Cond: (a_8.flex_type = r.actual_event_class)
-> Hash Left Join (cost=28204.30..74550.77 rows=2100 width=90) (actual time=3543.057..6155.122 rows=11091 loops=1)
Hash Cond: (a_8.policy_reference = p_9.policy_reference)
Filter: (COALESCE(p_9.product_name, a_8.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 641153
-> Seq Scan on actual_events a_8 (cost=18150.00..46283.65 rows=419927 width=69) (actual time=2349.790..3832.189 rows=652244 loops=1)
Filter: ((NOT (hashed SubPlan 7)) AND (flex_type <> 'FLEX::Events::OrderConfirmed'::text))
Rows Removed by Filter: 256141
SubPlan 7
-> HashAggregate (cost=17676.23..18055.25 rows=37902 width=4) (actual time=1777.786..2032.382 rows=214811 loops=1)
Group Key: expected_events_8.clearing_event_id
-> Append (cost=8261.68..17581.47 rows=37902 width=4) (actual time=202.344..1457.440 rows=216379 loops=1)
-> HashAggregate (cost=8261.68..8281.62 rows=1994 width=4) (actual time=202.342..275.758 rows=57352 loops=1)
Group Key: expected_events_8.clearing_event_id
-> Seq Scan on expected_events expected_events_8 (cost=0.00..8115.78 rows=58361 width=4) (actual time=0.013..114.177 rows=57680 loops=1)
Filter: (clearing_event_id IS NOT NULL)
Rows Removed by Filter: 195539
-> HashAggregate (cost=8561.75..8920.83 rows=35908 width=4) (actual time=526.501..740.541 rows=159027 loops=1)
Group Key: expected_events_9.fulfilling_event_id
-> Seq Scan on expected_events expected_events_9 (cost=0.00..8115.78 rows=178387 width=4) (actual time=0.019..258.565 rows=178590 loops=1)
Filter: (fulfilling_event_id IS NOT NULL)
Rows Removed by Filter: 74629
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=640.453..640.453 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_9 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.018..297.024 rows=226039 loops=1)
-> Hash (cost=3.41..3.41 rows=41 width=58) (actual time=0.151..0.151 rows=41 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on rules r (cost=0.00..3.41 rows=41 width=58) (actual time=0.014..0.087 rows=41 loops=1)
-> Nested Loop Left Join (cost=111472.72..152121.96 rows=1 width=199) (actual time=6281.792..8636.873 rows=26 loops=1)
Filter: (COALESCE(p_10.product_name, a_9.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 31919
-> Nested Loop (cost=111472.30..152120.55 rows=1 width=178) (actual time=5055.637..7873.579 rows=31945 loops=1)
-> Hash Semi Join (cost=111472.30..152117.03 rows=1 width=153) (actual time=5055.604..7266.581 rows=31945 loops=1)
Hash Cond: ((a_9.policy_reference = ex.policy_reference) AND (e_3.realtime_app_requirement_id = ex.realtime_app_requirement_id))
Join Filter: (ex.requirement_type_code <> e_3.requirement_type_code)
Rows Removed by Join Filter: 28
-> Hash Join (cost=46638.18..87181.55 rows=19308 width=161) (actual time=2979.108..5072.391 rows=41330 loops=1)
Hash Cond: (e_3.id = a_9.id)
-> Seq Scan on exam_one_actual_events e_3 (cost=0.00..38061.48 rows=610348 width=96) (actual time=0.008..749.194 rows=619235 loops=1)
-> Hash (cost=46283.65..46283.65 rows=28362 width=69) (actual time=2978.446..2978.446 rows=41330 loops=1)
Buckets: 65536 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3585kB
-> Seq Scan on actual_events a_9 (cost=18150.00..46283.65 rows=28362 width=69) (actual time=2360.820..2904.890 rows=41330 loops=1)
Filter: ((NOT (hashed SubPlan 6)) AND (flex_type = 'FLEX::Events::OrderConfirmed'::text))
Rows Removed by Filter: 867055
SubPlan 6
-> HashAggregate (cost=17676.23..18055.25 rows=37902 width=4) (actual time=1777.256..2033.075 rows=214811 loops=1)
Group Key: expected_events_6.clearing_event_id
-> Append (cost=8261.68..17581.47 rows=37902 width=4) (actual time=199.970..1461.633 rows=216379 loops=1)
-> HashAggregate (cost=8261.68..8281.62 rows=1994 width=4) (actual time=199.968..268.288 rows=57352 loops=1)
Group Key: expected_events_6.clearing_event_id
-> Seq Scan on expected_events expected_events_6 (cost=0.00..8115.78 rows=58361 width=4) (actual time=0.012..114.014 rows=57680 loops=1)
Filter: (clearing_event_id IS NOT NULL)
Rows Removed by Filter: 195539
-> HashAggregate (cost=8561.75..8920.83 rows=35908 width=4) (actual time=557.722..774.836 rows=159027 loops=1)
Group Key: expected_events_7.fulfilling_event_id
-> Seq Scan on expected_events expected_events_7 (cost=0.00..8115.78 rows=178387 width=4) (actual time=0.016..269.565 rows=178590 loops=1)
Filter: (fulfilling_event_id IS NOT NULL)
Rows Removed by Filter: 74629
-> Hash (cost=64828.87..64828.87 rows=350 width=21) (actual time=2076.410..2076.410 rows=16000 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 972kB
-> Nested Loop (cost=48076.26..64828.87 rows=350 width=21) (actual time=1628.807..2050.345 rows=16000 loops=1)
Join Filter: (ex.fulfilling_event_id = a2.id)
-> Hash Join (cost=48075.83..60125.98 rows=8122 width=29) (actual time=1628.729..1869.891 rows=16000 loops=1)
Hash Cond: (ex.fulfilling_event_id = e2.id)
-> Seq Scan on expected_events ex (cost=0.00..9382.67 rows=11536 width=25) (actual time=0.027..99.802 rows=16000 loops=1)
Filter: ((flex_type = 'FLEX::Schedule::Events::OrderConfirmedExpected'::text) AND (flex_status = 'Fulfilled'::expected_event_status_type))
Rows Removed by Filter: 237219
-> Hash (cost=38061.48..38061.48 rows=610348 width=4) (actual time=1627.028..1627.028 rows=619235 loops=1)
Buckets: 131072 Batches: 8 Memory Usage: 3743kB
-> Seq Scan on exam_one_actual_events e2 (cost=0.00..38061.48 rows=610348 width=4) (actual time=0.008..801.698 rows=619235 loops=1)
-> Index Scan using actual_events_pkey on actual_events a2 (cost=0.42..0.57 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=16000)
Index Cond: (id = e2.id)
Filter: (flex_type = 'FLEX::Events::OrderConfirmed'::text)
-> Seq Scan on rules r_1 (cost=0.00..3.51 rows=1 width=58) (actual time=0.004..0.013 rows=1 loops=31945)
Filter: (actual_event_class = 'FLEX::Events::OrderConfirmed'::text)
Rows Removed by Filter: 40
-> Index Scan using policies_policy_reference_key on policies p_10 (cost=0.42..1.14 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=31945)
Index Cond: (policy_reference = a_9.policy_reference)
-> Nested Loop (cost=104768.74..165538.95 rows=97 width=199) (actual time=8794.631..8794.631 rows=0 loops=1)
-> Seq Scan on rules r_2 (cost=0.00..3.51 rows=1 width=58) (actual time=0.038..0.066 rows=1 loops=1)
Filter: (actual_event_class = 'FLEX::Events::OrderConfirmed'::text)
Rows Removed by Filter: 40
-> Nested Loop Anti Join (cost=104768.74..165510.22 rows=97 width=174) (actual time=8794.548..8794.548 rows=0 loops=1)
Join Filter: ((ex_1.requirement_type_code <> e_4.requirement_type_code) AND (ex_1.policy_reference = a_10.policy_reference) AND (ex_1.realtime_app_requirement_id = e_4.realtime_app_requirement_id))
Rows Removed by Join Filter: 228852
-> Hash Left Join (cost=56692.48..100000.60 rows=97 width=182) (actual time=5366.058..6050.520 rows=26 loops=1)
Hash Cond: (a_10.policy_reference = p_11.policy_reference)
Filter: (COALESCE(p_11.product_name, a_10.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 41304
-> Hash Join (cost=46638.18..87181.55 rows=19308 width=161) (actual time=3022.410..5227.994 rows=41330 loops=1)
Hash Cond: (e_4.id = a_10.id)
-> Seq Scan on exam_one_actual_events e_4 (cost=0.00..38061.48 rows=610348 width=96) (actual time=0.009..863.445 rows=619235 loops=1)
-> Hash (cost=46283.65..46283.65 rows=28362 width=69) (actual time=3021.759..3021.759 rows=41330 loops=1)
Buckets: 65536 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3585kB
-> Seq Scan on actual_events a_10 (cost=18150.00..46283.65 rows=28362 width=69) (actual time=2382.010..2947.184 rows=41330 loops=1)
Filter: ((NOT (hashed SubPlan 5)) AND (flex_type = 'FLEX::Events::OrderConfirmed'::text))
Rows Removed by Filter: 867055
SubPlan 5
-> HashAggregate (cost=17676.23..18055.25 rows=37902 width=4) (actual time=1767.875..2054.996 rows=214811 loops=1)
Group Key: expected_events_4.clearing_event_id
-> Append (cost=8261.68..17581.47 rows=37902 width=4) (actual time=217.804..1451.322 rows=216379 loops=1)
-> HashAggregate (cost=8261.68..8281.62 rows=1994 width=4) (actual time=217.801..288.183 rows=57352 loops=1)
Group Key: expected_events_4.clearing_event_id
-> Seq Scan on expected_events expected_events_4 (cost=0.00..8115.78 rows=58361 width=4) (actual time=0.014..130.028 rows=57680 loops=1)
Filter: (clearing_event_id IS NOT NULL)
Rows Removed by Filter: 195539
-> HashAggregate (cost=8561.75..8920.83 rows=35908 width=4) (actual time=532.753..744.842 rows=159027 loops=1)
Group Key: expected_events_5.fulfilling_event_id
-> Seq Scan on expected_events expected_events_5 (cost=0.00..8115.78 rows=178387 width=4) (actual time=0.016..261.289 rows=178590 loops=1)
Filter: (fulfilling_event_id IS NOT NULL)
Rows Removed by Filter: 74629
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=647.925..647.925 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_11 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.011..300.076 rows=226039 loops=1)
-> Materialize (cost=48076.26..64830.62 rows=350 width=21) (actual time=68.624..94.221 rows=8803 loops=26)
-> Nested Loop (cost=48076.26..64828.87 rows=350 width=21) (actual time=1784.155..2202.497 rows=14410 loops=1)
Join Filter: (ex_1.fulfilling_event_id = a2_1.id)
-> Hash Join (cost=48075.83..60125.98 rows=8122 width=29) (actual time=1784.099..2037.004 rows=14410 loops=1)
Hash Cond: (ex_1.fulfilling_event_id = e2_1.id)
-> Seq Scan on expected_events ex_1 (cost=0.00..9382.67 rows=11536 width=25) (actual time=0.034..99.329 rows=16000 loops=1)
Filter: ((flex_type = 'FLEX::Schedule::Events::OrderConfirmedExpected'::text) AND (flex_status = 'Fulfilled'::expected_event_status_type))
Rows Removed by Filter: 237219
-> Hash (cost=38061.48..38061.48 rows=610348 width=4) (actual time=1782.313..1782.313 rows=619235 loops=1)
Buckets: 131072 Batches: 8 Memory Usage: 3743kB
-> Seq Scan on exam_one_actual_events e2_1 (cost=0.00..38061.48 rows=610348 width=4) (actual time=0.010..881.079 rows=619235 loops=1)
-> Index Scan using actual_events_pkey on actual_events a2_1 (cost=0.42..0.57 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=14410)
Index Cond: (id = e2_1.id)
Filter: (flex_type = 'FLEX::Events::OrderConfirmed'::text)
-> Nested Loop Left Join (cost=21220.94..56050.15 rows=72 width=98) (actual time=2906.462..5305.601 rows=19 loops=1)
Filter: (COALESCE(p_12.product_name, a_11.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 24062
-> Hash Join (cost=21220.53..48937.61 rows=14379 width=77) (actual time=2756.470..4732.065 rows=24081 loops=1)
Hash Cond: (a_11.id = r_3.id)
-> Seq Scan on actual_events a_11 (cost=18150.00..44042.21 rows=448288 width=69) (actual time=2431.672..3772.321 rows=693574 loops=1)
Filter: (NOT (hashed SubPlan 4))
Rows Removed by Filter: 214811
SubPlan 4
-> HashAggregate (cost=17676.23..18055.25 rows=37902 width=4) (actual time=1779.215..2081.438 rows=214811 loops=1)
Group Key: expected_events_2.clearing_event_id
-> Append (cost=8261.68..17581.47 rows=37902 width=4) (actual time=200.512..1454.619 rows=216379 loops=1)
-> HashAggregate (cost=8261.68..8281.62 rows=1994 width=4) (actual time=200.509..269.598 rows=57352 loops=1)
Group Key: expected_events_2.clearing_event_id
-> Seq Scan on expected_events expected_events_2 (cost=0.00..8115.78 rows=58361 width=4) (actual time=0.012..114.090 rows=57680 loops=1)
Filter: (clearing_event_id IS NOT NULL)
Rows Removed by Filter: 195539
-> HashAggregate (cost=8561.75..8920.83 rows=35908 width=4) (actual time=526.643..738.752 rows=159027 loops=1)
Group Key: expected_events_3.fulfilling_event_id
-> Seq Scan on expected_events expected_events_3 (cost=0.00..8115.78 rows=178387 width=4) (actual time=0.014..257.047 rows=178590 loops=1)
Filter: (fulfilling_event_id IS NOT NULL)
Rows Removed by Filter: 74629
-> Hash (cost=2711.05..2711.05 rows=28758 width=12) (actual time=127.016..127.016 rows=34388 loops=1)
Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 1990kB
-> Seq Scan on realtime_actual_events r_3 (cost=0.00..2711.05 rows=28758 width=12) (actual time=0.039..81.001 rows=34388 loops=1)
Filter: (event_category = 'Exam Related'::realtime_actual_event_category_type)
Rows Removed by Filter: 44356
-> Index Scan using policies_policy_reference_key on policies p_12 (cost=0.42..0.48 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=24081)
Index Cond: (policy_reference = a_11.policy_reference)
-> Hash Join (cost=1306544.27..1347066.27 rows=648 width=215) (actual time=7000.196..8297.496 rows=70 loops=1)
Hash Cond: (o.id = a_12.id)
-> Seq Scan on exam_one_actual_events o (cost=0.00..38061.48 rows=610348 width=88) (actual time=0.008..711.447 rows=619235 loops=1)
-> Hash (cost=1306532.37..1306532.37 rows=952 width=131) (actual time=6883.020..6883.020 rows=73 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 20kB
-> Hash Join (cost=10059.21..1306532.37 rows=952 width=131) (actual time=2805.934..6882.791 rows=73 loops=1)
Hash Cond: (a_12.flex_type = r_4.actual_event_class)
-> Hash Left Join (cost=10055.29..1306514.48 rows=1184 width=106) (actual time=2805.726..6882.344 rows=74 loops=1)
Hash Cond: (a_12.policy_reference = p_13.policy_reference)
Filter: (COALESCE(p_13.product_name, a_12.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 236196
-> Nested Loop (cost=0.99..1284594.03 rows=236748 width=85) (actual time=0.050..5429.260 rows=236270 loops=1)
-> Seq Scan on expected_events e_5 (cost=0.00..8115.78 rows=253378 width=24) (actual time=0.009..405.861 rows=253219 loops=1)
-> Bitmap Heap Scan on actual_events a_12 (cost=0.99..5.02 rows=2 width=69) (actual time=0.013..0.014 rows=1 loops=253219)
Recheck Cond: ((id = e_5.clearing_event_id) OR (id = e_5.fulfilling_event_id))
Heap Blocks: exact=236323
-> BitmapOr (cost=0.99..0.99 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=253219)
-> Bitmap Index Scan on actual_events_pkey (cost=0.00..0.49 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=253219)
Index Cond: (id = e_5.clearing_event_id)
-> Bitmap Index Scan on actual_events_pkey (cost=0.00..0.49 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=253219)
Index Cond: (id = e_5.fulfilling_event_id)
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=640.055..640.055 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_13 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.009..296.906 rows=226039 loops=1)
-> Hash (cost=3.41..3.41 rows=41 width=58) (actual time=0.140..0.140 rows=41 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on rules r_4 (cost=0.00..3.41 rows=41 width=58) (actual time=0.009..0.073 rows=41 loops=1)
-> Hash Join (cost=38067.96..102858.58 rows=1 width=98) (actual time=1156.478..1286.278 rows=1 loops=1)
Hash Cond: (r_5.id = a_13.id)
Join Filter: ((SubPlan 3) = a_13.id)
-> Seq Scan on realtime_actual_events r_5 (cost=0.00..2546.24 rows=65924 width=20) (actual time=0.006..101.394 rows=78744 loops=1)
-> Hash (cost=38067.06..38067.06 rows=72 width=90) (actual time=943.876..943.876 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Left Join (cost=10054.30..38067.06 rows=72 width=90) (actual time=924.445..943.868 rows=1 loops=1)
Hash Cond: (a_13.policy_reference = p_14.policy_reference)
Filter: (COALESCE(p_14.product_name, a_13.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 14855
-> Seq Scan on actual_events a_13 (cost=0.00..25892.21 rows=14495 width=69) (actual time=20.132..229.080 rows=14856 loops=1)
Filter: (flex_type = 'FLEX::Events::ImagesReceived'::text)
Rows Removed by Filter: 893529
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=639.667..639.667 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_14 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.011..296.641 rows=226039 loops=1)
SubPlan 2
-> Limit (cost=8749.23..8749.24 rows=1 width=9) (actual time=73.709..73.711 rows=1 loops=1)
-> HashAggregate (cost=8749.23..8749.24 rows=1 width=9) (actual time=73.704..73.704 rows=1 loops=1)
Group Key: e_6.provider_order_number
-> Seq Scan on expected_events e_6 (cost=0.00..8749.23 rows=3 width=9) (actual time=30.360..73.679 rows=3 loops=1)
Filter: (fulfilling_event_id = a_13.id)
Rows Removed by Filter: 253216
SubPlan 3
-> HashAggregate (cost=10649.56..10649.57 rows=1 width=4) (actual time=81.307..81.309 rows=1 loops=1)
Group Key: expected_events_1.fulfilling_event_id
-> Seq Scan on expected_events expected_events_1 (cost=0.00..10649.56 rows=1 width=4) (actual time=33.967..81.280 rows=3 loops=1)
Filter: ((flex_type = 'FLEX::Schedule::Events::ImagesReceivedExpected'::text) AND (flex_status = 'Fulfilled'::expected_event_status_type) AND (policy_reference = a_13.policy_reference) AND (realtime_app_requirement_id = r_5.app_requirement_id))
Rows Removed by Filter: 253216
-> Hash Join (cost=37572.66..51015.70 rows=1 width=98) (actual time=1542.314..1569.413 rows=1 loops=1)
Hash Cond: (r_6.id = a_14.id)
Join Filter: ((SubPlan 1) = a_14.id)
Rows Removed by Join Filter: 5
-> Seq Scan on realtime_actual_events r_6 (cost=0.00..2546.24 rows=65924 width=20) (actual time=0.005..95.176 rows=78744 loops=1)
-> Hash (cost=37572.53..37572.53 rows=10 width=90) (actual time=893.166..893.166 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Left Join (cost=10054.30..37572.53 rows=10 width=90) (actual time=866.318..893.136 rows=6 loops=1)
Hash Cond: (a_14.policy_reference = p_15.policy_reference)
Filter: (COALESCE(p_15.product_name, a_14.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 1887
-> Seq Scan on actual_events a_14 (cost=0.00..25892.21 rows=2032 width=69) (actual time=91.274..212.786 rows=1893 loops=1)
Filter: (flex_type = 'FLEX::Events::ExamRequirementDeleted'::text)
Rows Removed by Filter: 906492
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=637.854..637.854 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_15 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.012..297.509 rows=226039 loops=1)
SubPlan 1
-> HashAggregate (cost=10649.56..10649.57 rows=1 width=4) (actual time=83.261..83.261 rows=0 loops=6)
Group Key: expected_events.clearing_event_id
-> Seq Scan on expected_events (cost=0.00..10649.56 rows=1 width=4) (actual time=82.174..83.248 rows=0 loops=6)
Filter: ((flex_type = 'FLEX::Schedule::Events::OrderConfirmedExpected'::text) AND (flex_status = 'Cleared'::expected_event_status_type) AND (policy_reference = a_14.policy_reference) AND (realtime_app_requirement_id = r_6.app_requirement_id))
Rows Removed by Filter: 253219
-> Hash Right Join (cost=28774.94..37260.50 rows=103 width=115) (actual time=2299.142..2860.698 rows=1460 loops=1)
Hash Cond: (p_16.policy_reference = a_15.policy_reference)
Filter: (COALESCE(p_16.product_name, a_15.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 22669
-> Seq Scan on policies p_16 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.011..289.085 rows=226039 loops=1)
-> Hash (cost=28516.33..28516.33 rows=20689 width=94) (actual time=2292.126..2292.126 rows=24129 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 3868kB
-> Hash Join (cost=1296.50..28516.33 rows=20689 width=94) (actual time=336.819..2248.516 rows=24129 loops=1)
Hash Cond: (a_15.id = pps.id)
-> Seq Scan on actual_events a_15 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.007..1143.788 rows=908385 loops=1)
-> Hash (cost=1037.89..1037.89 rows=20689 width=29) (actual time=71.710..71.710 rows=24129 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1742kB
-> Seq Scan on pending_policy_status_actual_events pps (cost=0.00..1037.89 rows=20689 width=29) (actual time=0.024..39.344 rows=24129 loops=1)
-> Hash Left Join (cost=13774.92..66545.37 rows=186 width=175) (actual time=2067.469..3644.444 rows=2805 loops=1)
Hash Cond: (a_16.policy_reference = p_17.policy_reference)
Filter: (COALESCE(p_17.product_name, a_16.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 41551
-> Hash Join (cost=3720.62..52664.22 rows=37166 width=154) (actual time=795.902..2831.063 rows=44356 loops=1)
Hash Cond: (a_16.id = r_7.id)
-> Seq Scan on actual_events a_16 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.006..1179.885 rows=908385 loops=1)
-> Hash (cost=2711.05..2711.05 rows=37166 width=89) (actual time=151.350..151.350 rows=44356 loops=1)
Buckets: 32768 Batches: 2 Memory Usage: 2932kB
-> Seq Scan on realtime_actual_events r_7 (cost=0.00..2711.05 rows=37166 width=89) (actual time=5.948..76.604 rows=44356 loops=1)
Filter: (event_category = 'Pending Policy'::realtime_actual_event_category_type)
Rows Removed by Filter: 34388
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=641.303..641.303 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_17 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.012..298.315 rows=226039 loops=1)
-> Hash Join (cost=11246.23..41537.83 rows=151 width=123) (actual time=2108.427..3237.342 rows=498 loops=1)
Hash Cond: (a_17.policy_reference = p_18.policy_reference)
Join Filter: (COALESCE(p_18.product_name, a_17.product) = 'Hybrid All Cause'::text)
Rows Removed by Join Filter: 22424
-> Hash Join (cost=1191.92..28506.16 rows=30130 width=102) (actual time=491.542..2409.324 rows=30142 loops=1)
Hash Cond: (a_17.id = b.id)
-> Seq Scan on actual_events a_17 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.013..1164.391 rows=908385 loops=1)
-> Hash (cost=815.30..815.30 rows=30130 width=37) (actual time=121.803..121.803 rows=30142 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 2503kB
-> Seq Scan on policy_status_actual_events b (cost=0.00..815.30 rows=30130 width=37) (actual time=0.031..60.098 rows=30142 loops=1)
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=678.199..678.199 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_18 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.013..300.286 rows=226039 loops=1)
-> Hash Left Join (cost=12364.47..65147.72 rows=225 width=136) (actual time=1406.963..3858.606 rows=1125 loops=1)
Hash Cond: (a_18.policy_reference = p_19.policy_reference)
Filter: (COALESCE(p_19.product_name, a_18.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 43944
-> Hash Join (cost=2310.16..51228.84 rows=45074 width=115) (actual time=443.186..2945.125 rows=45069 loops=1)
Hash Cond: (a_18.id = b_1.id)
-> Seq Scan on actual_events a_18 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.014..1222.010 rows=908385 loops=1)
-> Hash (cost=1305.74..1305.74 rows=45074 width=50) (actual time=136.346..136.346 rows=45069 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 2436kB
-> Seq Scan on billing_method_actual_events b_1 (cost=0.00..1305.74 rows=45074 width=50) (actual time=0.023..67.679 rows=45069 loops=1)
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=653.178..653.178 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_19 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.012..297.719 rows=226039 loops=1)
-> Hash Left Join (cost=12259.73..64889.07 rows=215 width=135) (actual time=1589.099..4754.674 rows=1097 loops=1)
Hash Cond: (a_19.policy_reference = p_20.policy_reference)
Filter: (COALESCE(p_20.product_name, a_19.product) = 'Hybrid All Cause'::text)
Rows Removed by Filter: 41802
-> Hash Join (cost=2205.43..51081.44 rows=42908 width=114) (actual time=434.726..3943.742 rows=42899 loops=1)
Hash Cond: (a_19.id = pm.id)
-> Seq Scan on actual_events a_19 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.013..1236.632 rows=908385 loops=1)
-> Hash (cost=1249.08..1249.08 rows=42908 width=49) (actual time=128.117..128.117 rows=42899 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 2278kB
-> Seq Scan on payment_mode_actual_events pm (cost=0.00..1249.08 rows=42908 width=49) (actual time=0.024..64.730 rows=42899 loops=1)
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=640.140..640.140 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_20 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.013..297.718 rows=226039 loops=1)
-> Hash Join (cost=11671.19..42245.01 rows=181 width=115) (actual time=1225.541..3098.270 rows=2914 loops=1)
Hash Cond: (a_20.policy_reference = p_21.policy_reference)
Join Filter: (COALESCE(p_21.product_name, a_20.product) = 'Hybrid All Cause'::text)
Rows Removed by Join Filter: 32823
-> Hash Join (cost=1616.89..28992.45 rows=36262 width=94) (actual time=366.472..2319.695 rows=36267 loops=1)
Hash Cond: (a_20.id = b_2.id)
-> Seq Scan on actual_events a_20 (cost=0.00..23650.77 rows=896577 width=69) (actual time=0.014..1132.969 rows=908385 loops=1)
-> Hash (cost=1163.62..1163.62 rows=36262 width=29) (actual time=103.479..103.479 rows=36267 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2921kB
-> Seq Scan on inforce_at_tpa_actual_events b_2 (cost=0.00..1163.62 rows=36262 width=29) (actual time=0.025..55.264 rows=36267 loops=1)
-> Hash (cost=5685.80..5685.80 rows=225880 width=32) (actual time=643.046..643.046 rows=226039 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 2278kB
-> Seq Scan on policies p_21 (cost=0.00..5685.80 rows=225880 width=32) (actual time=0.012..298.036 rows=226039 loops=1)
Planning time: 57.753 ms
Execution time: 72068.110 ms
(436 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment