Performance issues with SQL Engine #2062
Description
I'm experiencing performance issues with the SQL Engine:
Table:
CREATE TABLE IF NOT EXISTS finance_report_payments ( id INTEGER AUTO_INCREMENT, tenant_id INTEGER NOT NULL, amount FLOAT NOT NULL, item_amount FLOAT, type INTEGER NOT NULL, is_turnover BOOLEAN NOT NULL, is_irrelevant BOOLEAN NOT NULL, payment_id INTEGER NOT NULL, location_id INTEGER, order_id INTEGER, catering_order_id INTEGER, quantity INTEGER, menu_category_id INTEGER, catering_order_item_id INTEGER, menu_categories_name VARCHAR, tax_rate INTEGER NOT NULL, tax_value FLOAT NOT NULL, date TIMESTAMP NOT NULL, consumer_account_id INTEGER NOT NULL, consumer_full_name VARCHAR(255) NOT NULL, consumer_id INTEGER NOT NULL, comment VARCHAR NOT NULL, created_by INTEGER, updated_by INTEGER, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, deleted_at TIMESTAMP, PRIMARY KEY (id) )Indexes:
I have 982850 records
SQL Query
select * from finance_report_payments where date >= CAST('2025-01-01' as TIMESTAMP) and date <= CAST('2025-01-31' as TIMESTAMP) and tenant_id = 728097 and type in (4, 5, 6, 8, 19, 23) limit 2500 offset 10000and Execution time: 10.533575125s is insainly long
DB Config:
INFO: local.Autoload: true INFO: local.Synced: true INFO: local.SyncFrequency: 20 INFO: local.Replica: false INFO: local.SyncReplication: false INFO: local.SyncAcks: 0 INFO: local.PrefetchTxBufferSize: 0 INFO: local.ReplicationCommitConcurrency: 0 INFO: local.AllowTxDiscarding: false INFO: local.SkipIntegrityCheck: false INFO: local.WaitForIndexing: false INFO: local.FileSize: 536870912 INFO: local.MaxKeyLen: 1024 INFO: local.MaxValueLen: 33554432 INFO: local.MaxTxEntries: 1024 INFO: local.EmbeddedValues: false INFO: local.PreallocFiles: false INFO: local.ExcludeCommitTime: false INFO: local.MaxActiveTransactions: 1000 INFO: local.MVCCReadSetLimit: 100000 INFO: local.MaxConcurrency: 30 INFO: local.MaxIOConcurrency: 1 INFO: local.WriteBufferSize: 4194304 INFO: local.TxLogCacheSize: 1000 INFO: local.VLogCacheSize: 0 INFO: local.VLogMaxOpenedFiles: 10 INFO: local.TxLogMaxOpenedFiles: 10 INFO: local.CommitLogMaxOpenedFiles: 10 INFO: local.WriteTxHeaderVersion: 1 INFO: local.ReadTxPoolSize: 128 INFO: local.TruncationFrequency: 86400000 INFO: local.RetentionPeriod: 0 INFO: local.IndexOptions.FlushThreshold: 100000 INFO: local.IndexOptions.SyncThreshold: 1000000 INFO: local.IndexOptions.FlushBufferSize: 4096 INFO: local.IndexOptions.CleanupPercentage: 0 INFO: local.IndexOptions.CacheSize: 134217728 INFO: local.IndexOptions.MaxNodeSize: 4096 INFO: local.IndexOptions.MaxActiveSnapshots: 100 INFO: local.IndexOptions.RenewSnapRootAfter: 1000 INFO: local.IndexOptions.CompactionThld: 2 INFO: local.IndexOptions.DelayDuringCompaction: 10 INFO: local.IndexOptions.NodesLogMaxOpenedFiles: 10 INFO: local.IndexOptions.HistoryLogMaxOpenedFiles: 1 INFO: local.IndexOptions.CommitLogMaxOpenedFiles: 1 INFO: local.IndexOptions.MaxBulkSize: 1 INFO: local.IndexOptions.BulkPreparationTimeout: 20 INFO: local.AHTOptions.SyncThreshold: 100000 INFO: local.AHTOptions.WriteBufferSize: 16777216 All other simple queries run very long
What am I doing wrong, or are there any suggestions?