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 [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.
