Thread: [GENERAL] join_collapse_limit = 14

[GENERAL] join_collapse_limit = 14

From

Andreas Joseph Krogh

Date:

07 January 2017, 22:33:52

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.

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Attachment

msg-1553726-3.png

Re: [GENERAL] join_collapse_limit = 14

From

Andreas Joseph Krogh

Date:

07 January 2017, 22:42:36

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

andreas@visena.com

www.visena.com

Attachment

msg-1553729-3.png

Re: [GENERAL] join_collapse_limit = 14

From

Tom Lane

Date:

07 January 2017, 22:48:49

Andreas Joseph Krogh <andreas@visena.com> writes:
> 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?

It can be.  The number of possible join orders is exponential in the join
size (at least 2^n, maybe worse, don't recall at the moment).  There are
heuristics in the planner that usually let it avoid investigating large
parts of that space; but the only thing that will certainly limit the
problem is preventing large join subproblems from being considered in the
first place --- which is what join_collapse_limit/from_collapse_limit are
for.

Depending on what your queries are like, you might not ever hit any of the
bad cases, so it might be fine.  But I'd be nervous about throwing a wide
mix of complex queries at a planner set with high thresholds.

If you've got just one problem query, it might be worth your time to take
note of the optimal join order (as seen in EXPLAIN when a good plan is
chosen) and rearrange the query so that the syntactic join order matches
that.  Then you should get the same plan even when join_collapse_limit is
small.

            regards, tom lane

Re: [GENERAL] join_collapse_limit = 14

From

Andreas Joseph Krogh

Date:

07 January 2017, 23:12:35

På lørdag 07. januar 2017 kl. 17:48:49, skrev Tom Lane <tgl@sss.pgh.pa.us>:

Andreas Joseph Krogh <andreas@visena.com> writes:
> 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?

It can be. The number of possible join orders is exponential in the join
size (at least 2^n, maybe worse, don't recall at the moment). There are
heuristics in the planner that usually let it avoid investigating large
parts of that space; but the only thing that will certainly limit the
problem is preventing large join subproblems from being considered in the
first place --- which is what join_collapse_limit/from_collapse_limit are
for.

Depending on what your queries are like, you might not ever hit any of the
bad cases, so it might be fine. But I'd be nervous about throwing a wide
mix of complex queries at a planner set with high thresholds.

If you've got just one problem query, it might be worth your time to take
note of the optimal join order (as seen in EXPLAIN when a good plan is
chosen) and rearrange the query so that the syntactic join order matches
that. Then you should get the same plan even when join_collapse_limit is
small.

Thanks.

Will geqo kick if if join_collapse_limit =4 and there are many more joins or will the planner just stop trying to rearrange JOINs after this limit?

In other words; Is join_collapse_limit=1 the only way or might it work for other "small" values as well?

In any case; I assume you're suggesting doing something like "SET LOCAL join_collapse_limit=<some-value>" in the same transaction so this doesn't have to be a system-wide setting?

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Attachment

msg-1553732-3.png

Re: [GENERAL] join_collapse_limit = 14

From

Tom Lane

Date:

07 January 2017, 23:25:42

Andreas Joseph Krogh <andreas@visena.com> writes:
> På lørdag 07. januar 2017 kl. 17:48:49, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>  If you've got just one problem query, it might be worth your time to take
>  note of the optimal join order (as seen in EXPLAIN when a good plan is
>  chosen) and rearrange the query so that the syntactic join order matches
>  that.  Then you should get the same plan even when join_collapse_limit is
>  small.

> Will geqo kick if if join_collapse_limit =4 and there are many more joins or
> will the planner just stop trying to rearrange JOINs after this limit?

geqo_threshold applies to the size of the join subproblems that exist
after flattening is done, or not done, according to join_collapse_limit
and from_collapse_limit: if a subproblem is >= geqo_threshold relations
then it's planned via geqo, else the normal search.  So if geqo_threshold
is more than join_collapse_limit then it's impossible for a JOIN nest to
result in use of the geqo code.

> In any case; I assume you're suggesting doing something like "SET LOCAL
> join_collapse_limit=<some-value>" in the same transaction so this doesn't have
> to be a system-wide setting?

Right, there's no reason not to set it locally in your session for
experimental purposes.  I'd just be nervous about turning it up globally
for production without having checked all your queries ...

            regards, tom lane

Re: [GENERAL] join_collapse_limit = 14

From

Andreas Joseph Krogh

Date:

07 January 2017, 23:44:09

På lørdag 07. januar 2017 kl. 18:25:42, skrev Tom Lane <tgl@sss.pgh.pa.us>:

Andreas Joseph Krogh <andreas@visena.com> writes:
> På lørdag 07. januar 2017 kl. 17:48:49, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
> If you've got just one problem query, it might be worth your time to take
> note of the optimal join order (as seen in EXPLAIN when a good plan is
> chosen) and rearrange the query so that the syntactic join order matches
> that. Then you should get the same plan even when join_collapse_limit is
> small.

> Will geqo kick if if join_collapse_limit =4 and there are many more joins or
> will the planner just stop trying to rearrange JOINs after this limit?

geqo_threshold applies to the size of the join subproblems that exist
after flattening is done, or not done, according to join_collapse_limit
and from_collapse_limit: if a subproblem is >= geqo_threshold relations
then it's planned via geqo, else the normal search. So if geqo_threshold
is more than join_collapse_limit then it's impossible for a JOIN nest to
result in use of the geqo code.

> In any case; I assume you're suggesting doing something like "SET LOCAL
> join_collapse_limit=<some-value>" in the same transaction so this doesn't have
> to be a system-wide setting?

Right, there's no reason not to set it locally in your session for
experimental purposes. I'd just be nervous about turning it up globally
for production without having checked all your queries ...

regards, tom lane

OK, thanks.

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Attachment

msg-1553735-3.png