Re: [h2] index doesn't help

[Evgenij Ryazanov]

Hello!

WHERE boolean and WHERE boolean = TRUE have no difference.

The actual problem is usage of OR, H2 is unable to use indexes in such 
conditions. Take a look on execution plan produced by the EXPLAIN 
 command:
SELECT
"SEQUENCE",
"MEM",
"MEMADDR",
"MEMVALUE",
"MEMREAD"
FROM "PUBLIC"."DATA"
/* PUBLIC.DATA.tableScan */
WHERE "MEM"
OR "IRQREQUEST"
ORDER BY 1

So you need to convert this query into query with a UNION:
(select sequence, mem, memaddr, memvalue, memRead from data where mem union 
select sequence, mem, memaddr, memvalue, memRead from data where 
irqRequest) order by sequence;
This query can use indexes, see its execution plan:

(SELECT
"SEQUENCE",
"MEM",
"MEMADDR",
"MEMVALUE",
"MEMREAD"
FROM "PUBLIC"."DATA"
/* PUBLIC.MEM: MEM = TRUE */
WHERE "MEM")
UNION
(SELECT
"SEQUENCE",
"MEM",
"MEMADDR",
"MEMVALUE",
"MEMREAD"
FROM "PUBLIC"."DATA"
/* PUBLIC.IRQREQUEST: IRQREQUEST = TRUE */
WHERE "IRQREQUEST")
ORDER BY 1

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/46d20b0f-f1c0-473a-b1d9-2993c0d8b1f2n%40googlegroups.com.

Re: [h2] index doesn't help

[Andreas Reichel]

Greetings.

On Sat, 2023-01-21 at 09:12 -0800, mche...@gmail.com wrote:
> where mem or irqRequest <-- very slow

I'd try 

WHERE mem=true OR irqRequest=true

It is possible that mem=irqRequest is not detected or considered (even
when it resolved to the same meaning).
It is also possible the OR expressions are not considered at all. It's
not a Oracle DB after all.

Good luck
Andreas



-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0112432d3a8e069fec3ba9fc7ca02084d880f138.camel%40manticore-projects.com.

[h2] index doesn't help

[mche...@gmail.com]

Hi all
   This query select sequence,mem,memaddr,memvalue, memRead from data where 
mem or irqRequest order by sequence

where mem <-- fast
where irqRequest <-- fast
where mem or irqRequest <-- very slow

create index mem on data(mem);
create index irqRequest on data(irqRequest);
create index memAndIrqRequest on data(mem, irqRequest);

mem and irqRequest columns are boolean

any hint?

thanks
Peter

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5ab1df66-9325-4b71-b445-45576619654dn%40googlegroups.com.