Hi Daniel, On 08-07-2020 17:33, Daniel Glöckner wrote:
Hi,
I'm currently in the process of evaluating MonetDB. Hats off for this great product! Very impressed by ease-of-use and performance so far.
Looking for information what indexes are used by MonetDB I found a pretty empty page only: https://www.monetdb.org/Documentation/SQLReference/Indexes See: https://www.monetdb.org/Documentation/SQLreference/TableDefinitions/IndexDef... and: https://www.monetdb.org/Documentation/SQLreference/SQLSyntaxOverview#CREATE_...
Note MonetDB is self-tuning, so internally creates indexes itself when they speed up data processing. This relieves the user (or DBA) to do the complex analysis and performance tuning of when to create/remove indices. So in most cases you do not need to worry about or spend time on creating indexes. In general it is advisable to define a primary key (pk) for each table as it creates an internal hash index implicitly, and foreign keys (fk) in tables referencing pks. These definitions will automagically speed up joins between 2 tables when joined on their pk-fk columns. Also specify NOT NULL for columns which may not be NULL. Also you could run the ANALYZE command to update the table's statistics after you have changed a table's data, see: https://www.monetdb.org/Documentation/ServerAdministration/TableStatistics and: https://www.monetdb.org/Documentation/SQLreference/SQLSyntaxOverview#ANALYZE
During our performance evaluation I was searching for common recipes to achieve good performance. Following page is unfortunately also under construction. https://www.monetdb.org/Documentation/SQLReference/PerformanceOptimization/P...
Is there any performance handbook available?
No, not yet. It is under construction. Some tips: - for bulk loading large data sets use COPY INTO FROM file instead of INSERT INTO ... commands. See: https://www.monetdb.org/Documentation/ServerAdministration/LoadingBulkData and https://www.monetdb.org/Documentation/SQLreference/SQLSyntaxOverview#COPY_IN... - when a table is very large and your queries often need only parts of that table consider to partition the table in multiple tables. See: https://www.monetdb.org/Documentation/ServerAdministration/DistributedQueryP... and https://www.monetdb.org/Documentation/SQLreference/SQLSyntaxOverview#CREATE_... and https://www.monetdb.org/blog/updatable-merge-tables - if your queries do some complex/expensive computations multiple times (or in multiple queries), it may be faster to first store the computation result in an extra column of a table and next query the added column to eliminate the computation overhead in the queries. Use: ALTER TABLE [IF EXISTS] qname ADD [COLUMN] column_name to add an extra column and UPDATE qname SET column_name = complex expression to update it with the computed values. You may also add triggers to keep the extra column values updated when new rows are added (INSERT) or updated.
I also did not find information regarding (performance) parameters for the DB server.
See: https://www.monetdb.org/Documentation/Reference/MonetDBServerApplications/ms... It also provides some information on the available optimizer pipes which can be set (initially at startup of the server of during a session). To change it during a session use: https://www.monetdb.org/Documentation/SQLreference/SQLSyntaxOverview#SET_OPT...
Kind regards, Daniel
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list