SELECT ex.ex, ex.lv, ex.tt, ex.td, dnsrc.ex AS trex, ((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2) AS trq FROM ex INNER JOIN lv ON (lv.lv = ex.lv) INNER JOIN dn ON (dn.ex = ex.ex) INNER JOIN mn ON (mn.mn = dn.mn) INNER JOIN ap ON (ap.ap = mn.ap) INNER JOIN dn AS dn2 ON (dn2.mn = dn.mn) INNER JOIN dn AS dn3 ON (dn3.ex = dn2.ex) INNER JOIN dn AS dnsrc ON (dnsrc.mn = dn3.mn) INNER JOIN mn AS mnsrc ON (mnsrc.mn = dnsrc.mn) INNER JOIN ap AS apsrc ON (apsrc.ap = mnsrc.ap) INNER JOIN ex AS exsrc ON (exsrc.ex = dnsrc.ex) INNER JOIN lv AS lvsrc ON (lvsrc.lv = exsrc.lv) WHERE dn.ex != dn2.ex AND dn3.ex != dnsrc.ex AND mn.ap != mnsrc.ap AND dn.ex != dnsrc.ex AND lcvc(lv.lc, lv.vc) IN ('zul-000') AND lcvc(lvsrc.lc, lvsrc.vc) IN ('gle-000') AND exsrc.tt IN ('doras') GROUP BY ex.ex, dnsrc.ex ORDER BY trq desc LIMIT 2000; EXPLAIN ANALYZE with geqo on and default_statistics_target = 100: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3603.82..3603.82 rows=2 width=57) (actual time=674.046..674.050 rows=31 loops=1) -> Sort (cost=3603.82..3603.82 rows=2 width=57) (actual time=674.044..674.046 rows=31 loops=1) Sort Key: (((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2)) Sort Method: quicksort Memory: 27kB -> HashAggregate (cost=3603.60..3603.82 rows=2 width=57) (actual time=672.799..674.008 rows=31 loops=1) Group Key: ex.ex, dnsrc.ex -> Nested Loop (cost=3075.23..3603.59 rows=2 width=57) (actual time=8.048..671.384 rows=766 loops=1) -> Nested Loop (cost=3075.17..3603.43 rows=2 width=55) (actual time=8.036..669.963 rows=766 loops=1) Join Filter: (mn.ap <> mnsrc.ap) Rows Removed by Join Filter: 3793 -> Hash Join (cost=3075.08..3603.21 rows=2 width=63) (actual time=8.012..660.229 rows=4559 loops=1) Hash Cond: (dn.ex = ex.ex) -> Nested Loop (cost=3.04..510.58 rows=20564 width=26) (actual time=0.939..553.677 rows=1047985 loops=1) Join Filter: (dn.ex <> dnsrc.ex) Rows Removed by Join Filter: 5396 -> Nested Loop (cost=2.92..277.03 rows=118 width=22) (actual time=0.934..120.245 rows=40543 loops=1) -> Nested Loop (cost=2.87..267.76 rows=118 width=16) (actual time=0.921..62.140 rows=40543 loops=1) -> Hash Join (cost=2.76..266.08 rows=1 width=12) (actual time=0.899..6.318 rows=1254 loops=1) Hash Cond: (exsrc.lv = lvsrc.lv) -> Nested Loop (cost=0.43..263.08 rows=871 width=16) (actual time=0.173..5.788 rows=1516 loops=1) -> Nested Loop (cost=0.34..164.79 rows=871 width=20) (actual time=0.142..1.239 rows=1516 loops=1) -> Nested Loop (cost=0.23..155.09 rows=5 width=12) (actual time=0.101..0.308 rows=43 loops=1) -> Index Scan using ex_tt_idx on ex exsrc (cost=0.11..2.57 rows=2 width=8) (actual time=0.060..0.065 rows=5 loops=1) Index Cond: (tt = 'doras'::text) -> Index Scan using dn_ex_idx on dn dnsrc (cost=0.11..75.55 rows=71 width=8) (actual time=0.026..0.045 rows=9 loops=5) Index Cond: (ex = exsrc.ex) -> Index Only Scan using dn_mn_ex_key on dn dn3 (cost=0.11..1.13 rows=81 width=8) (actual time=0.007..0.016 rows=35 loops=43) Index Cond: (mn = dnsrc.mn) Filter: (ex <> dnsrc.ex) Rows Removed by Filter: 1 Heap Fetches: 0 -> Index Scan using mn_pkey on mn mnsrc (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1516) Index Cond: (mn = dnsrc.mn) -> Hash (cost=2.32..2.32 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Index Scan using lv_lcvc_idx on lv lvsrc (cost=0.11..2.32 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1) Index Cond: (lcvc(lc, vc) = 'gle-000'::bpchar) -> Index Scan using dn_ex_idx on dn dn2 (cost=0.11..0.98 rows=71 width=8) (actual time=0.006..0.040 rows=32 loops=1254) Index Cond: (ex = dn3.ex) -> Index Scan using ap_pkey on ap apsrc (cost=0.06..0.07 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=40543) Index Cond: (ap = mnsrc.ap) -> Index Only Scan using dn_mn_ex_key on dn (cost=0.11..1.13 rows=81 width=8) (actual time=0.003..0.007 rows=26 loops=40543) Index Cond: (mn = dn2.mn) Filter: (ex <> dn2.ex) Rows Removed by Filter: 1 Heap Fetches: 764 -> Hash (cost=3049.48..3049.48 rows=2149 width=41) (actual time=5.541..5.541 rows=2105 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 121kB -> Nested Loop (cost=0.19..3049.48 rows=2149 width=41) (actual time=0.044..5.110 rows=2105 loops=1) -> Index Scan using lv_lcvc_idx on lv (cost=0.11..2.32 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1) Index Cond: (lcvc(lc, vc) = 'zul-000'::bpchar) -> Index Scan using ex_lv_idx on ex (cost=0.09..3017.95 rows=2921 width=41) (actual time=0.031..4.799 rows=2105 loops=1) Index Cond: (lv = lv.lv) -> Index Scan using mn_pkey on mn (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4559) Index Cond: (mn = dn.mn) -> Index Scan using ap_pkey on ap (cost=0.06..0.07 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=766) Index Cond: (ap = mn.ap) Planning time: 110.866 ms Execution time: 674.268 ms EXPLAIN ANALYZE with geqo off and default_statistics_target = 100: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=114.58..114.58 rows=1 width=57) (actual time=11155.010..11155.011 rows=31 loops=1) -> Sort (cost=114.58..114.58 rows=1 width=57) (actual time=11155.010..11155.010 rows=31 loops=1) Sort Key: (((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2)) Sort Method: quicksort Memory: 27kB -> HashAggregate (cost=114.47..114.58 rows=1 width=57) (actual time=11153.788..11154.985 rows=31 loops=1) Group Key: ex.ex, dnsrc.ex -> Nested Loop (cost=1.10..114.46 rows=1 width=57) (actual time=6.810..11152.329 rows=766 loops=1) -> Nested Loop (cost=1.05..114.38 rows=1 width=55) (actual time=6.807..11150.787 rows=766 loops=1) Join Filter: (mn.ap <> mnsrc.ap) Rows Removed by Join Filter: 3793 -> Nested Loop (cost=0.96..114.27 rows=1 width=63) (actual time=6.803..11137.899 rows=4559 loops=1) -> Nested Loop (cost=0.90..114.19 rows=1 width=57) (actual time=6.797..11124.582 rows=4559 loops=1) -> Nested Loop (cost=0.85..110.70 rows=27 width=57) (actual time=0.134..3802.781 rows=1047985 loops=1) -> Nested Loop (cost=0.76..107.61 rows=27 width=20) (actual time=0.115..805.442 rows=1047985 loops=1) Join Filter: ((dn.ex <> dn2.ex) AND (dn.ex <> dnsrc.ex)) Rows Removed by Join Filter: 45939 -> Nested Loop (cost=0.65..85.78 rows=11 width=28) (actual time=0.110..231.112 rows=40543 loops=1) -> Nested Loop (cost=0.56..84.54 rows=11 width=20) (actual time=0.102..54.952 rows=40543 loops=1) -> Nested Loop (cost=0.45..82.85 rows=1 width=16) (actual time=0.094..1.056 rows=1254 loops=1) -> Nested Loop (cost=0.33..80.91 rows=1 width=8) (actual time=0.081..0.132 rows=26 loops=1) -> Nested Loop (cost=0.22..4.65 rows=1 width=4) (actual time=0.071..0.073 rows=1 loops=1) -> Index Scan using lv_lcvc_idx on lv lvsrc (cost=0.11..2.32 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1) Index Cond: (lcvc(lc, vc) = 'gle-000'::bpchar) -> Index Scan using ex_lv_tt_key on ex exsrc (cost=0.11..2.32 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=1) Index Cond: ((lv = lvsrc.lv) AND (tt = 'doras'::text)) -> Index Scan using dn_ex_idx on dn dnsrc (cost=0.11..75.55 rows=71 width=8) (actual time=0.010..0.048 rows=26 loops=1) Index Cond: (ex = exsrc.ex) -> Index Only Scan using dn_mn_ex_key on dn dn3 (cost=0.11..1.13 rows=81 width=8) (actual time=0.004..0.025 rows=48 loops=26) Index Cond: (mn = dnsrc.mn) Filter: (ex <> dnsrc.ex) Rows Removed by Filter: 1 Heap Fetches: 0 -> Index Scan using dn_ex_idx on dn dn2 (cost=0.11..0.98 rows=71 width=8) (actual time=0.005..0.037 rows=32 loops=1254) Index Cond: (ex = dn3.ex) -> Index Scan using mn_pkey on mn (cost=0.09..0.10 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=40543) Index Cond: (mn = dn2.mn) -> Index Only Scan using dn_mn_ex_key on dn (cost=0.11..1.09 rows=81 width=8) (actual time=0.003..0.008 rows=27 loops=40543) Index Cond: (mn = mn.mn) Heap Fetches: 764 -> Index Scan using ex_pkey on ex (cost=0.09..0.10 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=1047985) Index Cond: (ex = dn.ex) -> Index Scan using lv_pkey on lv (cost=0.06..0.12 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1047985) Index Cond: (lv = ex.lv) Filter: (lcvc(lc, vc) = 'zul-000'::bpchar) Rows Removed by Filter: 1 -> Index Scan using ap_pkey on ap (cost=0.06..0.07 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=4559) Index Cond: (ap = mn.ap) -> Index Scan using mn_pkey on mn mnsrc (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4559) Index Cond: (mn = dnsrc.mn) -> Index Scan using ap_pkey on ap apsrc (cost=0.06..0.07 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=766) Index Cond: (ap = mnsrc.ap) Planning time: 163.196 ms Execution time: 11155.197 ms EXPLAIN ANALYZE with geqo on and default_statistics_target = 100: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=2786.77..2786.77 rows=1 width=57) (actual time=790.758..790.761 rows=31 loops=1) -> Sort (cost=2786.77..2786.77 rows=1 width=57) (actual time=790.758..790.761 rows=31 loops=1) Sort Key: (((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2)) Sort Method: quicksort Memory: 27kB -> HashAggregate (cost=2786.65..2786.77 rows=1 width=57) (actual time=789.532..790.738 rows=31 loops=1) Group Key: ex.ex, dnsrc.ex -> Nested Loop (cost=116.90..2786.65 rows=1 width=57) (actual time=627.615..788.824 rows=766 loops=1) -> Nested Loop (cost=116.85..2786.57 rows=1 width=55) (actual time=627.610..787.758 rows=766 loops=1) Join Filter: (mn.ap <> mnsrc.ap) Rows Removed by Join Filter: 3793 -> Hash Join (cost=116.76..2786.46 rows=1 width=63) (actual time=627.601..780.862 rows=4559 loops=1) Hash Cond: (ex.ex = dn.ex) -> Nested Loop (cost=0.19..2668.27 rows=2148 width=41) (actual time=0.028..1.977 rows=2105 loops=1) -> Index Scan using lv_lcvc_idx on lv (cost=0.11..2.32 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1) Index Cond: (lcvc(lc, vc) = 'zul-000'::bpchar) -> Index Scan using ex_lv_idx on ex (cost=0.09..2640.46 rows=2549 width=41) (actual time=0.012..1.642 rows=2105 loops=1) Index Cond: (lv = lv.lv) -> Hash (cost=86.23..86.23 rows=2889 width=26) (actual time=625.609..625.609 rows=1047985 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 61406kB -> Nested Loop (cost=3.04..86.23 rows=2889 width=26) (actual time=0.468..478.534 rows=1047985 loops=1) Join Filter: (dn.ex <> dnsrc.ex) Rows Removed by Join Filter: 5396 -> Nested Loop (cost=2.92..69.60 rows=72 width=22) (actual time=0.465..94.991 rows=40543 loops=1) -> Nested Loop (cost=2.87..63.94 rows=72 width=16) (actual time=0.462..39.185 rows=40543 loops=1) -> Hash Join (cost=2.76..63.53 rows=1 width=12) (actual time=0.454..4.564 rows=1254 loops=1) Hash Cond: (exsrc.lv = lvsrc.lv) -> Nested Loop (cost=0.43..61.01 rows=241 width=16) (actual time=0.058..4.104 rows=1516 loops=1) -> Nested Loop (cost=0.34..33.88 rows=241 width=20) (actual time=0.051..0.773 rows=1516 loops=1) -> Nested Loop (cost=0.23..32.51 rows=6 width=12) (actual time=0.023..0.090 rows=43 loops=1) -> Index Scan using ex_tt_idx on ex exsrc (cost=0.11..2.57 rows=2 width=8) (actual time=0.015..0.019 rows=5 loops=1) Index Cond: (tt = 'doras'::text) -> Index Scan using dn_ex_idx on dn dnsrc (cost=0.11..14.84 rows=13 width=8) (actual time=0.006..0.012 rows=9 loops=5) Index Cond: (ex = exsrc.ex) -> Index Only Scan using dn_mn_ex_key on dn dn3 (cost=0.11..0.18 rows=5 width=8) (actual time=0.004..0.011 rows=35 loops=43) Index Cond: (mn = dnsrc.mn) Filter: (ex <> dnsrc.ex) Rows Removed by Filter: 1 Heap Fetches: 0 -> Index Scan using mn_pkey on mn mnsrc (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1516) Index Cond: (mn = dnsrc.mn) -> Hash (cost=2.32..2.32 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Index Scan using lv_lcvc_idx on lv lvsrc (cost=0.11..2.32 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1) Index Cond: (lcvc(lc, vc) = 'gle-000'::bpchar) -> Index Scan using dn_ex_idx on dn dn2 (cost=0.11..0.28 rows=13 width=8) (actual time=0.004..0.023 rows=32 loops=1254) Index Cond: (ex = dn3.ex) -> Index Scan using ap_pkey on ap apsrc (cost=0.06..0.07 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=40543) Index Cond: (ap = mnsrc.ap) -> Index Only Scan using dn_mn_ex_key on dn (cost=0.11..0.18 rows=5 width=8) (actual time=0.003..0.006 rows=26 loops=40543) Index Cond: (mn = dn2.mn) Filter: (ex <> dn2.ex) Rows Removed by Filter: 1 Heap Fetches: 764 -> Index Scan using mn_pkey on mn (cost=0.09..0.10 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4559) Index Cond: (mn = dn.mn) -> Index Scan using ap_pkey on ap (cost=0.06..0.07 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=766) Index Cond: (ap = mn.ap) Planning time: 381.122 ms Execution time: 790.886 ms EXPLAIN ANALYZE with geqo off and default_statistics_target = 100: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=22.31..22.31 rows=1 width=57) (actual time=11552.198..11552.200 rows=31 loops=1) -> Sort (cost=22.31..22.31 rows=1 width=57) (actual time=11552.196..11552.196 rows=31 loops=1) Sort Key: (((uiuq_score(array_agg(ap.ui), array_agg(ap.uq)) + uiuq_score(array_agg(apsrc.ui), array_agg(apsrc.uq))) / 2)) Sort Method: quicksort Memory: 27kB -> HashAggregate (cost=22.20..22.31 rows=1 width=57) (actual time=11550.953..11552.178 rows=31 loops=1) Group Key: ex.ex, dnsrc.ex -> Nested Loop (cost=1.10..22.19 rows=1 width=57) (actual time=5.028..11549.670 rows=766 loops=1) -> Nested Loop (cost=1.05..22.12 rows=1 width=55) (actual time=5.025..11548.118 rows=766 loops=1) Join Filter: (mn.ap <> mnsrc.ap) Rows Removed by Join Filter: 3793 -> Nested Loop (cost=0.96..22.00 rows=1 width=63) (actual time=5.022..11535.336 rows=4559 loops=1) -> Nested Loop (cost=0.90..21.92 rows=1 width=57) (actual time=5.016..11521.601 rows=4559 loops=1) -> Nested Loop (cost=0.85..21.41 rows=4 width=57) (actual time=0.084..3902.872 rows=1047985 loops=1) -> Nested Loop (cost=0.76..20.95 rows=4 width=20) (actual time=0.077..898.511 rows=1047985 loops=1) Join Filter: ((dn.ex <> dnsrc.ex) AND (mn.mn = dn.mn)) Rows Removed by Join Filter: 5396 -> Nested Loop (cost=0.65..20.48 rows=2 width=28) (actual time=0.073..210.791 rows=40543 loops=1) -> Nested Loop (cost=0.56..20.26 rows=2 width=20) (actual time=0.065..56.851 rows=40543 loops=1) -> Nested Loop (cost=0.45..19.85 rows=1 width=16) (actual time=0.057..1.095 rows=1254 loops=1) -> Nested Loop (cost=0.33..19.62 rows=1 width=8) (actual time=0.047..0.089 rows=26 loops=1) -> Nested Loop (cost=0.22..4.65 rows=1 width=4) (actual time=0.038..0.040 rows=1 loops=1) -> Index Scan using lv_lcvc_idx on lv lvsrc (cost=0.11..2.32 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1) Index Cond: (lcvc(lc, vc) = 'gle-000'::bpchar) -> Index Scan using ex_lv_tt_key on ex exsrc (cost=0.11..2.32 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) Index Cond: ((lv = lvsrc.lv) AND (tt = 'doras'::text)) -> Index Scan using dn_ex_idx on dn dnsrc (cost=0.11..14.84 rows=13 width=8) (actual time=0.008..0.036 rows=26 loops=1) Index Cond: (ex = exsrc.ex) -> Index Only Scan using dn_mn_ex_key on dn dn3 (cost=0.11..0.18 rows=5 width=8) (actual time=0.004..0.027 rows=48 loops=26) Index Cond: (mn = dnsrc.mn) Filter: (ex <> dnsrc.ex) Rows Removed by Filter: 1 Heap Fetches: 0 -> Index Scan using dn_ex_idx on dn dn2 (cost=0.11..0.28 rows=13 width=8) (actual time=0.005..0.038 rows=32 loops=1254) Index Cond: (ex = dn3.ex) -> Index Scan using mn_pkey on mn (cost=0.09..0.10 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=40543) Index Cond: (mn = dn2.mn) -> Index Only Scan using dn_mn_ex_key on dn (cost=0.11..0.18 rows=5 width=8) (actual time=0.003..0.011 rows=26 loops=40543) Index Cond: (mn = dn2.mn) Filter: (ex <> dn2.ex) Rows Removed by Filter: 1 Heap Fetches: 764 -> Index Scan using ex_pkey on ex (cost=0.09..0.10 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=1047985) Index Cond: (ex = dn.ex) -> Index Scan using lv_pkey on lv (cost=0.06..0.12 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1047985) Index Cond: (lv = ex.lv) Filter: (lcvc(lc, vc) = 'zul-000'::bpchar) Rows Removed by Filter: 1 -> Index Scan using ap_pkey on ap (cost=0.06..0.07 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=4559) Index Cond: (ap = mn.ap) -> Index Scan using mn_pkey on mn mnsrc (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4559) Index Cond: (mn = dnsrc.mn) -> Index Scan using ap_pkey on ap apsrc (cost=0.06..0.07 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=766) Index Cond: (ap = mnsrc.ap) Planning time: 434.082 ms Execution time: 11552.325 ms