Hi, The url is just the path to the file. I get the same performance issue if I inspect the database from the console, or if I run in a simple java test case.
Having debugged the optimizer slightly, it does seem apparent that the inner group by is optimised for cost before looking at the join that needs to take place. The costs for the various orderings are similar ~ only 1% between the good and the bad ordering, which explains why it is unstable as to which ordering is chosen. - mike On Tue, Mar 10, 2015 at 12:27 PM, Fred&Dani&Pandora&Aquiles < [email protected]> wrote: > 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. > -- 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.
