[jira] [Updated] (IGNITE-11891) Multi-column index - query out of memory
[ 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)
[jira] [Updated] (IGNITE-11891) Multi-column index - query out of memory
[ 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)
[jira] [Updated] (IGNITE-11891) Multi-column index - query out of memory
[ https://issues.apache.org/jira/browse/IGNITE-11891?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Andrew Mashenkov updated IGNITE-11891: -- Ignite Flags: (was: Docs Required) > 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)
[jira] [Updated] (IGNITE-11891) Multi-column index - query out of memory
[ https://issues.apache.org/jira/browse/IGNITE-11891?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Andrew Mashenkov updated IGNITE-11891: -- Issue Type: Improvement (was: Bug) > 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: 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)