Is there a way to affect H2's query execution plan?

Technical Discussion
1

I suspect the answer will really fall to…use a different database…but it is still a worthwhile question.

We have clients that run on an IBM iSeries, and the following query performs well, using index TIMEENTRYPARTY, which I assume gets generated for the foreign key relationship:

<entity-find entity-name="basalt.payroll.InvoicedTimeEntryAndVendorInvoiceDetail" list="invoicedTimeEntries" limit="1">
 <econdition field-name="partyId"/>
 <econdition field-name="vendorInvoiceId" operator="is-not-null"/>
 <econdition field-name="toPartyId"/>
 <econdition field-name="statusId" value="InvoicePmtSent"/>
 <select-field field-name="vendorInvoiceId,fromDate,timeEntryId"/>
 <order-by field-name="-fromDate"/>
</entity-find>

This generates the SQL:

SELECT     tent.vendor_invoice_id,
           tent.from_date,
           tent.time_entry_id
FROM       (TIME_ENTRY tent
INNER JOIN INVOICE inv
ON         tent.vendor_invoice_id = inv.invoice_id)
WHERE      (
                      tent.party_id = '12345'
           AND        inv.to_party_id = 'CompCode'
           AND        inv.status_id = 'InvoicePmtSent'
           AND        tent.vendor_invoice_id IS NOT NULL)
GROUP BY   tent.vendor_invoice_id,
           tent.from_date,
           tent.time_entry_id
ORDER BY   tent.from_date DESC nulls last offset 0 rows FETCH first 1 rows ONLY

Up until recently, H2 performed that in < 100ms, but now takes upwards of 17 seconds. Record counts for time entry are 1.7 million; record counts for invoice are 0.2 million.

If I load an old image from August, with about 1.3 million time entries, and 0.16 million ish invoices, H2’s performance resumes its fast response.

The iSeries I’m able to see its query execution plan, and it uses an index to pair the time entries down to about 650 on a particular employee, and decides to make join on that over invoice first.

H2’s query analysis looks like it has stopped using an index…or at least decided to look at invoices first, which is suboptimal, as it makes a huge table scan it would seem:

Fast:

SELECT
    "TENT"."VENDOR_INVOICE_ID",
    "TENT"."FROM_DATE",
    "TENT"."TIME_ENTRY_ID"
FROM "PUBLIC"."TIME_ENTRY" "TENT"
    /* PUBLIC.IDXTIMEENTRYPART: PARTY_ID = '12345' */
    /* WHERE (TENT.VENDOR_INVOICE_ID IS NOT NULL)
        AND (TENT.PARTY_ID = '12345')
    */
    /* scanCount: 513 */
INNER JOIN "PUBLIC"."INVOICE" "INV"
    /* PUBLIC.PRIMARY_KEY_9: INVOICE_ID = TENT.VENDOR_INVOICE_ID */
    ON 1=1
    /* scanCount: 1018 */
WHERE ("TENT"."VENDOR_INVOICE_ID" = "INV"."INVOICE_ID")
    AND (("TENT"."VENDOR_INVOICE_ID" IS NOT NULL)
    AND (("INV"."STATUS_ID" = 'InvoicePmtSent')
    AND (("TENT"."PARTY_ID" = '12345')
    AND ("INV"."TO_PARTY_ID" = 'CompCode'))))
GROUP BY "TENT"."VENDOR_INVOICE_ID", "TENT"."FROM_DATE", "TENT"."TIME_ENTRY_ID"
ORDER BY 2 DESC NULLS LAST
OFFSET 0 ROWS
FETCH NEXT ROW ONLY
/*
reads: 577
*/
    "TENT"."VENDOR_INVOICE_ID",
    "TENT"."FROM_DATE",
    "TENT"."TIME_ENTRY_ID"
FROM "PUBLIC"."TIME_ENTRY" "TENT"
    /* PUBLIC.IDXTIMEENTRYPART: PARTY_ID = '12345' */
    /* WHERE (TENT.VENDOR_INVOICE_ID IS NOT NULL)
        AND (TENT.PARTY_ID = '12345')
    */
INNER JOIN "PUBLIC"."INVOICE" "INV"
    /* PUBLIC.PRIMARY_KEY_9: INVOICE_ID = TENT.VENDOR_INVOICE_ID */
    ON 1=1
WHERE ("TENT"."VENDOR_INVOICE_ID" = "INV"."INVOICE_ID")
    AND (("TENT"."VENDOR_INVOICE_ID" IS NOT NULL)
    AND (("INV"."STATUS_ID" = 'InvoicePmtSent')
    AND (("TENT"."PARTY_ID" = '12345')
    AND ("INV"."TO_PARTY_ID" = 'CompCode'))))
GROUP BY "TENT"."VENDOR_INVOICE_ID", "TENT"."FROM_DATE", "TENT"."TIME_ENTRY_ID"
ORDER BY 2 DESC NULLS LAST
OFFSET 0 ROWS
FETCH NEXT ROW ONLY

Slow:

SELECT
    "TENT"."VENDOR_INVOICE_ID",
    "TENT"."FROM_DATE",
    "TENT"."TIME_ENTRY_ID"
FROM "PUBLIC"."INVOICE" "INV"
    /* PUBLIC.IDXINVOICETOPARTY: TO_PARTY_ID = 'CompCode' */
    /* WHERE (INV.STATUS_ID = 'InvoicePmtSent')
        AND (INV.TO_PARTY_ID = 'CompCode')
    */
    /* scanCount: 105723 */
INNER JOIN "PUBLIC"."TIME_ENTRY" "TENT"
    /* PUBLIC.IDXTIMEENTRYPART: PARTY_ID = '12345' */
    ON 1=1
    /* scanCount: 63960099 */
WHERE ("TENT"."VENDOR_INVOICE_ID" = "INV"."INVOICE_ID")
    AND (("TENT"."VENDOR_INVOICE_ID" IS NOT NULL)
    AND (("INV"."STATUS_ID" = 'InvoicePmtSent')
    AND (("TENT"."PARTY_ID" = '12345')
    AND ("INV"."TO_PARTY_ID" = 'CompCode'))))
GROUP BY "TENT"."VENDOR_INVOICE_ID", "TENT"."FROM_DATE", "TENT"."TIME_ENTRY_ID"
ORDER BY 2 DESC NULLS LAST
OFFSET 0 ROWS
FETCH NEXT ROW ONLY
/*
reads: 11280
*/
    "TENT"."VENDOR_INVOICE_ID",
    "TENT"."FROM_DATE",
    "TENT"."TIME_ENTRY_ID"
FROM "PUBLIC"."INVOICE" "INV"
    /* PUBLIC.IDXINVOICETOPARTY: TO_PARTY_ID = 'CompCode' */
    /* WHERE (INV.STATUS_ID = 'InvoicePmtSent')
        AND (INV.TO_PARTY_ID = 'CompCode')
    */
INNER JOIN "PUBLIC"."TIME_ENTRY" "TENT"
    /* PUBLIC.IDXTIMEENTRYPART: PARTY_ID = '12345' */
    ON 1=1
WHERE ("TENT"."VENDOR_INVOICE_ID" = "INV"."INVOICE_ID")
    AND (("TENT"."VENDOR_INVOICE_ID" IS NOT NULL)
    AND (("INV"."STATUS_ID" = 'InvoicePmtSent')
    AND (("TENT"."PARTY_ID" = '12345')
    AND ("INV"."TO_PARTY_ID" = 'CompCode'))))
GROUP BY "TENT"."VENDOR_INVOICE_ID", "TENT"."FROM_DATE", "TENT"."TIME_ENTRY_ID"
ORDER BY 2 DESC NULLS LAST
OFFSET 0 ROWS
FETCH NEXT ROW ONLY
2

So, not necessarily answering my own question, but I was able to get it to go lickity cut again.

I added an additional e-condition on time entry and that caused H2 to go back to its faster ordering. Oddly, it didn’t pick another index…it used the same one, but it did change the order so as to do time entry first.

For reference, you can put ‘explain analyze’ in front of your query in H2, and it will show more about the path it takes and reads required.

1 Like