GEQO and join_collapse_limit correlation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

Hi,

We recently had an issue in production. We have queries that are procedurally generated by an Object/Relational Mapping framework. Some of these queries are huge, involving over 120 tables.

With the following parameters the planner seemed to be getting very bad plans for some of these queries (times are from a single execution, but they are in those orders of magnitude):

----
from_collapse_limit = 14
join_collapse_limit = 14
geqo_threshold = 14
geqo_effort= 5

(cost=14691360.79..81261293.30 rows=6 width=15934)

 Planning time: 3859.928 ms
 Execution time: 6883365.973 ms
----

If we raise the join_collapse_limit to a really high value the plans are much better, but (of course) planning time gets worse:

----
from_collapse_limit = 150
join_collapse_limit = 150
geqo_threshold = 14
geqo_effort= 5

(cost=379719.44..562997.32 rows=7 width=15934)

 Planning time: 7112.416 ms
 Execution time: 7.741 ms
----

After some testing in order to lower the planning time we ended bringing down the GEQO values, and we have the best results with:

----
from_collapse_limit = 150
join_collapse_limit = 150
geqo_threshold = 2
geqo_effort= 2

(cost=406427.86..589667.55 rows=6 width=15934)

 Planning time: 2721.099 ms
 Execution time: 22.728 ms
----

Issues with the join_collapse_limit have been discussed before [1], but lowering the GEQO values seems counterintuitive based on the documentation for this parameter [2]: "Setting this value [join_collapse_limit] to geqo_threshold or more may trigger use of the GEQO planner, resulting in non-optimal plans."

What we want to know is if this mechanisms are working as intended and we can follow a similar approach in the future (lower GEQO values), or this is just a fluke for a corner case.

I have been able to reproduce a similar behaviour, to a much smaller scale, with the attached scripts in Postgres 10.

[1] https://www.postgresql.org/message-id/25845.1483809942%40sss.pgh.pa.us
[2] https://www.postgresql.org/docs/current/static/runtime-config-query.html


Regards,

Juan José Santamaría

CREATE TABLE master_tbl AS
SELECT s.a AS master_id FROM generate_series(1,1000) AS s(a);

ALTER TABLE master_tbl ADD PRIMARY KEY (master_id);

DO $$
DECLARE
    i INT;
    child_name CHAR(2);

BEGIN
    FOR i IN 1..40 LOOP
 
        child_name := TO_CHAR(i, 'fm00');
        EXECUTE 'CREATE TABLE child_tbl' || child_name ||
                ' AS SELECT s.a AS child_id, s.a%10 AS master_id
                FROM generate_series(1,10000) AS s(a)';

        EXECUTE 'ALTER TABLE child_tbl' || child_name ||
                ' ADD PRIMARY KEY (child_id)';
        EXECUTE 'CREATE INDEX idx_child_tbl' || child_name ||
                ' ON child_tbl' || child_name || '(master_id)';
 
    END LOOP;
END;
$$ LANGUAGE plpgsql;

VACUUM;

set session max_parallel_workers = 0;

set session join_collapse_limit = 8;
set session geqo_threshold = 12;
set session geqo_effort = 5;

SELECT master_tbl.*
FROM master_tbl
JOIN child_tbl01 ON master_tbl.master_id = child_tbl01.master_id
JOIN child_tbl02 ON child_tbl01.child_id = child_tbl02.master_id
JOIN child_tbl03 ON child_tbl02.child_id = child_tbl03.master_id
JOIN child_tbl04 ON child_tbl03.child_id = child_tbl04.master_id
JOIN child_tbl05 ON child_tbl04.child_id = child_tbl05.master_id
JOIN child_tbl06 ON child_tbl05.child_id = child_tbl06.master_id
JOIN child_tbl07 ON child_tbl06.child_id = child_tbl07.master_id
JOIN child_tbl08 ON child_tbl07.child_id = child_tbl08.master_id
JOIN child_tbl09 ON child_tbl08.child_id = child_tbl09.master_id
JOIN child_tbl10 ON child_tbl09.child_id = child_tbl10.master_id

JOIN child_tbl11 ON child_tbl10.child_id = child_tbl11.master_id
JOIN child_tbl12 ON child_tbl11.child_id = child_tbl12.master_id
JOIN child_tbl13 ON child_tbl12.child_id = child_tbl13.master_id
JOIN child_tbl14 ON child_tbl13.child_id = child_tbl14.master_id
JOIN child_tbl15 ON child_tbl14.child_id = child_tbl15.master_id
JOIN child_tbl16 ON child_tbl15.child_id = child_tbl16.master_id
JOIN child_tbl17 ON child_tbl16.child_id = child_tbl17.master_id
JOIN child_tbl18 ON child_tbl17.child_id = child_tbl18.master_id
JOIN child_tbl19 ON child_tbl18.child_id = child_tbl19.master_id
JOIN child_tbl20 ON child_tbl19.child_id = child_tbl20.master_id

JOIN child_tbl21 ON child_tbl20.child_id = child_tbl21.master_id
JOIN child_tbl22 ON child_tbl21.child_id = child_tbl22.master_id
JOIN child_tbl23 ON child_tbl22.child_id = child_tbl23.master_id
JOIN child_tbl24 ON child_tbl23.child_id = child_tbl24.master_id
JOIN child_tbl25 ON child_tbl24.child_id = child_tbl25.master_id
JOIN child_tbl26 ON child_tbl25.child_id = child_tbl26.master_id
JOIN child_tbl27 ON child_tbl26.child_id = child_tbl27.master_id
JOIN child_tbl28 ON child_tbl27.child_id = child_tbl28.master_id
JOIN child_tbl29 ON child_tbl28.child_id = child_tbl29.master_id
JOIN child_tbl30 ON child_tbl29.child_id = child_tbl30.master_id

JOIN child_tbl31 ON child_tbl20.child_id = child_tbl31.master_id
JOIN child_tbl32 ON child_tbl20.child_id = child_tbl32.master_id
JOIN child_tbl33 ON child_tbl20.child_id = child_tbl33.master_id
JOIN child_tbl34 ON child_tbl20.child_id = child_tbl34.master_id
JOIN child_tbl35 ON child_tbl20.child_id = child_tbl35.master_id
JOIN child_tbl36 ON child_tbl20.child_id = child_tbl36.master_id
JOIN child_tbl37 ON child_tbl20.child_id = child_tbl37.master_id
JOIN child_tbl38 ON child_tbl20.child_id = child_tbl38.master_id
JOIN child_tbl39 ON child_tbl20.child_id = child_tbl39.master_id
JOIN child_tbl40 ON child_tbl30.child_id = child_tbl40.master_id

WHERE
     master_tbl.master_id=1
AND  child_tbl31.child_id=1
AND  child_tbl32.child_id=1
AND  child_tbl33.child_id=1
AND  child_tbl34.child_id=1
AND  child_tbl35.child_id=1
AND  child_tbl36.child_id=1
AND  child_tbl37.child_id=1
AND  child_tbl38.child_id=1
AND  child_tbl39.child_id=1
AND  child_tbl40.child_id=1;

set session max_parallel_workers = 0;

set session join_collapse_limit = 11;
set session geqo_threshold = 12;
set session geqo_effort = 5;

SELECT master_tbl.*
FROM master_tbl
JOIN child_tbl01 ON master_tbl.master_id = child_tbl01.master_id
JOIN child_tbl02 ON child_tbl01.child_id = child_tbl02.master_id
JOIN child_tbl03 ON child_tbl02.child_id = child_tbl03.master_id
JOIN child_tbl04 ON child_tbl03.child_id = child_tbl04.master_id
JOIN child_tbl05 ON child_tbl04.child_id = child_tbl05.master_id
JOIN child_tbl06 ON child_tbl05.child_id = child_tbl06.master_id
JOIN child_tbl07 ON child_tbl06.child_id = child_tbl07.master_id
JOIN child_tbl08 ON child_tbl07.child_id = child_tbl08.master_id
JOIN child_tbl09 ON child_tbl08.child_id = child_tbl09.master_id
JOIN child_tbl10 ON child_tbl09.child_id = child_tbl10.master_id

JOIN child_tbl11 ON child_tbl10.child_id = child_tbl11.master_id
JOIN child_tbl12 ON child_tbl11.child_id = child_tbl12.master_id
JOIN child_tbl13 ON child_tbl12.child_id = child_tbl13.master_id
JOIN child_tbl14 ON child_tbl13.child_id = child_tbl14.master_id
JOIN child_tbl15 ON child_tbl14.child_id = child_tbl15.master_id
JOIN child_tbl16 ON child_tbl15.child_id = child_tbl16.master_id
JOIN child_tbl17 ON child_tbl16.child_id = child_tbl17.master_id
JOIN child_tbl18 ON child_tbl17.child_id = child_tbl18.master_id
JOIN child_tbl19 ON child_tbl18.child_id = child_tbl19.master_id
JOIN child_tbl20 ON child_tbl19.child_id = child_tbl20.master_id

JOIN child_tbl21 ON child_tbl20.child_id = child_tbl21.master_id
JOIN child_tbl22 ON child_tbl21.child_id = child_tbl22.master_id
JOIN child_tbl23 ON child_tbl22.child_id = child_tbl23.master_id
JOIN child_tbl24 ON child_tbl23.child_id = child_tbl24.master_id
JOIN child_tbl25 ON child_tbl24.child_id = child_tbl25.master_id
JOIN child_tbl26 ON child_tbl25.child_id = child_tbl26.master_id
JOIN child_tbl27 ON child_tbl26.child_id = child_tbl27.master_id
JOIN child_tbl28 ON child_tbl27.child_id = child_tbl28.master_id
JOIN child_tbl29 ON child_tbl28.child_id = child_tbl29.master_id
JOIN child_tbl30 ON child_tbl29.child_id = child_tbl30.master_id

JOIN child_tbl31 ON child_tbl20.child_id = child_tbl31.master_id
JOIN child_tbl32 ON child_tbl20.child_id = child_tbl32.master_id
JOIN child_tbl33 ON child_tbl20.child_id = child_tbl33.master_id
JOIN child_tbl34 ON child_tbl20.child_id = child_tbl34.master_id
JOIN child_tbl35 ON child_tbl20.child_id = child_tbl35.master_id
JOIN child_tbl36 ON child_tbl20.child_id = child_tbl36.master_id
JOIN child_tbl37 ON child_tbl20.child_id = child_tbl37.master_id
JOIN child_tbl38 ON child_tbl20.child_id = child_tbl38.master_id
JOIN child_tbl39 ON child_tbl20.child_id = child_tbl39.master_id
JOIN child_tbl40 ON child_tbl30.child_id = child_tbl40.master_id

WHERE
     master_tbl.master_id=1
AND  child_tbl31.child_id=1
AND  child_tbl32.child_id=1
AND  child_tbl33.child_id=1
AND  child_tbl34.child_id=1
AND  child_tbl35.child_id=1
AND  child_tbl36.child_id=1
AND  child_tbl37.child_id=1
AND  child_tbl38.child_id=1
AND  child_tbl39.child_id=1
AND  child_tbl40.child_id=1;

set session max_parallel_workers = 0;

set session join_collapse_limit = 11;
set session geqo_threshold = 2;
set session geqo_effort = 2;

SELECT master_tbl.*
FROM master_tbl
JOIN child_tbl01 ON master_tbl.master_id = child_tbl01.master_id
JOIN child_tbl02 ON child_tbl01.child_id = child_tbl02.master_id
JOIN child_tbl03 ON child_tbl02.child_id = child_tbl03.master_id
JOIN child_tbl04 ON child_tbl03.child_id = child_tbl04.master_id
JOIN child_tbl05 ON child_tbl04.child_id = child_tbl05.master_id
JOIN child_tbl06 ON child_tbl05.child_id = child_tbl06.master_id
JOIN child_tbl07 ON child_tbl06.child_id = child_tbl07.master_id
JOIN child_tbl08 ON child_tbl07.child_id = child_tbl08.master_id
JOIN child_tbl09 ON child_tbl08.child_id = child_tbl09.master_id
JOIN child_tbl10 ON child_tbl09.child_id = child_tbl10.master_id

JOIN child_tbl11 ON child_tbl10.child_id = child_tbl11.master_id
JOIN child_tbl12 ON child_tbl11.child_id = child_tbl12.master_id
JOIN child_tbl13 ON child_tbl12.child_id = child_tbl13.master_id
JOIN child_tbl14 ON child_tbl13.child_id = child_tbl14.master_id
JOIN child_tbl15 ON child_tbl14.child_id = child_tbl15.master_id
JOIN child_tbl16 ON child_tbl15.child_id = child_tbl16.master_id
JOIN child_tbl17 ON child_tbl16.child_id = child_tbl17.master_id
JOIN child_tbl18 ON child_tbl17.child_id = child_tbl18.master_id
JOIN child_tbl19 ON child_tbl18.child_id = child_tbl19.master_id
JOIN child_tbl20 ON child_tbl19.child_id = child_tbl20.master_id

JOIN child_tbl21 ON child_tbl20.child_id = child_tbl21.master_id
JOIN child_tbl22 ON child_tbl21.child_id = child_tbl22.master_id
JOIN child_tbl23 ON child_tbl22.child_id = child_tbl23.master_id
JOIN child_tbl24 ON child_tbl23.child_id = child_tbl24.master_id
JOIN child_tbl25 ON child_tbl24.child_id = child_tbl25.master_id
JOIN child_tbl26 ON child_tbl25.child_id = child_tbl26.master_id
JOIN child_tbl27 ON child_tbl26.child_id = child_tbl27.master_id
JOIN child_tbl28 ON child_tbl27.child_id = child_tbl28.master_id
JOIN child_tbl29 ON child_tbl28.child_id = child_tbl29.master_id
JOIN child_tbl30 ON child_tbl29.child_id = child_tbl30.master_id

JOIN child_tbl31 ON child_tbl20.child_id = child_tbl31.master_id
JOIN child_tbl32 ON child_tbl20.child_id = child_tbl32.master_id
JOIN child_tbl33 ON child_tbl20.child_id = child_tbl33.master_id
JOIN child_tbl34 ON child_tbl20.child_id = child_tbl34.master_id
JOIN child_tbl35 ON child_tbl20.child_id = child_tbl35.master_id
JOIN child_tbl36 ON child_tbl20.child_id = child_tbl36.master_id
JOIN child_tbl37 ON child_tbl20.child_id = child_tbl37.master_id
JOIN child_tbl38 ON child_tbl20.child_id = child_tbl38.master_id
JOIN child_tbl39 ON child_tbl20.child_id = child_tbl39.master_id
JOIN child_tbl40 ON child_tbl30.child_id = child_tbl40.master_id

WHERE
     master_tbl.master_id=1
AND  child_tbl31.child_id=1
AND  child_tbl32.child_id=1
AND  child_tbl33.child_id=1
AND  child_tbl34.child_id=1
AND  child_tbl35.child_id=1
AND  child_tbl36.child_id=1
AND  child_tbl37.child_id=1
AND  child_tbl38.child_id=1
AND  child_tbl39.child_id=1
AND  child_tbl40.child_id=1;

DROP TABLE master_tbl;

DO $$
DECLARE
    i INT;
    child_name CHAR(2);

BEGIN
    FOR i IN 1..40 LOOP

    child_name := to_char(i, 'fm00');
    EXECUTE 'DROP TABLE child_tbl' || child_name;

    END LOOP;
END;
$$ LANGUAGE plpgsql;
[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux