[
https://issues.apache.org/jira/browse/IGNITE-11891?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16856733#comment-16856733
]
João Fonseca commented on IGNITE-11891:
---------------------------------------
In the H2 documentation, I found this:
[http://www.h2database.com/html/performance.html]
{noformat}
Multi-column indexes are used if all or the first columns of the index are
used. Both equality lookup and range scans are supported. Indexes are used to
order result sets, but only if the condition uses the same index or no index at
all.
{noformat}
I changed my query to
{code:java}
select * from event where level = 'WARNING' order by level, timestamp desc
limit 25
{code}
Adding the level column explicitly to the order by clause solves the problem,
the query just dumps the results straight out of the index, without trying to
sort the results.
I guess this makes sense, although the optimiser should be smart enough to do
this automatically. If the original query was
{code:java}
select * from event where level in ( 'WARNING', 'INFO' ) order by timestamp
desc limit 25
{code}
the index could not be dumped directly, as the WARNING and INFO records are
stored in different locations of the index.
Thanks for the discussion, which enabled me to understand this issue a lot
better. I'm not sure anymore if this qualifies as a bug. At most, it's
something that could be optimised.
> Multi-column index - query out of memory
> ----------------------------------------
>
> Key: IGNITE-11891
> URL: https://issues.apache.org/jira/browse/IGNITE-11891
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.7
> Reporter: João Fonseca
> Priority: Major
>
> 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
(v7.6.3#76005)
