Re: [GENERAL] join_collapse_limit = 14 - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject Re: [GENERAL] join_collapse_limit = 14
Date
Msg-id VisenaEmail.2.51ebdb804e961875.15979caf230@tc7-visena
Whole thread Raw
In response to [GENERAL] join_collapse_limit = 14  (Andreas Joseph Krogh <andreas@visena.com>)
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)


 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-general by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: [GENERAL] join_collapse_limit = 14
Next
From: Tom Lane
Date:
Subject: Re: [GENERAL] join_collapse_limit = 14