ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From João Fonsecs (JIRA) <j...@apache.org>
Subject [jira] [Created] (IGNITE-11891) Multi-column index - query out of memory
Date Tue, 04 Jun 2019 15:19:00 GMT
João Fonsecs created IGNITE-11891:
-------------------------------------

             Summary: 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 Fonsecs


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)

Mime
View raw message