Performance issues with SQL Engine

[nchornii]

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 10000

and 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?

[ostafen]

Hi, @nchornii, have you tried to create indexes to speed up your queries?

[nchornii]

Hi, @nchornii, have you tried to create indexes to speed up your queries?

hey @ostafen, thank you for quick reply
As you can see in the screenshot, I have indexes, I tried different combinations (complex index, single column index etc) - nothing really helps

[ostafen]

@nchornii: most probably the issue is with the "IN" clause causing the query planner to fallback to a full table scan.

Can you run the query without any filter on the "type" column too see if the index is used?

[nchornii]

@ostafen I guess the problem is with offset

Without type, offset 0
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 limit 2500 offset 0
Execution time: 2.104979s

Same query but offset 10000
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 limit 2500 offset 10000
Execution time: 7.924721625s

But without type it faster, but still not perfect
Total records by this query is 17666

[ostafen]

Can you try this query:

SELECT * FROM (
    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;
) WHERE type IN (4, 5, 6, 8, 19, 23) LIMIT 2500 OFFSET 10000;

This way the query planner should be able to use indexes on the nested query, while still filtering by "type".

[nchornii]

@ostafen

SQL Query: SELECT * FROM (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) WHERE type IN (4, 5, 6, 8, 19, 23) LIMIT 2500 OFFSET 10000;
Execution time: 12.955175583s

with offset 0
SQL Query: SELECT * FROM (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) WHERE type IN (4, 5, 6, 8, 19, 23) LIMIT 2500 OFFSET 0;
Execution time: 1.970076792s

Maybe there is the way to increase result rows from to 2500 to let's say 10k?

[ostafen]

I think the issue here is that the query returns a huge number of rows. If your intention is to implement paging, and fetch the results of this query incrementally (in groups of 2500 elements), then using offset and LIMIT is not a good idea, as each query will still generate the full amount of rows and just skip the first OFFSET rows when returning the rows, thus resulting in a quadratic complexity.

Maybe, instead of using OFFSET, an approach could be to additionally sort your results by date and then specify a different start date from query to query in order to skip rows.

[nchornii]

@ostafen basically, I need all 17666 records, but the database can return 2.5k records as max, yes, I can limit date, but to get all the data I need to do like 10 queries, which will lead to the same time.
So maybe there is a way to increase result rows from to 2500 to let's say 10k?

result size limit reached: found more than 2500 rows (the maximum limit). Query constraints can be applied using the LIMIT clause

[ostafen]

Which immudb version are you using and how are you querying results (SDK method)?

That limitation on the maximum number of rows is no more present

[nchornii]

Here is version:
using docker

immudb 1.9.5
Commit  : 86e956fc33214c0f69154587908446d15cc22a88
Built at: Mon, 16 Sep 2024 12:24:07 UTC
Static  : true
================ Config ================
Data dir         : /var/lib/immudb
Address          : 0.0.0.0:3322
Metrics address  : 0.0.0.0:9497/metrics
Sync replication : false
Config file      : configs/immudb.toml
Log format       : text
Max recv msg size: 33554432
Auth enabled     : true
Dev mode         : true
Default database : defaultdb
Maintenance mode : false
Synced mode      : true

SDK version, from Go client connected directly to postgresql server

[nchornii]

@ostafen updated to 1.9.7 but still have this limit

[ostafen]

@nchornii, regarding the dashboard, the limitation still applies.

However, that should no more be true for the Golang SDK. I just tried to insert 2600 rows to a table and query them all using the sql stdlib, and it works.

Can you please check that you are using the correct Golang SDK version (the one from release 1.9.7)?

[nchornii]

@ostafen sorry for late reply. I tried with Golang SDK and there is no limit of 2500, but it exists with /api/v2/db/sqlquery API, is there any setting to change this?

[javea7171]

Keyset pagination is nearly always the answer for paging large datasets