explain analyze SELECT o.oid as "OID" , n.nspname as "Schema" , o.oprname as "Name" , r.rolname as "Owner" , CASE WHEN o.oprkind='b' THEN 'infix(left and right)' WHEN o.oprkind='l' THEN 'prefix (left)' WHEN o.oprkind='r' THEN 'postfix (right)' END as "Kind" , CASE WHEN o.oprcanhash='t' THEN 'Yes' WHEN o.oprcanhash='f' THEN 'No' END as "Supports Hash Joins" , tl.typname as "Left Operand" , tr.typname as "Right Operand" , tres.typname as "Result Type" , ocom.oprname as "Commutator Operator" , onegate.oprname as "Negator Operator" , olsort.oprname as "Left Sort Operator" , orsort.oprname as "Right Sort Operator" , oltcm.oprname as "Less Than Operator" , ogtcm.oprname as "Greater Than Operator" , pcode.proname as "Operator Function" , prest.proname as "Restriction Selectivity Function" , pjoin.proname as "Join Selectivity Function" FROM pg_catalog.pg_operator o left join pg_catalog.pg_namespace n ON n.oid = o.oprnamespace left join pg_catalog.pg_roles r on r.oid=o.oprowner left join pg_catalog.pg_type tl on tl.oid=o.oprleft left join pg_catalog.pg_type tr on tl.oid=o.oprright left join pg_catalog.pg_type tres on tl.oid=o.oprresult left join pg_catalog.pg_operator ocom on ocom.oid=o.oprcom left join pg_catalog.pg_operator onegate on onegate.oid=o.oprnegate left join pg_catalog.pg_operator oneg on oneg.oid=o.oprnegate left join pg_catalog.pg_operator olsort on olsort.oid=o.oprlsortop left join pg_catalog.pg_operator orsort on orsort.oid=o.oprrsortop left join pg_catalog.pg_operator oltcm on oltcm.oid=o.oprltcmpop left join pg_catalog.pg_operator ogtcm on ogtcm.oid=o.oprgtcmpop left join pg_catalog.pg_proc pcode on pcode.oid=o.oprcode left join pg_catalog.pg_proc prest on prest.oid=o.oprrest left join pg_catalog.pg_proc pjoin on pjoin.oid=o.oprjoin WHERE n.nspname like 'public' ORDER BY lower(n.nspname), lower(o.oprname) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=2707.30..2707.66 rows=143 width=966) (actual time=22085.090..23284.736 rows=314064 loops=1) Sort Key: lower((n.nspname)::text), lower((o.oprname)::text) -> Hash Left Join (cost=2652.63..2702.18 rows=143 width=966) (actual time=3668.065..9163.877 rows=314064 loops=1) Hash Cond: ((o.oprcode)::oid = pcode.oid) -> Hash Left Join (cost=2567.13..2600.79 rows=143 width=906) (actual time=3661.366..7305.265 rows=314064 loops=1) Hash Cond: ((o.oprrest)::oid = prest.oid) -> Hash Left Join (cost=2481.63..2504.16 rows=143 width=846) (actual time=3654.311..6704.433 rows=314064 loops=1) Hash Cond: (o.oprlsortop = olsort.oid) -> Hash Left Join (cost=2453.91..2474.29 rows=143 width=786) (actual time=3651.827..6250.536 rows=314064 loops=1) Hash Cond: (o.oprrsortop = orsort.oid) -> Hash Left Join (cost=2426.18..2444.42 rows=143 width=726) (actual time=3649.287..5795.792 rows=314064 loops=1) Hash Cond: (o.oprltcmpop = oltcm.oid) -> Hash Left Join (cost=2398.46..2414.55 rows=143 width=666) (actual time=3646.749..5331.642 rows=314064 loops=1) Hash Cond: (o.oprgtcmpop = ogtcm.oid) -> Merge Left Join (cost=2370.73..2384.68 rows=143 width=606) (actual time=3643.994..4867.855 rows=314064 loops=1) Merge Cond: ("outer"."?column19?" = pjoin.oid) -> Sort (cost=2158.92..2159.27 rows=143 width=546) (actual time=3634.598..3800.837 rows=314064 loops=1) Sort Key: (o.oprjoin)::oid -> Hash Left Join (cost=49.70..2153.80 rows=143 width=546) (actual time=5.883..2061.807 rows=314064 loops=1) Hash Cond: (o.oprnegate = oneg.oid) -> Hash Left Join (cost=21.98..2123.93 rows=143 width=550) (actual time=4.022..1348.066 rows=314064 loops=1) Hash Cond: (o.oprowner = r.oid) -> Nested Loop Left Join (cost=20.89..2120.69 rows=143 width=490) (actual time=3.878..709.734 rows=314064 loops=1) Join Filter: (tl.oid = o.oprright) -> Nested Loop Left Join (cost=10.44..1103.17 rows=143 width=434) (actual time=3.843..73.766 rows=1128 loops=1) Join Filter: (tl.oid = o.oprresult) -> Nested Loop Left Join (cost=0.00..85.65 rows=143 width=374) (actual time=2.654..13.504 rows=192 loops=1) -> Nested Loop Left Join (cost=0.00..75.35 rows=143 width=310) (actual time=2.637..10.730 rows=192 loops=1) -> Nested Loop Left Join (cost=0.00..56.37 rows=143 width=250) (actual time=2.625..8.149 rows=192 loops=1) -> Nested Loop (cost=0.00..37.38 rows=143 width=186) (actual time=2.588..4.261 rows=192 loops=1) Join Filter: (n.oid = o.oprnamespace) -> Seq Scan on pg_namespace n (cost=0.00..1.07 rows=1 width=68) (actual time=0.052..0.054 rows=1 loops=1) Filter: (nspname ~~ 'public'::text) -> Seq Scan on pg_operator o (cost=0.00..25.58 rows=858 width=126) (actual time=0.022..3.408 rows=858 loops=1) -> Index Scan using pg_operator_oid_index on pg_operator onegate (cost=0.00..0.12 rows=1 width=68) (actual time=0.014..0.016 rows=1 loops=192) Index Cond: (onegate.oid = o.oprnegate) -> Index Scan using pg_operator_oid_index on pg_operator ocom (cost=0.00..0.12 rows=1 width=68) (actual time=0.008..0.010 rows=1 loops=192) Index Cond: (ocom.oid = o.oprcom) -> Index Scan using pg_type_oid_index on pg_type tl (cost=0.00..0.06 rows=1 width=68) (actual time=0.007..0.011 rows=1 loops=192) Index Cond: (tl.oid = o.oprleft) -> Materialize (cost=10.44..13.57 rows=313 width=64) (actual time=0.001..0.125 rows=313 loops=192) -> Seq Scan on pg_type tr2 (cost=0.00..10.13 rows=313 width=64) (actual time=0.013..0.328 rows=313 loops=1) -> Materialize (cost=10.44..13.57 rows=313 width=64) (actual time=0.000..0.133 rows=313 loops=1128) -> Seq Scan on pg_type tr (cost=0.00..10.13 rows=313 width=64) (actual time=0.007..0.320 rows=313 loops=1) -> Hash (cost=1.08..1.08 rows=4 width=68) (actual time=0.065..0.065 rows=5 loops=1) -> Subquery Scan r (cost=0.00..1.08 rows=4 width=68) (actual time=0.021..0.037 rows=5 loops=1) -> Seq Scan on pg_authid (cost=0.00..1.04 rows=4 width=118) (actual time=0.018..0.027 rows=5 loops=1) -> Hash (cost=25.58..25.58 rows=858 width=4) (actual time=1.797..1.797 rows=858 loops=1) -> Seq Scan on pg_operator oneg (cost=0.00..25.58 rows=858 width=4) (actual time=0.012..0.781 rows=858 loops=1) -> Sort (cost=211.81..217.71 rows=2360 width=68) (actual time=9.366..104.265 rows=216851 loops=1) Sort Key: pjoin.oid -> Seq Scan on pg_proc pjoin (cost=0.00..79.60 rows=2360 width=68) (actual time=0.039..2.700 rows=2360 loops=1) -> Hash (cost=25.58..25.58 rows=858 width=68) (actual time=2.589..2.589 rows=858 loops=1) -> Seq Scan on pg_operator ogtcm (cost=0.00..25.58 rows=858 width=68) (actual time=0.040..1.024 rows=858 loops=1) -> Hash (cost=25.58..25.58 rows=858 width=68) (actual time=2.448..2.448 rows=858 loops=1) -> Seq Scan on pg_operator oltcm (cost=0.00..25.58 rows=858 width=68) (actual time=0.016..0.909 rows=858 loops=1) -> Hash (cost=25.58..25.58 rows=858 width=68) (actual time=2.471..2.471 rows=858 loops=1) -> Seq Scan on pg_operator orsort (cost=0.00..25.58 rows=858 width=68) (actual time=0.015..0.923 rows=858 loops=1) -> Hash (cost=25.58..25.58 rows=858 width=68) (actual time=2.409..2.409 rows=858 loops=1) -> Seq Scan on pg_operator olsort (cost=0.00..25.58 rows=858 width=68) (actual time=0.022..0.928 rows=858 loops=1) -> Hash (cost=79.60..79.60 rows=2360 width=68) (actual time=6.983..6.983 rows=2360 loops=1) -> Seq Scan on pg_proc prest (cost=0.00..79.60 rows=2360 width=68) (actual time=0.020..2.793 rows=2360 loops=1) -> Hash (cost=79.60..79.60 rows=2360 width=68) (actual time=6.571..6.571 rows=2360 loops=1) -> Seq Scan on pg_proc pcode (cost=0.00..79.60 rows=2360 width=68) (actual time=0.017..2.593 rows=2360 loops=1) Total runtime: 24195.085 ms (65 rows)