[ https://issues.apache.org/jira/browse/IGNITE-11891?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrew Mashenkov updated IGNITE-11891: -------------------------------------- Labels: performance (was: ) > 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 > Labels: 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 (v7.6.3#76005)