performance falling to very slow when use order by, even if the order by columns has indexed.

[Isaac Zeng]

-- my table and indexes

create table "order"(

    serial bigint,

    id bigint primary key,

    "accountId" bigint not null,

    symbol varchar(8) not null,

    side varchar(4) not null,

    px decimal(18, 9),

    qty decimal(18, 9),

    "ordRejReason" varchar,

    text varchar,

    orderingpx decimal(18, 9) as (case side when 'Buy' then 10000000 - px else px end)

);

create index orderbook_idx on "order"(symbol, side, orderingpx, serial);

create index account_symbol_idx on "order"("accountId", symbol);

testing data size 100k-1000k rows

-- explain show me this query use the index orderbook_idx, and it's fast
select * from "order" where symbol = ? and side = ? ;

-- explain show me this query use the index orderbook_idx also, buy it's very slow,
-- even if the index contains the columns order by columns
select * from "order" where symbol = ? and side = ? order by orderingpx,  serial;

[Evgenij Ryazanov]

Hello.

You need an index on (orderingpx, serial) columns. Index on (symbol, side, orderingpx, serial) columns can't be used by order by orderingpx, serial, because such index is ordered by symbol, side, ….

[Isaac Zeng]

I found the blowing query:

select * from "order" where symbol = ? and side = ?

returns results have already ordered by `orderingpx, serial`, it that a definite behavior? can I trust it.

[Evgenij Ryazanov]

No, you can't rely on such behavior. For example, it will be broken in you'll add a smaller index with symbol and side columns.

I missed, however, the WHERE clause in your query. With such clause when only one value is allowed in symbol column and only one value is allowed in side column your index on (symbol, side, orderingpx, serial) can be used for ORDER BY orderingpx, serial, but query execution planner of H2 is not that smart, you need to rewrite your ORDER BY clause as

ORDER BY symbol, side, orderingpx, serial

to help it.

The related issue is here:

https://github.com/h2database/h2database/issues/210

[Isaac Zeng]

very helpful, thanks. I use `order by (symbol, side, orderingpx, serial)` to approach just now.