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.
