Yes, but there's a very large real-world performance difference between
using the index I've created and the unique key that H2 chooses. I don't
know anything about the internals of H2, but I could make some guesses as
to why.
I personally think the most intuitive behavior would be to use a covering
index over a PK or UK that doesn't cover.
Unique Key index is *422 ms*
-- Performance
SELECT MAX(b.id) as id
FROM tbl_b b
GROUP BY b.tbl_a_id
[2015-12-23 11:38:17] 100 rows retrieved starting from 1 in 422ms
(execution: 410ms, fetching: 12ms)
-- Explain Analyze
SELECT
MAX(B.ID) AS ID
FROM PUBLIC.TBL_B B
/* PUBLIC.TBL_B_UK_INDEX_4 */
/* scanCount: 50000 */
GROUP BY B.TBL_A_ID
/* group sorted */
/*
reads: 31554
*/
The TBL_B_IDX is *58 ms*
-- Performance
SELECT MAX(b.id) as id
FROM tbl_b b
-- This where predicate is just to trick H2 to using tbl_b_idx
WHERE b.tbl_a_id > 0
GROUP BY b.tbl_a_id
[2015-12-23 11:44:50] 100 rows retrieved starting from 1 in 58ms
(execution: 50ms, fetching: 8ms)
-- Explain Analyze
SELECT
MAX(B.ID) AS ID
FROM PUBLIC.TBL_B B
/* PUBLIC.TBL_B_IDX: TBL_A_ID > 0 */
/* scanCount: 50000 */
WHERE B.TBL_A_ID > 0
GROUP BY B.TBL_A_ID
On Tuesday, December 22, 2015 at 1:12:18 AM UTC-5, Noel Grandin wrote:
>
>
>
> On 2015-12-22 05:28 AM, James Sheets wrote:
> > Try making another unit test, checking that the below query uses
> 'tbl_b_idx', and the test case will fail:
> >
> > EXPLAIN ANALYZE
> > SELECT MAX(b.id)as id
> > FROM tbl_b b
> > GROUP BY b.tbl_a_id;
> >
>
> For this particular query, you need to read all of the rows, so there is
> no cost difference in using one index over another.
>
--
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
