Skip to content

Instantly share code, notes, and snippets.

@varpa89

varpa89/gist:cb7d806f8ea0f4740c0f

Created October 20, 2015 12:35
Show Gist options
  • Save varpa89/cb7d806f8ea0f4740c0f to your computer and use it in GitHub Desktop.
Save varpa89/cb7d806f8ea0f4740c0f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
explain ANALYZE WITH RECURSIVE sub_orgs(gid, organization_type) AS
(SELECT o.gid, o.organization_type FROM en_organization o
WHERE gid = 'b55d19bc-1ecc-68d9-bf7a-78ec21808327'
UNION SELECT
o.gid, o.organization_type FROM sub_orgs so, en_organization o
WHERE o.management_organization_gid = so.gid AND o.organization_type IN ('MOUO', 'ROUO'))
SELECT * FROM sub_orgs;
"CTE Scan on sub_orgs (cost=16.32..17.14 rows=41 width=36)"
" CTE sub_orgs"
" -> Recursive Union (cost=0.00..16.32 rows=41 width=20)"
" -> Seq Scan on en_organization o (cost=0.00..1.10 rows=1 width=20)"
" -> Hash Join (cost=1.15..1.44 rows=4 width=20)"
" Hash Cond: (so.gid = o_1.management_organization_gid)"
" Filter: (gid = 'b55d19bc-1ecc-68d9-bf7a-78ec21808327'::uuid)"
" -> WorkTable Scan on sub_orgs so (cost=0.00..0.20 rows=10 width=16)"
" -> Hash (cost=1.10..1.10 rows=4 width=36)"
" -> Seq Scan on en_organization o_1 (cost=0.00..1.10 rows=4 width=36)"
" Filter: ((organization_type)::text = ANY ('{MOUO,ROUO}'::text[]))"
"CTE Scan on sub_orgs (cost=16.32..17.14 rows=41 width=36) (actual time=0.019..0.091 rows=4 loops=1)"
" CTE sub_orgs"
" -> Recursive Union (cost=0.00..16.32 rows=41 width=20) (actual time=0.017..0.081 rows=4 loops=1)"
" -> Seq Scan on en_organization o (cost=0.00..1.10 rows=1 width=20) (actual time=0.013..0.015 rows=1 loops=1)"
" Filter: (gid = 'b55d19bc-1ecc-68d9-bf7a-78ec21808327'::uuid)"
" Rows Removed by Filter: 13"
" -> Hash Join (cost=1.15..1.44 rows=4 width=20) (actual time=0.014..0.017 rows=1 loops=3)"
" Hash Cond: (so.gid = o_1.management_organization_gid)"
" -> WorkTable Scan on sub_orgs so (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..0.002 rows=1 loops=3)"
" -> Hash (cost=1.10..1.10 rows=4 width=36) (actual time=0.026..0.026 rows=3 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on en_organization o_1 (cost=0.00..1.10 rows=4 width=36) (actual time=0.005..0.016 rows=10 loops=1)"
" Filter: ((organization_type)::text = ANY ('{MOUO,ROUO}'::text[]))"
" Rows Removed by Filter: 4"
"Total runtime: 0.137 ms"
explain ANALYZE WITH RECURSIVE sub_orgs(gid, organization_type) AS
(SELECT o.gid, o.organization_type FROM en_organization o
WHERE gid = 'b55d19bc-1ecc-68d9-bf7a-78ec21808327'
UNION SELECT
o.gid, o.organization_type FROM sub_orgs so, en_organization o
WHERE o.management_organization_gid = so.gid)
SELECT * FROM sub_orgs WHERE organization_type IN ('MOUO', 'ROUO');
"CTE Scan on sub_orgs (cost=17.57..19.17 rows=2 width=36)"
" Filter: ((organization_type)::text = ANY ('{MOUO,ROUO}'::text[]))"
" CTE sub_orgs"
" -> Recursive Union (cost=0.00..17.57 rows=71 width=20)"
" -> Seq Scan on en_organization o (cost=0.00..1.10 rows=1 width=20)"
" Filter: (gid = 'b55d19bc-1ecc-68d9-bf7a-78ec21808327'::uuid)"
" -> Hash Join (cost=0.33..1.51 rows=7 width=20)"
" Hash Cond: (o_1.management_organization_gid = so.gid)"
" -> Seq Scan on en_organization o_1 (cost=0.00..1.08 rows=8 width=36)"
" -> Hash (cost=0.20..0.20 rows=10 width=16)"
" -> WorkTable Scan on sub_orgs so (cost=0.00..0.20 rows=10 width=16)"
"CTE Scan on sub_orgs (cost=17.57..19.17 rows=2 width=36) (actual time=0.023..0.201 rows=4 loops=1)"
" Filter: ((organization_type)::text = ANY ('{MOUO,ROUO}'::text[]))"
" Rows Removed by Filter: 4"
" CTE sub_orgs"
" -> Recursive Union (cost=0.00..17.57 rows=71 width=20) (actual time=0.018..0.181 rows=8 loops=1)"
" -> Seq Scan on en_organization o (cost=0.00..1.10 rows=1 width=20) (actual time=0.013..0.015 rows=1 loops=1)"
" Filter: (gid = 'b55d19bc-1ecc-68d9-bf7a-78ec21808327'::uuid)"
" Rows Removed by Filter: 13"
" -> Hash Join (cost=0.33..1.51 rows=7 width=20) (actual time=0.022..0.036 rows=2 loops=4)"
" Hash Cond: (o_1.management_organization_gid = so.gid)"
" -> Seq Scan on en_organization o_1 (cost=0.00..1.08 rows=8 width=36) (actual time=0.002..0.015 rows=14 loops=4)"
" -> Hash (cost=0.20..0.20 rows=10 width=16) (actual time=0.005..0.005 rows=2 loops=4)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> WorkTable Scan on sub_orgs so (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..0.002 rows=2 loops=4)"
"Total runtime: 0.242 ms"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment