[h2] request takes too much time

[sim]

Hi,

Let's assume I have two tables

table clients
=============
cl_id bigint primary key,
cl_name varchar

table requests
==============
req_id identity primary key,
req_type integer,
req_date timestamp,
cl_id bigint foreign key

And I want to run a request like

select c.*,r.* 
from requests r 
join clients c on r.cl_id = c.cl_id 
where r.req_id between 1L and 2L

It takes ~40s to get 2 rows

explain analyze:

SELECT
    C.CL_ID,
    C.CL_NAME,
    R.REQ_ID,
    R.REQ_TYPE,
    R.REQ_DATE,
    R.CL_ID
FROM PUBLIC.CLIENTS C
    /* PUBLIC.CLIENTS.tableScan */
    /* scanCount: 11 */
INNER JOIN PUBLIC.REQUESTS R
    /* PUBLIC.REQUESTS_CLID_IDX: CL_ID = C.CL_ID */
    ON 1=1
    /* scanCount: 7528481 */
WHERE (R.CL_ID = C.CL_ID)
    AND ((R.REQ_ID >= 1)
    AND (R.REQ_ID <= 2))
/*
reads: 3439464
*/


Not helps much a request like
select c.*,r.* 
from (select * from requests where req_id between 1L and 2L) r 
join clients c on r.cl_id = c.cl_id 

explain analyze:

SELECT
    C.CL_ID,
    C.CL_NAME,
    R.REQ_ID,
    R.REQ_TYPE,
    R.REQ_DATE,
    R.CL_ID
FROM PUBLIC.CLIENTS C
    /* PUBLIC.CLIENTS.tableScan */
    /* scanCount: 11 */
INNER JOIN (
    SELECT
        REQUESTS.REQ_ID,
        REQUESTS.REQ_TYPE,
        REQUESTS.REQ_DATE,
        REQUESTS.CL_ID
    FROM PUBLIC.REQUESTS
        /* PUBLIC.PRIMARY_KEY_F: REQ_ID >= 1
            AND REQ_ID <= 2
         */
    WHERE (REQ_ID >= 1)
        AND (REQ_ID <= 2)
) R
    /* SELECT
        REQUESTS.REQ_ID,
        REQUESTS.REQ_TYPE,
        REQUESTS.REQ_DATE,
        REQUESTS.CL_ID
    FROM PUBLIC.REQUESTS
        /++ PUBLIC.REQUESTS_CLID_IDX: CL_ID IS ?1 ++/
        /++ scanCount: 1 ++/
    WHERE (REQUESTS.CL_ID IS ?1)
        AND ((REQ_ID >= 1)
        AND (REQ_ID <= 2)): CL_ID = C.CL_ID
     */
    ON 1=1
    /* scanCount: 12 */
WHERE R.CL_ID = C.CL_ID
/*
reads: 3439464
*/

Is it ok such a simple request takes so much time?

-- 
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.