[
https://issues.apache.org/jira/browse/IGNITE-11891?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nikolay Izhikov updated IGNITE-11891:
-------------------------------------
Labels: h2-limitation performance (was: performance)
> Multi-column index - query out of memory
> ----------------------------------------
>
> Key: IGNITE-11891
> URL: https://issues.apache.org/jira/browse/IGNITE-11891
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Affects Versions: 2.7
> Reporter: João Fonseca
> Priority: Major
> Labels: h2-limitation, performance
>
> My application uses a table for logging events. Something like:
>
> {noformat}
> create table event (
> id bigint not null,
> level varchar(8) not null,
> timestamp bigint not null,
> message varchar(4096) not null,
> primary key (id)
> ) ;
> {noformat}
> I have two indexes:
>
> {noformat}
> create index index_event_timestamp on event (timestamp desc)
> create index index_event_level on event (level, timestamp desc)
> {noformat}
> The idea is to support both the following queries:
>
> {noformat}
> select * from event order by timestamp desc limit 25
> select * from event where level = 'WARNING' order by timestamp desc limit 25
> {noformat}
> Once the table size increases to several million records, the second query
> generates OOM on the server. From what I can see (from the explain results),
> the index_event_level is used to fetch records with WARNING level, but the
> timestamp column available with the index is not used in the "order by"
> clause. The server attempts to fetch all records and then sort them by
> timestamp, despite the index already doing this...
> I removed the second index as a work-around, and the query runs faster on the
> first index - it scans index_event_timestamp, and retrieves the records with
> level=WARNING. It's smart to realize that the scan results are already sorted
> correctly.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
