Skip to content

Instantly share code, notes, and snippets.

@TJC

TJC/explain.txt

Created April 25, 2012 06:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save TJC/2487097 to your computer and use it in GitHub Desktop.
Save TJC/2487097 to your computer and use it in GitHub Desktop.
Download ZIP
Query plan that chews up gigabytes during EXPLAIN
Raw
explain.txt
This query plan results from a query that basically looks like
UPDATE line SET status = 'X'
WHERE line IN (
SELECT id FROM a_view_based_on_views
WHERE file_id = 1
)
AND file_id = 1;
QUERY PLAN
----------
Update on line (cost=89.58..311.29 rows=2 width=1090)
-> Nested Loop Semi Join (cost=89.58..152.51 rows=1 width=1020)
Join Filter: (foo_01_30.line.lineno = foo_01_30.org.line)
-> Seq Scan on line (cost=0.00..0.00 rows=1 width=66)
Filter: ((file_id = 725) AND ((status)::text = 'OK'::text))
-> Materialize (cost=89.58..152.49 rows=1 width=958)
-> Nested Loop (cost=89.58..152.49 rows=1 width=958)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=20.53..30.85 rows=1 width=200)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=17.35..27.00 rows=1 width=166)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=14.15..23.12 rows=1 width=132)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=10.95..19.25 rows=1 width=98)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=7.77..15.39 rows=1 width=64)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Hash Join (cost=7.77..11.48 rows=1 width=48)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Nested Loop (cost=69.05..121.62 rows=1 width=968)
Join Filter: ((foo_01_30.org.state = org.state) AND (foo_01_30.org.regid = org.regid) AND (foo_01_30.org.orgid = org.orgid))
-> Nested Loop (cost=69.05..117.61 rows=1 width=944)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid) AND (abs((sd_max(sd_max(sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3), sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3)), sd_max(sd_div_safe((COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric) - COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric)), COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric), 3), sd_div_safe(((COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)) - (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric))), (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)), 3))) - sd_min(sd_min(sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3), sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3)), sd_min(sd_div_safe((COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric) - COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric)), COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric), 3), sd_div_safe(((COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)) - (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric))), (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)), 3))))) > 0.50))
-> Nested Loop Left Join (cost=20.53..30.85 rows=1 width=456)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=17.35..27.00 rows=1 width=358)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=14.15..23.12 rows=1 width=260)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=10.95..19.25 rows=1 width=162)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=7.77..15.39 rows=1 width=64)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Hash Join (cost=7.77..11.48 rows=1 width=48)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Nested Loop Left Join (cost=48.52..83.15 rows=1 width=784)
-> Nested Loop (cost=48.52..82.59 rows=1 width=778)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=48.52..78.68 rows=1 width=762)
-> Nested Loop (cost=48.52..78.11 rows=1 width=756)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop (cost=48.52..74.20 rows=1 width=740)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=38.55..62.06 rows=1 width=656)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=35.37..58.20 rows=1 width=622)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop (cost=35.37..54.29 rows=1 width=606)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=23.73..38.63 rows=1 width=378)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=20.53..34.76 rows=1 width=344)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=20.53..30.85 rows=1 width=328)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=17.35..27.00 rows=1 width=294)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=14.15..23.12 rows=1 width=260)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=10.95..19.25 rows=1 width=162)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=7.77..15.39 rows=1 width=64)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Hash Join (cost=7.77..11.48 rows=1 width=48)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Hash Left Join (cost=11.64..15.55 rows=6 width=228)
Hash Cond: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Hash Left Join (cost=7.78..11.62 rows=6 width=130)
Hash Cond: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.56..3.56 rows=18 width=108)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Hash (cost=3.54..3.54 rows=18 width=108)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Hash Right Join (cost=9.97..12.04 rows=6 width=84)
Hash Cond: ((fteorg.state = org.state) AND (fteorg.regid = org.regid) AND (fteorg.orgid = org.orgid))
-> Seq Scan on fteorg_255 fteorg (cost=0.00..1.86 rows=18 width=36)
Filter: (setting = '1'::bpchar)
-> Hash (cost=9.86..9.86 rows=6 width=58)
-> Hash Right Join (cost=7.77..9.86 rows=6 width=58)
Hash Cond: ((fteorg.state = org.state) AND (fteorg.regid = org.regid) AND (fteorg.orgid = org.orgid))
-> Seq Scan on fteorg_725 fteorg (cost=0.00..1.88 rows=18 width=36)
Filter: (setting = '1'::bpchar)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Index Scan using fteorg_725_affc1_idx on fteorg_725 fteorg (cost=0.00..0.55 rows=1 width=16)
Index Cond: ((org.state = state) AND (org.regid = regid) AND (org.orgid = orgid) AND (setting = '1'::bpchar))
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Index Scan using fteorg_255_affc1_idx on fteorg_255 fteorg (cost=0.00..0.55 rows=1 width=16)
Index Cond: ((org.state = state) AND (org.regid = regid) AND (org.orgid = orgid) AND (setting = '1'::bpchar))
-> Append (cost=0.00..3.41 rows=34 width=24)
-> Seq Scan on org (cost=0.00..0.00 rows=1 width=33)
Filter: (file_id = 725)
-> Seq Scan on org_725 org (cost=0.00..3.41 rows=33 width=24)
Filter: (file_id = 725)
-> Nested Loop (cost=152.49..158.79 rows=1 width=1159)
-> HashAggregate (cost=152.49..152.50 rows=1 width=958)
-> Nested Loop (cost=89.58..152.49 rows=1 width=958)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=20.53..30.85 rows=1 width=200)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=17.35..27.00 rows=1 width=166)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=14.15..23.12 rows=1 width=132)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=10.95..19.25 rows=1 width=98)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=7.77..15.39 rows=1 width=64)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Hash Join (cost=7.77..11.48 rows=1 width=48)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Nested Loop (cost=69.05..121.62 rows=1 width=968)
Join Filter: ((foo_01_30.org.state = org.state) AND (foo_01_30.org.regid = org.regid) AND (foo_01_30.org.orgid = org.orgid))
-> Nested Loop (cost=69.05..117.61 rows=1 width=944)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid) AND (abs((sd_max(sd_max(sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3), sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3)), sd_max(sd_div_safe((COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric) - COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric)), COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric), 3), sd_div_safe(((COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)) - (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric))), (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)), 3))) - sd_min(sd_min(sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3), sd_div_safe((COALESCE(tmpinner.total, 0::numeric) - COALESCE(tmpinner.total, 0::numeric)), COALESCE(tmpinner.total, 0::numeric), 3)), sd_min(sd_div_safe((COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric) - COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric)), COALESCE((((fteorg.ftemed + fteorg.ftenurses) + fteorg.ftedhp) + fteorg.ftepcare), 0::numeric), 3), sd_div_safe(((COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)) - (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric))), (COALESCE(tmpinner.total, 0::numeric) + COALESCE(tmpinner.total, 0::numeric)), 3))))) > 0.50))
-> Nested Loop Left Join (cost=20.53..30.85 rows=1 width=456)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=17.35..27.00 rows=1 width=358)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=14.15..23.12 rows=1 width=260)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=10.95..19.25 rows=1 width=162)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=7.77..15.39 rows=1 width=64)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Hash Join (cost=7.77..11.48 rows=1 width=48)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Nested Loop Left Join (cost=48.52..83.15 rows=1 width=784)
-> Nested Loop (cost=48.52..82.59 rows=1 width=778)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=48.52..78.68 rows=1 width=762)
-> Nested Loop (cost=48.52..78.11 rows=1 width=756)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop (cost=48.52..74.20 rows=1 width=740)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=38.55..62.06 rows=1 width=656)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=35.37..58.20 rows=1 width=622)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop (cost=35.37..54.29 rows=1 width=606)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=23.73..38.63 rows=1 width=378)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=20.53..34.76 rows=1 width=344)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Nested Loop Left Join (cost=20.53..30.85 rows=1 width=328)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=17.35..27.00 rows=1 width=294)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=14.15..23.12 rows=1 width=260)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop Left Join (cost=10.95..19.25 rows=1 width=162)
Join Filter: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Nested Loop (cost=7.77..15.39 rows=1 width=64)
Join Filter: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Hash Join (cost=7.77..11.48 rows=1 width=48)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=44)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Hash Left Join (cost=11.64..15.55 rows=6 width=228)
Hash Cond: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Hash Left Join (cost=7.78..11.62 rows=6 width=130)
Hash Cond: ((org.state = tmpinner.state) AND (org.regid = tmpinner.regid) AND (org.orgid = tmpinner.orgid))
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.56..3.56 rows=18 width=108)
-> Subquery Scan on tmpinner (cost=3.20..3.56 rows=18 width=108)
-> HashAggregate (cost=3.20..3.38 rows=18 width=18)
-> Seq Scan on admi_725 admi (cost=0.00..2.60 rows=60 width=18)
-> Hash (cost=3.54..3.54 rows=18 width=108)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=108)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Subquery Scan on tmpinner (cost=3.18..3.54 rows=18 width=44)
-> HashAggregate (cost=3.18..3.36 rows=18 width=18)
-> Seq Scan on admi_255 admi (cost=0.00..2.59 rows=59 width=18)
-> Hash Right Join (cost=9.97..12.04 rows=6 width=84)
Hash Cond: ((fteorg.state = org.state) AND (fteorg.regid = org.regid) AND (fteorg.orgid = org.orgid))
-> Seq Scan on fteorg_255 fteorg (cost=0.00..1.86 rows=18 width=36)
Filter: (setting = '1'::bpchar)
-> Hash (cost=9.86..9.86 rows=6 width=58)
-> Hash Right Join (cost=7.77..9.86 rows=6 width=58)
Hash Cond: ((fteorg.state = org.state) AND (fteorg.regid = org.regid) AND (fteorg.orgid = org.orgid))
-> Seq Scan on fteorg_725 fteorg (cost=0.00..1.88 rows=18 width=36)
Filter: (setting = '1'::bpchar)
-> Hash (cost=7.67..7.67 rows=6 width=32)
-> Hash Join (cost=3.91..7.67 rows=6 width=32)
Hash Cond: ((org.state = org.state) AND (org.regid = org.regid) AND (org.orgid = org.orgid))
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Hash (cost=3.33..3.33 rows=33 width=16)
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Seq Scan on org_725 org (cost=0.00..3.33 rows=33 width=16)
-> Index Scan using fteorg_725_affc1_idx on fteorg_725 fteorg (cost=0.00..0.55 rows=1 width=16)
Index Cond: ((org.state = state) AND (org.regid = regid) AND (org.orgid = orgid) AND (setting = '1'::bpchar))
-> Seq Scan on org_255 org (cost=0.00..3.33 rows=33 width=16)
-> Index Scan using fteorg_255_affc1_idx on fteorg_255 fteorg (cost=0.00..0.55 rows=1 width=16)
Index Cond: ((org.state = state) AND (org.regid = regid) AND (org.orgid = orgid) AND (setting = '1'::bpchar))
-> Append (cost=0.00..3.41 rows=34 width=24)
-> Seq Scan on org (cost=0.00..0.00 rows=1 width=33)
Filter: (file_id = 725)
-> Seq Scan on org_725 org (cost=0.00..3.41 rows=33 width=24)
Filter: (file_id = 725)
-> Index Scan using line_725_lineno_idx on line_725 line (cost=0.00..6.27 rows=1 width=205)
Index Cond: (lineno = foo_01_30.org.line)
Filter: ((file_id = 725) AND ((status)::text = 'OK'::text))
(353 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment