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

[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

[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