Hi Mike, You said previously that the problem gone and back again. In which version the issue gone? How looks like your URL?
Regards, Fred 2015-03-09 14:33 GMT-03:00 Mike Goodwin <[email protected]>: > Hi, > > I replied off list with the data. Thanks Fred. > > I did a little bit of investigation myself. I could see in the optimizer > that for whatever reason it was not giving the plan which used the index a > better score. Actually the costs were all very similar (+/- 1%). I added in > an extra heuristic which was just to favour slightly the plan which keeps > the original order and this indeed fixes/works around my issue. > > The patch below to show what I mean, not because I think it would be a > good thing to integrate this change - probably it would be better to fix > the cost analysis! > > regards, > > Mike > > Index: src/main/org/h2/command/dml/Optimizer.java > =================================================================== > --- src/main/org/h2/command/dml/Optimizer.java (revision 6063) > +++ src/main/org/h2/command/dml/Optimizer.java (working copy) > @@ -102,7 +102,7 @@ > TableFilter[] list = new TableFilter[filters.length]; > Permutations<TableFilter> p = Permutations.create(filters, list); > for (int x = 0; !canStop(x) && p.next(); x++) { > - testPlan(list); > + testPlan(list, x==0); > } > } > > @@ -171,9 +171,11 @@ > } > } > > - private boolean testPlan(TableFilter[] list) { > + private boolean testPlan(TableFilter[] list) { return testPlan(list, > false); } > + private boolean testPlan(TableFilter[] list, boolean favour) { > Plan p = new Plan(list, list.length, condition); > double costNow = p.calculateCost(session); > + if(favour) costNow = costNow*0.9; > if (cost < 0 || costNow < cost) { > cost = costNow; > bestPlan = p; > > > > > > On Mon, Mar 9, 2015 at 2:36 PM, Fred&Dani&Pandora&Aquiles < > [email protected]> wrote: > >> Hi Mike, >> >> I would like to try to help you. So, can you provide schema/test data to >> reproduce the cited situation? >> >> Regards, >> >> Fred >> >> 2015-03-09 10:24 GMT-03:00 Mike Goodwin <[email protected]>: >> >>> Hi, >>> >>> I have a query performance problem that has come, gone away and come >>> back again. >>> >>> The issue seems to be when using what are sometimes referred to as sub >>> types. A sub type is a table that share a primary key with the super type >>> table. This explains the scheme a bit better. >>> >>> >>> http://blogs.msdn.com/b/dfurman/archive/2009/08/31/disjoint-subtyping-in-sql.aspx >>> >>> I have a situation where I have a query that essentially involves a sub >>> type (B/enquiry line) and the super type (A/part) table. A 3rd table >>> (C/enquiry) is referenced by the sub type directly and there is a foreign >>> key/index between them. I want to aggregate over B/A and group by C.The >>> problem seems to be that the optimiser has chosen to use A as the main >>> table in the group by sub query and then because of this choice it does not >>> have access to the query when joining to C and so it becomes very slow >>> because of the nested table scan. >>> >>> I appreciate that probably reads like nonsense and may not be accurate. >>> I can provide the SCRIPT TO schema/test data, if anyone would like to take >>> a closer look. The query is and the EXPLAIN is at the bottom of this email. >>> >>> >>> I should say I really appreciate h2 and the performance it gives us >>> (it's a great little database). >>> >>> <minor SQL rant> Sometimes I find SQL to be a bit frustrating, because >>> when doing what I view as hierarchical operations with what should be a >>> clear execution plan, when they are expressed in SQL this clarity on how >>> they should be executed is lost because of its generality. 99% of the SQL I >>> write (or generate) usually amounts to hierarchical operations and this >>> generality increases the risk of performance issues - I think this maybe >>> true for many database users. Take In this example, some enquiries, some >>> lines, some parts which correspond to lines. There may be 1000s of >>> enquiries and 10000s of lines/parts. However each enquiry would only ever >>> have a few lines (probably <10 on average). The lines themselves are never >>> shared between enquires. An operation aggregating over the lines (e.g. >>> counting them, or summing their values) should not ever need to bigger than >>> O(lines) and I should be able to express it in away which this is clear and >>> without risk. It seems to be me (having used other database, e.g. oracle) >>> that the promise of optimisers removing this risk have never fully >>> delivered. I would dearly like to have a way to express hiercarchical >>> queries and bypass sql!! (I'd settle for a way to optionally prevent the >>> query optimiser from reordering joins). >>> >>> regards, >>> >>> Mike >>> >>> (on a related note, I think that it would be really good if the SCRIPT >>> function could take a query and then output just the necessary statements >>> to recreate enough of the database to run the query, it would make >>> producing isolated test cases many many times easier ...) >>> >>> ---------------- >>> >>> >>> >>> EXPLAIN SELECT >>> IFNULL(t_lines_filter0.vc0,0) AS value >>> FROM "enquiry" AS t >>> LEFT JOIN ( >>> SELECT >>> IFNULL(coalesce(sum(CASE WHEN >>> t_lines_filter0."include" >>> THEN >>> CASE WHEN t_lines_filter0_part_sub_job_costing.revision >>> IS NULL >>> THEN NULL >>> ELSE >>> IFNULL(t_lines_filter0_part_sub_job_costing_internal.vc0,0) >>> END >>> ELSE >>> 0 >>> END),0),0) AS vc0, >>> t_lines_filter0."enquiry" AS GROUPBY >>> FROM "enquiry_line" AS t_lines_filter0 >>> INNER JOIN "part_sub" AS t_lines_filter0_part_sub ON >>> t_lines_filter0."Id" IS t_lines_filter0_part_sub."Id" >>> INNER JOIN "job" AS t_lines_filter0_part_sub_job ON >>> t_lines_filter0_part_sub."Id" IS t_lines_filter0_part_sub_job."Id" >>> LEFT JOIN "costing" AS t_lines_filter0_part_sub_job_costing ON >>> t_lines_filter0_part_sub_job."costing" IS >>> t_lines_filter0_part_sub_job_costing."Id" >>> LEFT JOIN ( >>> SELECT >>> >>> IFNULL(coalesce(sum(t_lines_filter0_part_sub_job_costing_internal."total_time"),0),0) >>> AS vc0, >>> t_lines_filter0_part_sub_job_costing_internal."costing" >>> AS GROUPBY >>> FROM "costing_internal" AS >>> t_lines_filter0_part_sub_job_costing_internal >>> WHERE (true) >>> GROUP BY groupby >>> ) AS t_lines_filter0_part_sub_job_costing_internal ON >>> t_lines_filter0_part_sub_job_costing."Id" IS >>> t_lines_filter0_part_sub_job_costing_internal.groupby >>> WHERE (t_lines_filter0."include") >>> GROUP BY groupby >>> ) AS t_lines_filter0 ON t."Id" IS t_lines_filter0.groupby ; >>> >>> >>> ---------- >>> >>> >>> SELECT >>> IFNULL(T_LINES_FILTER0.VC0, 0) AS VALUE >>> FROM "enquiry" T >>> /* "enquiry".tableScan */ >>> LEFT OUTER JOIN ( >>> SELECT >>> IFNULL(COALESCE(SUM(CASE WHEN T_LINES_FILTER0."include" THEN >>> CASE WHEN (T_LINES_FILTER0_PART_SUB_JOB_COSTING.REVISION IS NULL) THEN NULL >>> ELSE IFNULL(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.VC0, 0) END ELSE >>> 0 END), 0), 0) AS VC0, >>> T_LINES_FILTER0."enquiry" AS GROUPBY >>> FROM "part_sub" T_LINES_FILTER0_PART_SUB >>> /* "part_sub".tableScan */ >>> INNER JOIN "job" T_LINES_FILTER0_PART_SUB_JOB >>> /* PRIMARY_KEY_1A: "Id" IS T_LINES_FILTER0_PART_SUB."Id" */ >>> ON 1=1 >>> /* WHERE T_LINES_FILTER0_PART_SUB."Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB."Id" >>> */ >>> LEFT OUTER JOIN "costing" T_LINES_FILTER0_PART_SUB_JOB_COSTING >>> /* PRIMARY_KEY_C03: "Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB."costing" */ >>> ON T_LINES_FILTER0_PART_SUB_JOB."costing" IS >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" >>> LEFT OUTER JOIN ( >>> SELECT >>> >>> IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), >>> 0), 0) AS VC0, >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS >>> GROUPBY >>> FROM "costing_internal" >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL >>> /* FK_COSTING_INTERNAL_COSTING_INDEX_F */ >>> WHERE TRUE >>> GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" >>> /* group sorted */ >>> ) T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL >>> /* SELECT >>> >>> IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), >>> 0), 0) AS VC0, >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS >>> GROUPBY >>> FROM "costing_internal" >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL >>> /++ FK_COSTING_INTERNAL_COSTING_INDEX_F: "costing" IS ?1 ++/ >>> WHERE T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" IS >>> ?1 >>> GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" >>> /++ group sorted ++/: GROUPBY IS >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" >>> */ >>> ON T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.GROUPBY >>> INNER JOIN "enquiry_line" T_LINES_FILTER0 >>> /* PRIMARY_KEY_B95: "Id" IS T_LINES_FILTER0_PART_SUB."Id" */ >>> ON 1=1 >>> WHERE T_LINES_FILTER0."include" >>> AND ((T_LINES_FILTER0_PART_SUB."Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB."Id") >>> AND (T_LINES_FILTER0."Id" IS T_LINES_FILTER0_PART_SUB."Id")) >>> GROUP BY T_LINES_FILTER0."enquiry" >>> ) T_LINES_FILTER0 >>> /* SELECT >>> IFNULL(COALESCE(SUM(CASE WHEN T_LINES_FILTER0."include" THEN >>> CASE WHEN (T_LINES_FILTER0_PART_SUB_JOB_COSTING.REVISION IS NULL) THEN NULL >>> ELSE IFNULL(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.VC0, 0) END ELSE >>> 0 END), 0), 0) AS VC0, >>> T_LINES_FILTER0."enquiry" AS GROUPBY >>> FROM "part_sub" T_LINES_FILTER0_PART_SUB >>> /++ "part_sub".tableScan ++/ >>> INNER JOIN "job" T_LINES_FILTER0_PART_SUB_JOB >>> /++ PRIMARY_KEY_1A: "Id" IS T_LINES_FILTER0_PART_SUB."Id" ++/ >>> ON 1=1 >>> /++ WHERE T_LINES_FILTER0_PART_SUB."Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB."Id" >>> ++/ >>> LEFT OUTER JOIN "costing" T_LINES_FILTER0_PART_SUB_JOB_COSTING >>> /++ PRIMARY_KEY_C03: "Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB."costing" ++/ >>> ON T_LINES_FILTER0_PART_SUB_JOB."costing" IS >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" >>> LEFT OUTER JOIN ( >>> SELECT >>> >>> IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), >>> 0), 0) AS VC0, >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS >>> GROUPBY >>> FROM "costing_internal" >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL >>> /++ FK_COSTING_INTERNAL_COSTING_INDEX_F ++/ >>> WHERE TRUE >>> GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" >>> /++ group sorted ++/ >>> ) T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL >>> /++ SELECT >>> >>> IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), >>> 0), 0) AS VC0, >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS >>> GROUPBY >>> FROM "costing_internal" >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL >>> /++ FK_COSTING_INTERNAL_COSTING_INDEX_F: "costing" IS ?2 ++/ >>> WHERE T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" IS >>> ?2 >>> GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" >>> /++ group sorted ++/: GROUPBY IS >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" >>> ++/ >>> ON T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.GROUPBY >>> INNER JOIN "enquiry_line" T_LINES_FILTER0 >>> /++ PRIMARY_KEY_B95: "Id" IS T_LINES_FILTER0_PART_SUB."Id" ++/ >>> ON TRUE >>> WHERE (T_LINES_FILTER0."enquiry" IS ?1) >>> AND (T_LINES_FILTER0."include" >>> AND ((T_LINES_FILTER0_PART_SUB."Id" IS >>> T_LINES_FILTER0_PART_SUB_JOB."Id") >>> AND (T_LINES_FILTER0."Id" IS T_LINES_FILTER0_PART_SUB."Id"))) >>> GROUP BY T_LINES_FILTER0."enquiry": GROUPBY IS T."Id" >>> */ >>> ON T."Id" IS T_LINES_FILTER0.GROUPBY >>> >>> >>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
