Re: [GENERAL] join_collapse_limit = 14 - Mailing list pgsql-general
På lørdag 07. januar 2017 kl. 17:33:52, skrev Andreas Joseph Krogh <andreas@visena.com>:
Hi all.I have a query which takes forever and the only way I've been able to make it perform reasonably well is increasing join_collapse_limit to 14 (12 still produced lots of nest-loops).This way lots of nest-loops (which I think caused the slowness) was made into hash-joins and performance was acceptable again.I wonder; In general, is there any downside of having join_collapse_limit = 14 on modern hardware (32 cores, 64GB RAM), and geqo_threshold=16 ?I'm aware of it increasing planning-time, but is this really an issue in practice?Thanks.
Here are 2 plans, the first (taking 778019.823 ms) is with join_collapse_limit=12, the second (taking 3305.756 ms) is with join_collapse_limit=14:
Any comments/insight appreciated.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=18359.87..36633.71 rows=2612 width=447) (actual time=2502.089..778004.712 rows=9024 loops=1)
Join Filter: ((cat.project_id = proj.entity_id) AND (cat.template_id = oppct.entity_id) AND (ph.phase_template_id = oppt.entity_id) AND (opr.requirement_template_id = oprt.entity_id))
Rows Removed by Join Filter: 4065898564
-> Hash Right Join (cost=13414.55..22020.84 rows=2612 width=285) (actual time=137.031..501.475 rows=9024 loops=1)
Hash Cond: ((prat.project_template_id = proj.project_template_id) AND (proj_resp_project_role.player_id = proj.entity_id))
-> Nested Loop (cost=583.23..9103.85 rows=10408 width=68) (actual time=4.944..332.425 rows=97918 loops=1)
Join Filter: (proj_resp_assoc.entity_id = proj_resp_project_role.association_id)
-> Hash Join (cost=582.81..6528.47 rows=5205 width=76) (actual time=4.938..223.817 rows=48959 loops=1)
Hash Cond: (proj_resp_person_role.player_id = proj_resp_pers.entity_id)
-> Nested Loop (cost=3.55..5868.34 rows=7686 width=32) (actual time=0.068..192.904 rows=97918 loops=1)
-> Hash Join (cost=3.13..3907.76 rows=3843 width=16) (actual time=0.056..69.013 rows=48959 loops=1)
Hash Cond: (proj_resp_assoc.type_id = proj_resp_assoctype.entity_id)
-> Seq Scan on origo_association proj_resp_assoc (cost=0.00..3491.55 rows=99909 width=16) (actual time=0.025..54.321 rows=99125 loops=1)
Filter: (tsrange @> (now())::timestamp without time zone)
Rows Removed by Filter: 19649
-> Hash (cost=3.12..3.12 rows=1 width=24) (actual time=0.023..0.023 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.75..3.12 rows=1 width=24) (actual time=0.019..0.022 rows=1 loops=1)
Hash Cond: (proj_resp_assoctype.entity_id = prat.association_type_id)
-> Seq Scan on origo_association_type proj_resp_assoctype (cost=0.00..1.26 rows=26 width=8) (actual time=0.003..0.006 rows=26 loops=1)
-> Hash (cost=1.74..1.74 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on origo_project_template_association_type prat (cost=0.00..1.74 rows=1 width=16) (actual time=0.008..0.011 rows=1 loops=1)
Filter: (is_responsible_person_role AND (project_template_id = '2818559'::bigint))
Rows Removed by Filter: 58
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_person_role (cost=0.42..0.49 rows=2 width=16) (actual time=0.002..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_assoc.entity_id)
Heap Fetches: 97918
-> Hash (cost=415.78..415.78 rows=13078 width=52) (actual time=4.858..4.858 rows=13078 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 818kB
-> Seq Scan on onp_crm_person proj_resp_pers (cost=0.00..415.78 rows=13078 width=52) (actual time=0.005..3.109 rows=13078 loops=1)
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_project_role (cost=0.42..0.47 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_person_role.association_id)
Heap Fetches: 97918
-> Hash (cost=12792.14..12792.14 rows=2612 width=241) (actual time=128.297..128.297 rows=9024 loops=1)
Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2547kB
-> Hash Join (cost=10963.51..12792.14 rows=2612 width=241) (actual time=88.307..124.933 rows=9024 loops=1)
Hash Cond: (proj.entity_id = r.player_id)
-> Nested Loop (cost=710.64..2488.75 rows=6506 width=241) (actual time=6.305..35.605 rows=81744 loops=1)
-> Nested Loop (cost=4.19..12.13 rows=8 width=139) (actual time=0.084..0.198 rows=16 loops=1)
-> Nested Loop (cost=3.91..8.65 rows=8 width=116) (actual time=0.076..0.127 rows=16 loops=1)
-> Nested Loop (cost=3.77..5.31 rows=2 width=96) (actual time=0.066..0.079 rows=5 loops=1)
-> Merge Join (cost=3.77..3.99 rows=2 width=96) (actual time=0.056..0.062 rows=5 loops=1)
Merge Cond: (oppct.entity_id = oppt.category_id)
-> Sort (cost=1.26..1.26 rows=1 width=52) (actual time=0.021..0.022 rows=1 loops=1)
Sort Key: oppct.entity_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on origo_project_phase_category_template oppct (cost=0.00..1.25 rows=1 width=52) (actual time=0.014..0.014 rows=1 loops=1)
Filter: (template_id = '2818559'::bigint)
Rows Removed by Filter: 19
-> Sort (cost=2.51..2.61 rows=41 width=52) (actual time=0.027..0.031 rows=40 loops=1)
Sort Key: oppt.category_id
Sort Method: quicksort Memory: 28kB
-> Seq Scan on origo_project_phase_template oppt (cost=0.00..1.41 rows=41 width=52) (actual time=0.005..0.011 rows=41 loops=1)
-> Materialize (cost=0.00..1.29 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=5)
-> Seq Scan on origo_project_template pt (cost=0.00..1.29 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=1)
Filter: (entity_id = '2818559'::bigint)
Rows Removed by Filter: 22
-> Index Scan using origo_project_requirement_tem_requirement_index_phase_templ_key on origo_project_requirement_template oprt (cost=0.14..1.59 rows=8 width=28) (actual time=0.005..0.008 rows=3 loops=5)
Index Cond: (phase_template_id = oppt.entity_id)
Filter: is_active
Rows Removed by Filter: 3
-> Index Scan using origo_default_task_pkey on origo_default_task odt (cost=0.28..0.42 rows=1 width=31) (actual time=0.003..0.004 rows=1 loops=16)
Index Cond: (entity_id = oprt.task_id)
-> Materialize (cost=706.45..2394.23 rows=845 width=110) (actual time=0.389..1.361 rows=5109 loops=16)
-> Nested Loop Left Join (cost=706.45..2390.00 rows=845 width=110) (actual time=6.220..16.530 rows=5109 loops=1)
-> Hash Join (cost=706.17..2048.41 rows=845 width=90) (actual time=6.217..11.075 rows=5109 loops=1)
Hash Cond: (proj.relation_id = comp.entity_id)
-> Bitmap Heap Scan on onp_crm_project proj (cost=29.20..1355.93 rows=1884 width=48) (actual time=0.741..2.551 rows=5451 loops=1)
Recheck Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
Heap Blocks: exact=1115
-> Bitmap Index Scan on origo_project_template_id_year_idx (cost=0.00..28.73 rows=1884 width=0) (actual time=0.580..0.580 rows=5451 loops=1)
Index Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
-> Hash (cost=593.91..593.91 rows=6644 width=46) (actual time=5.463..5.463 rows=6644 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 545kB
-> Index Scan using origo_relation_is_active_idx on onp_crm_relation comp (cost=0.29..593.91 rows=6644 width=46) (actual time=0.014..4.029 rows=6644 loops=1)
Index Cond: (is_active = true)
Filter: is_active
-> Index Scan using onp_crm_relation_pkey on onp_crm_relation p_comp (cost=0.29..0.39 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=5109)
Index Cond: (comp.parent_id = entity_id)
-> Hash (cost=10073.54..10073.54 rows=14347 width=8) (actual time=81.882..81.882 rows=5111 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 328kB
-> HashAggregate (cost=9930.07..10073.54 rows=14347 width=8) (actual time=80.396..81.179 rows=5111 loops=1)
Group Key: r.player_id
-> Nested Loop (cost=4242.27..9894.20 rows=14347 width=8) (actual time=52.183..78.258 rows=7250 loops=1)
Join Filter: (r.entity_id <> r2.entity_id)
Rows Removed by Join Filter: 7250
-> Hash Join (cost=4241.85..6198.90 rows=7173 width=24) (actual time=52.165..58.489 rows=7250 loops=1)
Hash Cond: (r2.association_id = a.entity_id)
-> Bitmap Heap Scan on origo_association_role r2 (cost=95.31..1948.91 rows=8528 width=16) (actual time=0.894..3.500 rows=8194 loops=1)
Recheck Cond: (player_id = '1390'::bigint)
Heap Blocks: exact=1020
-> Bitmap Index Scan on origo_association_role_player_id_idx (cost=0.00..93.18 rows=8528 width=0) (actual time=0.749..0.749 rows=8194 loops=1)
Index Cond: (player_id = '1390'::bigint)
-> Hash (cost=2897.68..2897.68 rows=99909 width=8) (actual time=51.201..51.201 rows=99125 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4897kB
-> Seq Scan on origo_association a (cost=0.00..2897.68 rows=99909 width=8) (actual time=0.014..35.640 rows=99125 loops=1)
Filter: (tsrange @> '2017-01-07 02:48:36'::timestamp without time zone)
Rows Removed by Filter: 19649
-> Index Scan using origo_assoc_role_type_player_uk on origo_association_role r (cost=0.42..0.49 rows=2 width=24) (actual time=0.002..0.002 rows=2 loops=7250)
Index Cond: (association_id = a.entity_id)
-> Materialize (cost=4945.32..14377.80 rows=4 width=202) (actual time=0.008..46.594 rows=450566 loops=9024)
-> Nested Loop (cost=4945.32..14377.78 rows=4 width=202) (actual time=43.371..2697.583 rows=450566 loops=1)
Join Filter: (opr.task_id = task.entity_id)
-> Nested Loop (cost=4944.89..14375.90 rows=4 width=132) (actual time=43.359..1649.247 rows=450566 loops=1)
-> Nested Loop (cost=4944.46..14373.40 rows=4 width=116) (actual time=43.350..753.080 rows=450566 loops=1)
-> Nested Loop (cost=4944.04..14372.79 rows=1 width=96) (actual time=43.343..413.793 rows=117061 loops=1)
-> Hash Join (cost=4943.75..14372.46 rows=1 width=63) (actual time=43.328..208.605 rows=117061 loops=1)
Hash Cond: ((phase_status.entity_id = ph.closed_activity_status_id) AND (phase_status.phase_id = ph.entity_id))
-> Seq Scan on origo_project_phase_status phase_status (cost=0.00..6794.83 rows=351183 width=24) (actual time=0.004..28.131 rows=351183 loops=1)
-> Hash (cost=3187.70..3187.70 rows=117070 width=55) (actual time=43.264..43.264 rows=117061 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 11361kB
-> Seq Scan on origo_project_phase ph (cost=0.00..3187.70 rows=117070 width=55) (actual time=0.005..21.567 rows=117070 loops=1)
-> Index Scan using origo_project_phase_category_pkey on origo_project_phase_category cat (cost=0.29..0.31 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=117061)
Index Cond: (entity_id = ph.category_id)
-> Index Scan using origo_project_requirement_phase_task_idx on origo_project_requirement opr (cost=0.42..0.55 rows=6 width=36) (actual time=0.001..0.002 rows=4 loops=117061)
Index Cond: (phase_id = phase_status.phase_id)
-> Index Scan using onp_crm_entity_pkey on onp_crm_entity taskent (cost=0.43..0.61 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=450566)
Index Cond: (entity_id = opr.task_id)
-> Index Scan using onp_crm_activity_entity_id_key on onp_crm_activity task (cost=0.43..0.46 rows=1 width=86) (actual time=0.002..0.002 rows=1 loops=450566)
Index Cond: (entity_id = taskent.entity_id)
Execution time: 778019.823 ms
(122 rows)
rsm=> explain analyze execute process_nocompassoc(2818559, 2016, true, 1390, '2017-01-07 02:48:36');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=27922.56..36528.86 rows=2612 width=447) (actual time=2999.607..3304.927 rows=9024 loops=1)
Hash Cond: ((prat.project_template_id = proj.project_template_id) AND (proj_resp_project_role.player_id = proj.entity_id))
-> Nested Loop (cost=583.23..9103.85 rows=10408 width=68) (actual time=5.060..298.042 rows=97918 loops=1)
Join Filter: (proj_resp_assoc.entity_id = proj_resp_project_role.association_id)
-> Hash Join (cost=582.81..6528.47 rows=5205 width=76) (actual time=5.052..194.600 rows=48959 loops=1)
Hash Cond: (proj_resp_person_role.player_id = proj_resp_pers.entity_id)
-> Nested Loop (cost=3.55..5868.34 rows=7686 width=32) (actual time=0.075..167.551 rows=97918 loops=1)
-> Hash Join (cost=3.13..3907.76 rows=3843 width=16) (actual time=0.061..61.983 rows=48959 loops=1)
Hash Cond: (proj_resp_assoc.type_id = proj_resp_assoctype.entity_id)
-> Seq Scan on origo_association proj_resp_assoc (cost=0.00..3491.55 rows=99909 width=16) (actual time=0.029..48.702 rows=99125 loops=1)
Filter: (tsrange @> (now())::timestamp without time zone)
Rows Removed by Filter: 19649
-> Hash (cost=3.12..3.12 rows=1 width=24) (actual time=0.023..0.023 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.75..3.12 rows=1 width=24) (actual time=0.020..0.023 rows=1 loops=1)
Hash Cond: (proj_resp_assoctype.entity_id = prat.association_type_id)
-> Seq Scan on origo_association_type proj_resp_assoctype (cost=0.00..1.26 rows=26 width=8) (actual time=0.003..0.005 rows=26 loops=1)
-> Hash (cost=1.74..1.74 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on origo_project_template_association_type prat (cost=0.00..1.74 rows=1 width=16) (actual time=0.008..0.012 rows=1 loops=1)
Filter: (is_responsible_person_role AND (project_template_id = '2818559'::bigint))
Rows Removed by Filter: 58
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_person_role (cost=0.42..0.49 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_assoc.entity_id)
Heap Fetches: 97918
-> Hash (cost=415.78..415.78 rows=13078 width=52) (actual time=4.965..4.965 rows=13078 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 818kB
-> Seq Scan on onp_crm_person proj_resp_pers (cost=0.00..415.78 rows=13078 width=52) (actual time=0.005..3.243 rows=13078 loops=1)
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_project_role (cost=0.42..0.47 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_person_role.association_id)
Heap Fetches: 97918
-> Hash (cost=27300.15..27300.15 rows=2612 width=411) (actual time=2990.690..2990.690 rows=9024 loops=1)
Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3844kB
-> Hash Join (cost=17817.07..27300.15 rows=2612 width=411) (actual time=2104.213..2984.456 rows=9024 loops=1)
Hash Cond: (proj.entity_id = r.player_id)
-> Hash Right Join (cost=7564.19..16996.75 rows=6508 width=411) (actual time=1273.386..2910.036 rows=81744 loops=1)
Hash Cond: ((cat.project_id = proj.entity_id) AND (cat.template_id = oppct.entity_id) AND (ph.phase_template_id = oppt.entity_id) AND (opr.requirement_template_id = oprt.entity_id))
-> Nested Loop (cost=4945.32..14377.78 rows=4 width=202) (actual time=43.745..2639.769 rows=450566 loops=1)
Join Filter: (opr.task_id = task.entity_id)
-> Nested Loop (cost=4944.89..14375.90 rows=4 width=132) (actual time=43.732..1590.810 rows=450566 loops=1)
-> Nested Loop (cost=4944.46..14373.40 rows=4 width=116) (actual time=43.724..725.579 rows=450566 loops=1)
-> Nested Loop (cost=4944.04..14372.79 rows=1 width=96) (actual time=43.716..394.695 rows=117061 loops=1)
-> Hash Join (cost=4943.75..14372.46 rows=1 width=63) (actual time=43.701..206.770 rows=117061 loops=1)
Hash Cond: ((phase_status.entity_id = ph.closed_activity_status_id) AND (phase_status.phase_id = ph.entity_id))
-> Seq Scan on origo_project_phase_status phase_status (cost=0.00..6794.83 rows=351183 width=24) (actual time=0.015..28.135 rows=351183 loops=1)
-> Hash (cost=3187.70..3187.70 rows=117070 width=55) (actual time=43.620..43.620 rows=117061 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 11361kB
-> Seq Scan on origo_project_phase ph (cost=0.00..3187.70 rows=117070 width=55) (actual time=0.006..22.141 rows=117070 loops=1)
-> Index Scan using origo_project_phase_category_pkey on origo_project_phase_category cat (cost=0.29..0.31 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=117061)
Index Cond: (entity_id = ph.category_id)
-> Index Scan using origo_project_requirement_phase_task_idx on origo_project_requirement opr (cost=0.42..0.55 rows=6 width=36) (actual time=0.001..0.002 rows=4 loops=117061)
Index Cond: (phase_id = phase_status.phase_id)
-> Index Scan using onp_crm_entity_pkey on onp_crm_entity taskent (cost=0.43..0.61 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=450566)
Index Cond: (entity_id = opr.task_id)
-> Index Scan using onp_crm_activity_entity_id_key on onp_crm_activity task (cost=0.43..0.46 rows=1 width=86) (actual time=0.002..0.002 rows=1 loops=450566)
Index Cond: (entity_id = taskent.entity_id)
-> Hash (cost=2488.75..2488.75 rows=6506 width=241) (actual time=84.430..84.430 rows=81744 loops=1)
Buckets: 131072 (originally 8192) Batches: 1 (originally 1) Memory Usage: 22001kB
-> Nested Loop (cost=710.64..2488.75 rows=6506 width=241) (actual time=6.239..44.023 rows=81744 loops=1)
-> Nested Loop (cost=4.19..12.13 rows=8 width=139) (actual time=0.084..0.267 rows=16 loops=1)
-> Nested Loop (cost=3.91..8.65 rows=8 width=116) (actual time=0.077..0.160 rows=16 loops=1)
-> Nested Loop (cost=3.77..5.31 rows=2 width=96) (actual time=0.065..0.086 rows=5 loops=1)
-> Merge Join (cost=3.77..3.99 rows=2 width=96) (actual time=0.055..0.065 rows=5 loops=1)
Merge Cond: (oppct.entity_id = oppt.category_id)
-> Sort (cost=1.26..1.26 rows=1 width=52) (actual time=0.020..0.020 rows=1 loops=1)
Sort Key: oppct.entity_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on origo_project_phase_category_template oppct (cost=0.00..1.25 rows=1 width=52) (actual time=0.013..0.014 rows=1 loops=1)
Filter: (template_id = '2818559'::bigint)
Rows Removed by Filter: 19
-> Sort (cost=2.51..2.61 rows=41 width=52) (actual time=0.026..0.033 rows=40 loops=1)
Sort Key: oppt.category_id
Sort Method: quicksort Memory: 28kB
-> Seq Scan on origo_project_phase_template oppt (cost=0.00..1.41 rows=41 width=52) (actual time=0.005..0.014 rows=41 loops=1)
-> Materialize (cost=0.00..1.29 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=5)
-> Seq Scan on origo_project_template pt (cost=0.00..1.29 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=1)
Filter: (entity_id = '2818559'::bigint)
Rows Removed by Filter: 22
-> Index Scan using origo_project_requirement_tem_requirement_index_phase_templ_key on origo_project_requirement_template oprt (cost=0.14..1.59 rows=8 width=28) (actual time=0.008..0.012 rows=3 loops=5)
Index Cond: (phase_template_id = oppt.entity_id)
Filter: is_active
Rows Removed by Filter: 3
-> Index Scan using origo_default_task_pkey on origo_default_task odt (cost=0.28..0.42 rows=1 width=31) (actual time=0.004..0.006 rows=1 loops=16)
Index Cond: (entity_id = oprt.task_id)
-> Materialize (cost=706.45..2394.23 rows=845 width=110) (actual time=0.385..1.632 rows=5109 loops=16)
-> Nested Loop Left Join (cost=706.45..2390.00 rows=845 width=110) (actual time=6.153..19.712 rows=5109 loops=1)
-> Hash Join (cost=706.17..2048.41 rows=845 width=90) (actual time=6.150..12.233 rows=5109 loops=1)
Hash Cond: (proj.relation_id = comp.entity_id)
-> Bitmap Heap Scan on onp_crm_project proj (cost=29.20..1355.93 rows=1884 width=48) (actual time=0.807..3.024 rows=5451 loops=1)
Recheck Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
Heap Blocks: exact=1115
-> Bitmap Index Scan on origo_project_template_id_year_idx (cost=0.00..28.73 rows=1884 width=0) (actual time=0.643..0.643 rows=5451 loops=1)
Index Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
-> Hash (cost=593.91..593.91 rows=6644 width=46) (actual time=5.331..5.331 rows=6644 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 545kB
-> Index Scan using origo_relation_is_active_idx on onp_crm_relation comp (cost=0.29..593.91 rows=6644 width=46) (actual time=0.014..3.972 rows=6644 loops=1)
Index Cond: (is_active = true)
Filter: is_active
-> Index Scan using onp_crm_relation_pkey on onp_crm_relation p_comp (cost=0.29..0.39 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=5109)
Index Cond: (comp.parent_id = entity_id)
-> Hash (cost=10073.54..10073.54 rows=14347 width=8) (actual time=63.636..63.636 rows=5111 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 328kB
-> HashAggregate (cost=9930.07..10073.54 rows=14347 width=8) (actual time=62.399..63.044 rows=5111 loops=1)
Group Key: r.player_id
-> Nested Loop (cost=4242.27..9894.20 rows=14347 width=8) (actual time=38.494..60.771 rows=7250 loops=1)
Join Filter: (r.entity_id <> r2.entity_id)
Rows Removed by Join Filter: 7250
-> Hash Join (cost=4241.85..6198.90 rows=7173 width=24) (actual time=38.478..43.983 rows=7250 loops=1)
Hash Cond: (r2.association_id = a.entity_id)
-> Bitmap Heap Scan on origo_association_role r2 (cost=95.31..1948.91 rows=8528 width=16) (actual time=0.592..2.867 rows=8194 loops=1)
Recheck Cond: (player_id = '1390'::bigint)
Heap Blocks: exact=1020
-> Bitmap Index Scan on origo_association_role_player_id_idx (cost=0.00..93.18 rows=8528 width=0) (actual time=0.481..0.481 rows=8194 loops=1)
Index Cond: (player_id = '1390'::bigint)
-> Hash (cost=2897.68..2897.68 rows=99909 width=8) (actual time=37.828..37.828 rows=99125 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4897kB
-> Seq Scan on origo_association a (cost=0.00..2897.68 rows=99909 width=8) (actual time=0.014..26.008 rows=99125 loops=1)
Filter: (tsrange @> '2017-01-07 02:48:36'::timestamp without time zone)
Rows Removed by Filter: 19649
-> Index Scan using origo_assoc_role_type_player_uk on origo_association_role r (cost=0.42..0.49 rows=2 width=24) (actual time=0.002..0.002 rows=2 loops=7250)
Index Cond: (association_id = a.entity_id)
Execution time: 3305.756 ms
(122 rows)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=18359.87..36633.71 rows=2612 width=447) (actual time=2502.089..778004.712 rows=9024 loops=1)
Join Filter: ((cat.project_id = proj.entity_id) AND (cat.template_id = oppct.entity_id) AND (ph.phase_template_id = oppt.entity_id) AND (opr.requirement_template_id = oprt.entity_id))
Rows Removed by Join Filter: 4065898564
-> Hash Right Join (cost=13414.55..22020.84 rows=2612 width=285) (actual time=137.031..501.475 rows=9024 loops=1)
Hash Cond: ((prat.project_template_id = proj.project_template_id) AND (proj_resp_project_role.player_id = proj.entity_id))
-> Nested Loop (cost=583.23..9103.85 rows=10408 width=68) (actual time=4.944..332.425 rows=97918 loops=1)
Join Filter: (proj_resp_assoc.entity_id = proj_resp_project_role.association_id)
-> Hash Join (cost=582.81..6528.47 rows=5205 width=76) (actual time=4.938..223.817 rows=48959 loops=1)
Hash Cond: (proj_resp_person_role.player_id = proj_resp_pers.entity_id)
-> Nested Loop (cost=3.55..5868.34 rows=7686 width=32) (actual time=0.068..192.904 rows=97918 loops=1)
-> Hash Join (cost=3.13..3907.76 rows=3843 width=16) (actual time=0.056..69.013 rows=48959 loops=1)
Hash Cond: (proj_resp_assoc.type_id = proj_resp_assoctype.entity_id)
-> Seq Scan on origo_association proj_resp_assoc (cost=0.00..3491.55 rows=99909 width=16) (actual time=0.025..54.321 rows=99125 loops=1)
Filter: (tsrange @> (now())::timestamp without time zone)
Rows Removed by Filter: 19649
-> Hash (cost=3.12..3.12 rows=1 width=24) (actual time=0.023..0.023 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.75..3.12 rows=1 width=24) (actual time=0.019..0.022 rows=1 loops=1)
Hash Cond: (proj_resp_assoctype.entity_id = prat.association_type_id)
-> Seq Scan on origo_association_type proj_resp_assoctype (cost=0.00..1.26 rows=26 width=8) (actual time=0.003..0.006 rows=26 loops=1)
-> Hash (cost=1.74..1.74 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on origo_project_template_association_type prat (cost=0.00..1.74 rows=1 width=16) (actual time=0.008..0.011 rows=1 loops=1)
Filter: (is_responsible_person_role AND (project_template_id = '2818559'::bigint))
Rows Removed by Filter: 58
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_person_role (cost=0.42..0.49 rows=2 width=16) (actual time=0.002..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_assoc.entity_id)
Heap Fetches: 97918
-> Hash (cost=415.78..415.78 rows=13078 width=52) (actual time=4.858..4.858 rows=13078 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 818kB
-> Seq Scan on onp_crm_person proj_resp_pers (cost=0.00..415.78 rows=13078 width=52) (actual time=0.005..3.109 rows=13078 loops=1)
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_project_role (cost=0.42..0.47 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_person_role.association_id)
Heap Fetches: 97918
-> Hash (cost=12792.14..12792.14 rows=2612 width=241) (actual time=128.297..128.297 rows=9024 loops=1)
Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2547kB
-> Hash Join (cost=10963.51..12792.14 rows=2612 width=241) (actual time=88.307..124.933 rows=9024 loops=1)
Hash Cond: (proj.entity_id = r.player_id)
-> Nested Loop (cost=710.64..2488.75 rows=6506 width=241) (actual time=6.305..35.605 rows=81744 loops=1)
-> Nested Loop (cost=4.19..12.13 rows=8 width=139) (actual time=0.084..0.198 rows=16 loops=1)
-> Nested Loop (cost=3.91..8.65 rows=8 width=116) (actual time=0.076..0.127 rows=16 loops=1)
-> Nested Loop (cost=3.77..5.31 rows=2 width=96) (actual time=0.066..0.079 rows=5 loops=1)
-> Merge Join (cost=3.77..3.99 rows=2 width=96) (actual time=0.056..0.062 rows=5 loops=1)
Merge Cond: (oppct.entity_id = oppt.category_id)
-> Sort (cost=1.26..1.26 rows=1 width=52) (actual time=0.021..0.022 rows=1 loops=1)
Sort Key: oppct.entity_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on origo_project_phase_category_template oppct (cost=0.00..1.25 rows=1 width=52) (actual time=0.014..0.014 rows=1 loops=1)
Filter: (template_id = '2818559'::bigint)
Rows Removed by Filter: 19
-> Sort (cost=2.51..2.61 rows=41 width=52) (actual time=0.027..0.031 rows=40 loops=1)
Sort Key: oppt.category_id
Sort Method: quicksort Memory: 28kB
-> Seq Scan on origo_project_phase_template oppt (cost=0.00..1.41 rows=41 width=52) (actual time=0.005..0.011 rows=41 loops=1)
-> Materialize (cost=0.00..1.29 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=5)
-> Seq Scan on origo_project_template pt (cost=0.00..1.29 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=1)
Filter: (entity_id = '2818559'::bigint)
Rows Removed by Filter: 22
-> Index Scan using origo_project_requirement_tem_requirement_index_phase_templ_key on origo_project_requirement_template oprt (cost=0.14..1.59 rows=8 width=28) (actual time=0.005..0.008 rows=3 loops=5)
Index Cond: (phase_template_id = oppt.entity_id)
Filter: is_active
Rows Removed by Filter: 3
-> Index Scan using origo_default_task_pkey on origo_default_task odt (cost=0.28..0.42 rows=1 width=31) (actual time=0.003..0.004 rows=1 loops=16)
Index Cond: (entity_id = oprt.task_id)
-> Materialize (cost=706.45..2394.23 rows=845 width=110) (actual time=0.389..1.361 rows=5109 loops=16)
-> Nested Loop Left Join (cost=706.45..2390.00 rows=845 width=110) (actual time=6.220..16.530 rows=5109 loops=1)
-> Hash Join (cost=706.17..2048.41 rows=845 width=90) (actual time=6.217..11.075 rows=5109 loops=1)
Hash Cond: (proj.relation_id = comp.entity_id)
-> Bitmap Heap Scan on onp_crm_project proj (cost=29.20..1355.93 rows=1884 width=48) (actual time=0.741..2.551 rows=5451 loops=1)
Recheck Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
Heap Blocks: exact=1115
-> Bitmap Index Scan on origo_project_template_id_year_idx (cost=0.00..28.73 rows=1884 width=0) (actual time=0.580..0.580 rows=5451 loops=1)
Index Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
-> Hash (cost=593.91..593.91 rows=6644 width=46) (actual time=5.463..5.463 rows=6644 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 545kB
-> Index Scan using origo_relation_is_active_idx on onp_crm_relation comp (cost=0.29..593.91 rows=6644 width=46) (actual time=0.014..4.029 rows=6644 loops=1)
Index Cond: (is_active = true)
Filter: is_active
-> Index Scan using onp_crm_relation_pkey on onp_crm_relation p_comp (cost=0.29..0.39 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=5109)
Index Cond: (comp.parent_id = entity_id)
-> Hash (cost=10073.54..10073.54 rows=14347 width=8) (actual time=81.882..81.882 rows=5111 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 328kB
-> HashAggregate (cost=9930.07..10073.54 rows=14347 width=8) (actual time=80.396..81.179 rows=5111 loops=1)
Group Key: r.player_id
-> Nested Loop (cost=4242.27..9894.20 rows=14347 width=8) (actual time=52.183..78.258 rows=7250 loops=1)
Join Filter: (r.entity_id <> r2.entity_id)
Rows Removed by Join Filter: 7250
-> Hash Join (cost=4241.85..6198.90 rows=7173 width=24) (actual time=52.165..58.489 rows=7250 loops=1)
Hash Cond: (r2.association_id = a.entity_id)
-> Bitmap Heap Scan on origo_association_role r2 (cost=95.31..1948.91 rows=8528 width=16) (actual time=0.894..3.500 rows=8194 loops=1)
Recheck Cond: (player_id = '1390'::bigint)
Heap Blocks: exact=1020
-> Bitmap Index Scan on origo_association_role_player_id_idx (cost=0.00..93.18 rows=8528 width=0) (actual time=0.749..0.749 rows=8194 loops=1)
Index Cond: (player_id = '1390'::bigint)
-> Hash (cost=2897.68..2897.68 rows=99909 width=8) (actual time=51.201..51.201 rows=99125 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4897kB
-> Seq Scan on origo_association a (cost=0.00..2897.68 rows=99909 width=8) (actual time=0.014..35.640 rows=99125 loops=1)
Filter: (tsrange @> '2017-01-07 02:48:36'::timestamp without time zone)
Rows Removed by Filter: 19649
-> Index Scan using origo_assoc_role_type_player_uk on origo_association_role r (cost=0.42..0.49 rows=2 width=24) (actual time=0.002..0.002 rows=2 loops=7250)
Index Cond: (association_id = a.entity_id)
-> Materialize (cost=4945.32..14377.80 rows=4 width=202) (actual time=0.008..46.594 rows=450566 loops=9024)
-> Nested Loop (cost=4945.32..14377.78 rows=4 width=202) (actual time=43.371..2697.583 rows=450566 loops=1)
Join Filter: (opr.task_id = task.entity_id)
-> Nested Loop (cost=4944.89..14375.90 rows=4 width=132) (actual time=43.359..1649.247 rows=450566 loops=1)
-> Nested Loop (cost=4944.46..14373.40 rows=4 width=116) (actual time=43.350..753.080 rows=450566 loops=1)
-> Nested Loop (cost=4944.04..14372.79 rows=1 width=96) (actual time=43.343..413.793 rows=117061 loops=1)
-> Hash Join (cost=4943.75..14372.46 rows=1 width=63) (actual time=43.328..208.605 rows=117061 loops=1)
Hash Cond: ((phase_status.entity_id = ph.closed_activity_status_id) AND (phase_status.phase_id = ph.entity_id))
-> Seq Scan on origo_project_phase_status phase_status (cost=0.00..6794.83 rows=351183 width=24) (actual time=0.004..28.131 rows=351183 loops=1)
-> Hash (cost=3187.70..3187.70 rows=117070 width=55) (actual time=43.264..43.264 rows=117061 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 11361kB
-> Seq Scan on origo_project_phase ph (cost=0.00..3187.70 rows=117070 width=55) (actual time=0.005..21.567 rows=117070 loops=1)
-> Index Scan using origo_project_phase_category_pkey on origo_project_phase_category cat (cost=0.29..0.31 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=117061)
Index Cond: (entity_id = ph.category_id)
-> Index Scan using origo_project_requirement_phase_task_idx on origo_project_requirement opr (cost=0.42..0.55 rows=6 width=36) (actual time=0.001..0.002 rows=4 loops=117061)
Index Cond: (phase_id = phase_status.phase_id)
-> Index Scan using onp_crm_entity_pkey on onp_crm_entity taskent (cost=0.43..0.61 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=450566)
Index Cond: (entity_id = opr.task_id)
-> Index Scan using onp_crm_activity_entity_id_key on onp_crm_activity task (cost=0.43..0.46 rows=1 width=86) (actual time=0.002..0.002 rows=1 loops=450566)
Index Cond: (entity_id = taskent.entity_id)
Execution time: 778019.823 ms
(122 rows)
rsm=> explain analyze execute process_nocompassoc(2818559, 2016, true, 1390, '2017-01-07 02:48:36');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=27922.56..36528.86 rows=2612 width=447) (actual time=2999.607..3304.927 rows=9024 loops=1)
Hash Cond: ((prat.project_template_id = proj.project_template_id) AND (proj_resp_project_role.player_id = proj.entity_id))
-> Nested Loop (cost=583.23..9103.85 rows=10408 width=68) (actual time=5.060..298.042 rows=97918 loops=1)
Join Filter: (proj_resp_assoc.entity_id = proj_resp_project_role.association_id)
-> Hash Join (cost=582.81..6528.47 rows=5205 width=76) (actual time=5.052..194.600 rows=48959 loops=1)
Hash Cond: (proj_resp_person_role.player_id = proj_resp_pers.entity_id)
-> Nested Loop (cost=3.55..5868.34 rows=7686 width=32) (actual time=0.075..167.551 rows=97918 loops=1)
-> Hash Join (cost=3.13..3907.76 rows=3843 width=16) (actual time=0.061..61.983 rows=48959 loops=1)
Hash Cond: (proj_resp_assoc.type_id = proj_resp_assoctype.entity_id)
-> Seq Scan on origo_association proj_resp_assoc (cost=0.00..3491.55 rows=99909 width=16) (actual time=0.029..48.702 rows=99125 loops=1)
Filter: (tsrange @> (now())::timestamp without time zone)
Rows Removed by Filter: 19649
-> Hash (cost=3.12..3.12 rows=1 width=24) (actual time=0.023..0.023 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.75..3.12 rows=1 width=24) (actual time=0.020..0.023 rows=1 loops=1)
Hash Cond: (proj_resp_assoctype.entity_id = prat.association_type_id)
-> Seq Scan on origo_association_type proj_resp_assoctype (cost=0.00..1.26 rows=26 width=8) (actual time=0.003..0.005 rows=26 loops=1)
-> Hash (cost=1.74..1.74 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on origo_project_template_association_type prat (cost=0.00..1.74 rows=1 width=16) (actual time=0.008..0.012 rows=1 loops=1)
Filter: (is_responsible_person_role AND (project_template_id = '2818559'::bigint))
Rows Removed by Filter: 58
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_person_role (cost=0.42..0.49 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_assoc.entity_id)
Heap Fetches: 97918
-> Hash (cost=415.78..415.78 rows=13078 width=52) (actual time=4.965..4.965 rows=13078 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 818kB
-> Seq Scan on onp_crm_person proj_resp_pers (cost=0.00..415.78 rows=13078 width=52) (actual time=0.005..3.243 rows=13078 loops=1)
-> Index Only Scan using origo_assoc_role_type_player_uk on origo_association_role proj_resp_project_role (cost=0.42..0.47 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=48959)
Index Cond: (association_id = proj_resp_person_role.association_id)
Heap Fetches: 97918
-> Hash (cost=27300.15..27300.15 rows=2612 width=411) (actual time=2990.690..2990.690 rows=9024 loops=1)
Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 3844kB
-> Hash Join (cost=17817.07..27300.15 rows=2612 width=411) (actual time=2104.213..2984.456 rows=9024 loops=1)
Hash Cond: (proj.entity_id = r.player_id)
-> Hash Right Join (cost=7564.19..16996.75 rows=6508 width=411) (actual time=1273.386..2910.036 rows=81744 loops=1)
Hash Cond: ((cat.project_id = proj.entity_id) AND (cat.template_id = oppct.entity_id) AND (ph.phase_template_id = oppt.entity_id) AND (opr.requirement_template_id = oprt.entity_id))
-> Nested Loop (cost=4945.32..14377.78 rows=4 width=202) (actual time=43.745..2639.769 rows=450566 loops=1)
Join Filter: (opr.task_id = task.entity_id)
-> Nested Loop (cost=4944.89..14375.90 rows=4 width=132) (actual time=43.732..1590.810 rows=450566 loops=1)
-> Nested Loop (cost=4944.46..14373.40 rows=4 width=116) (actual time=43.724..725.579 rows=450566 loops=1)
-> Nested Loop (cost=4944.04..14372.79 rows=1 width=96) (actual time=43.716..394.695 rows=117061 loops=1)
-> Hash Join (cost=4943.75..14372.46 rows=1 width=63) (actual time=43.701..206.770 rows=117061 loops=1)
Hash Cond: ((phase_status.entity_id = ph.closed_activity_status_id) AND (phase_status.phase_id = ph.entity_id))
-> Seq Scan on origo_project_phase_status phase_status (cost=0.00..6794.83 rows=351183 width=24) (actual time=0.015..28.135 rows=351183 loops=1)
-> Hash (cost=3187.70..3187.70 rows=117070 width=55) (actual time=43.620..43.620 rows=117061 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 11361kB
-> Seq Scan on origo_project_phase ph (cost=0.00..3187.70 rows=117070 width=55) (actual time=0.006..22.141 rows=117070 loops=1)
-> Index Scan using origo_project_phase_category_pkey on origo_project_phase_category cat (cost=0.29..0.31 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=117061)
Index Cond: (entity_id = ph.category_id)
-> Index Scan using origo_project_requirement_phase_task_idx on origo_project_requirement opr (cost=0.42..0.55 rows=6 width=36) (actual time=0.001..0.002 rows=4 loops=117061)
Index Cond: (phase_id = phase_status.phase_id)
-> Index Scan using onp_crm_entity_pkey on onp_crm_entity taskent (cost=0.43..0.61 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=450566)
Index Cond: (entity_id = opr.task_id)
-> Index Scan using onp_crm_activity_entity_id_key on onp_crm_activity task (cost=0.43..0.46 rows=1 width=86) (actual time=0.002..0.002 rows=1 loops=450566)
Index Cond: (entity_id = taskent.entity_id)
-> Hash (cost=2488.75..2488.75 rows=6506 width=241) (actual time=84.430..84.430 rows=81744 loops=1)
Buckets: 131072 (originally 8192) Batches: 1 (originally 1) Memory Usage: 22001kB
-> Nested Loop (cost=710.64..2488.75 rows=6506 width=241) (actual time=6.239..44.023 rows=81744 loops=1)
-> Nested Loop (cost=4.19..12.13 rows=8 width=139) (actual time=0.084..0.267 rows=16 loops=1)
-> Nested Loop (cost=3.91..8.65 rows=8 width=116) (actual time=0.077..0.160 rows=16 loops=1)
-> Nested Loop (cost=3.77..5.31 rows=2 width=96) (actual time=0.065..0.086 rows=5 loops=1)
-> Merge Join (cost=3.77..3.99 rows=2 width=96) (actual time=0.055..0.065 rows=5 loops=1)
Merge Cond: (oppct.entity_id = oppt.category_id)
-> Sort (cost=1.26..1.26 rows=1 width=52) (actual time=0.020..0.020 rows=1 loops=1)
Sort Key: oppct.entity_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on origo_project_phase_category_template oppct (cost=0.00..1.25 rows=1 width=52) (actual time=0.013..0.014 rows=1 loops=1)
Filter: (template_id = '2818559'::bigint)
Rows Removed by Filter: 19
-> Sort (cost=2.51..2.61 rows=41 width=52) (actual time=0.026..0.033 rows=40 loops=1)
Sort Key: oppt.category_id
Sort Method: quicksort Memory: 28kB
-> Seq Scan on origo_project_phase_template oppt (cost=0.00..1.41 rows=41 width=52) (actual time=0.005..0.014 rows=41 loops=1)
-> Materialize (cost=0.00..1.29 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=5)
-> Seq Scan on origo_project_template pt (cost=0.00..1.29 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=1)
Filter: (entity_id = '2818559'::bigint)
Rows Removed by Filter: 22
-> Index Scan using origo_project_requirement_tem_requirement_index_phase_templ_key on origo_project_requirement_template oprt (cost=0.14..1.59 rows=8 width=28) (actual time=0.008..0.012 rows=3 loops=5)
Index Cond: (phase_template_id = oppt.entity_id)
Filter: is_active
Rows Removed by Filter: 3
-> Index Scan using origo_default_task_pkey on origo_default_task odt (cost=0.28..0.42 rows=1 width=31) (actual time=0.004..0.006 rows=1 loops=16)
Index Cond: (entity_id = oprt.task_id)
-> Materialize (cost=706.45..2394.23 rows=845 width=110) (actual time=0.385..1.632 rows=5109 loops=16)
-> Nested Loop Left Join (cost=706.45..2390.00 rows=845 width=110) (actual time=6.153..19.712 rows=5109 loops=1)
-> Hash Join (cost=706.17..2048.41 rows=845 width=90) (actual time=6.150..12.233 rows=5109 loops=1)
Hash Cond: (proj.relation_id = comp.entity_id)
-> Bitmap Heap Scan on onp_crm_project proj (cost=29.20..1355.93 rows=1884 width=48) (actual time=0.807..3.024 rows=5451 loops=1)
Recheck Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
Heap Blocks: exact=1115
-> Bitmap Index Scan on origo_project_template_id_year_idx (cost=0.00..28.73 rows=1884 width=0) (actual time=0.643..0.643 rows=5451 loops=1)
Index Cond: ((project_template_id = '2818559'::bigint) AND (year = 2016))
-> Hash (cost=593.91..593.91 rows=6644 width=46) (actual time=5.331..5.331 rows=6644 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 545kB
-> Index Scan using origo_relation_is_active_idx on onp_crm_relation comp (cost=0.29..593.91 rows=6644 width=46) (actual time=0.014..3.972 rows=6644 loops=1)
Index Cond: (is_active = true)
Filter: is_active
-> Index Scan using onp_crm_relation_pkey on onp_crm_relation p_comp (cost=0.29..0.39 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=5109)
Index Cond: (comp.parent_id = entity_id)
-> Hash (cost=10073.54..10073.54 rows=14347 width=8) (actual time=63.636..63.636 rows=5111 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 328kB
-> HashAggregate (cost=9930.07..10073.54 rows=14347 width=8) (actual time=62.399..63.044 rows=5111 loops=1)
Group Key: r.player_id
-> Nested Loop (cost=4242.27..9894.20 rows=14347 width=8) (actual time=38.494..60.771 rows=7250 loops=1)
Join Filter: (r.entity_id <> r2.entity_id)
Rows Removed by Join Filter: 7250
-> Hash Join (cost=4241.85..6198.90 rows=7173 width=24) (actual time=38.478..43.983 rows=7250 loops=1)
Hash Cond: (r2.association_id = a.entity_id)
-> Bitmap Heap Scan on origo_association_role r2 (cost=95.31..1948.91 rows=8528 width=16) (actual time=0.592..2.867 rows=8194 loops=1)
Recheck Cond: (player_id = '1390'::bigint)
Heap Blocks: exact=1020
-> Bitmap Index Scan on origo_association_role_player_id_idx (cost=0.00..93.18 rows=8528 width=0) (actual time=0.481..0.481 rows=8194 loops=1)
Index Cond: (player_id = '1390'::bigint)
-> Hash (cost=2897.68..2897.68 rows=99909 width=8) (actual time=37.828..37.828 rows=99125 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4897kB
-> Seq Scan on origo_association a (cost=0.00..2897.68 rows=99909 width=8) (actual time=0.014..26.008 rows=99125 loops=1)
Filter: (tsrange @> '2017-01-07 02:48:36'::timestamp without time zone)
Rows Removed by Filter: 19649
-> Index Scan using origo_assoc_role_type_player_uk on origo_association_role r (cost=0.42..0.49 rows=2 width=24) (actual time=0.002..0.002 rows=2 loops=7250)
Index Cond: (association_id = a.entity_id)
Execution time: 3305.756 ms
(122 rows)
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
pgsql-general by date: