Results of 1 - 10 of about 33 for effect (0.002 sec.)
- Schema 14695
- Types It is important to use the correct type for encoding columns (e.g., BIGINT , DATE , DATETIME ). While it is always possible to use string types ( VARCHAR , etc.) to encode more specific values, this is not recommended. Strings use mor
...ct table narrow, which speeds up grouping and joining. (For a fixed, known set of values, the ENUM type achieves a similar effect automatically.) First, build a dimension table that assigns an integer key to each distinct value. Sorting the values keep..., PRIMARY KEY , and FOREIGN KEY . These constraints can be beneficial for ensuring data integrity but they have a negative effect on load performance as they necessitate building indexes and performing checks. Moreover, they very rarely improve the per...or more details). Bestpractice Do not define constraints unless your goal is to ensure data integrity. Microbenchmark: The Effect of Primary Keys We illustrate the effect of using primary keys with the LDBC Comment table at scale factor 300 . This tabl... - https://man.mtool.work/duckdb/2.0/guides/performance/schema.html
- Indexing 10396
- DuckDB has two types of indexes: zonemaps and ART indexes. Zonemaps DuckDB automatically creates zonemaps (also known as min-max indexes) for the columns of all general-purpose data types . Operations like predicate pushdown into scan opera
...that filter value (e.g., it can omit a block with a min-max range of 1000 to 2000 when comparing for = 123 or < 400 ). The Effect of Ordering on Zonemaps The more ordered the data within a column, the more valuable the zonemap indexes will be. For exam...t ordering will still be helpful. The best case of ordered data commonly arises with DATETIME columns. Microbenchmark: The Effect of Ordering For an example, let's repeat the microbenchmark for timestamps with an ordered timestamp column using an ascen...erformance of memory-intensive queries. Re-attaching ( DETACH + ATTACH ) the database containing indexes can mitigate this effect, as we deserialize index memory lazily. Disabling index scans and re-attaching after changes can further decrease the impa... - https://man.mtool.work/duckdb/2.0/guides/performance/indexing.html
- Text Types 10396
- In DuckDB, strings can be stored in the VARCHAR field. The field allows storage of Unicode characters. Internally, the data is encoded as UTF-8. Name Aliases Description VARCHAR CHAR , BPCHAR , STRING , TEXT Variable-length character string
...VARCHAR(n) CHAR(n) , BPCHAR(n) , STRING(n) , TEXT(n) Variable-length character string. The maximum length n has no effect and is only provided for compatibility Specifying a Length Limit Specifying the length for the VARCHAR , STRING and TEXT t...ypes is not required and has no effect on the system. Specifying the length will not improve performance or reduce storage space of the strings in the database.... - https://man.mtool.work/duckdb/2.0/sql/data_types/text.html
- Profiling 10187
- Profiling is essential to help understand why certain queries exhibit specific performance characteristics. DuckDB contains several built-in features to enable query profiling, which this page covers. For a high-level example of using EXPLA
...file SELECT SELECT , ALL profiling_output Set a profiling output file Console A filepath profiling_mode Deprecated, has no effect standard standard , detailed , all tracked_metrics Select which metrics to collect ['*'] (all metrics) A list of metric gl...rameter Type Description metrics LIST , STRUCT , or JSON Specifies which metrics to enable mode VARCHAR Deprecated, has no effect (detailed profiling information is always collected) save_location VARCHAR File path for profiling output coverage VARCHAR...ckdb_available_metrics(); Detailed Profiling Deprecated In DuckDB 2.0, the profiling_mode setting is deprecated and has no effect: detailed profiling information, such as the planner and optimizer metrics, is always collected. To restrict the collected... - https://man.mtool.work/duckdb/2.0/dev/profiling.html
- File Formats 9786
- Handling Parquet Files DuckDB has advanced support for Parquet files, which includes directly querying Parquet files . When deciding on whether to query these files directly or to first load them to the database, you need to consider severa
...Parquet files into the database first. The compression algorithm and the row group sizes in the Parquet files have a large effect on performance: study these using the parquet_metadata function . The Effect of Row Group Sizes DuckDB works best on Parqu...0.93 s 491520 0.95 s 983040 0.97 s 1966080 0.88 s The results show that row group sizes <5,000 have a strongly detrimental effect, making runtimes more than 5-10× larger than ideally-sized row groups, while row group sizes between 5,000 and 20,000 are... - https://man.mtool.work/duckdb/2.0/guides/performance/file_formats.html
- COPY Statement 9369
- Examples Read a CSV file into the lineitem table, using auto-detected CSV options: COPY lineitem FROM 'lineitem.csv'; Read a CSV file into the lineitem table, using manually specified CSV options: COPY lineitem FROM 'lineitem.csv' (DELIMITE
...g is cancelled. BOOL auto OVERWRITE_OR_IGNORE Whether or not to allow overwriting files if they already exist. Only has an effect when used with options that write multiple files, such as PARTITION_BY , PER_THREAD_OUTPUT or FILE_SIZE_BYTES . BOOL false...n true , all existing files inside targeted directories will be removed (not supported on remote filesystems). Only has an effect when used with options that write multiple files, such as PARTITION_BY , PER_THREAD_OUTPUT or FILE_SIZE_BYTES . BOOL false...rn is generated that already exists, the path will be regenerated to ensure no existing files are overwritten. Only has an effect when used with options that write multiple files, such as PARTITION_BY , PER_THREAD_OUTPUT or FILE_SIZE_BYTES . BOOL false... - https://man.mtool.work/duckdb/2.0/sql/statements/copy.html
- Pragmas 9176
- The PRAGMA statement is a SQL extension adopted by DuckDB from SQLite. PRAGMA statements can be issued in a similar manner to regular SQL statements. PRAGMA commands may alter the internal state of the database engine, and can influence the
...on-integer literals is not allowed: SELECT 42 ORDER BY 'hello world'; -- Binder Error: ORDER BY non-integer literal has no effect. To allow this behavior, use the order_by_non_integer_literal option: SET order_by_non_integer_literal = true; Implicit Ca...json ), or be .txt . Profiling Mode Deprecated The profiling_mode setting is deprecated in DuckDB 2.0 and its value has no effect: detailed profiling information is always collected. To enable profiling, use PRAGMA enable_profiling . To select which me...he Deprecated The enable_object_cache and disable_object_cache pragmas are deprecated in DuckDB 2.0 and no longer have any effect. Enable caching of objects for e.g., Parquet metadata: PRAGMA enable_object_cache; Disable caching of objects: PRAGMA disa... - https://man.mtool.work/duckdb/2.0/configuration/pragmas.html
- Working with Huge Databases 8551
- This page contains information for working with huge DuckDB database files. While most DuckDB databases are well below 1 TB, in our 2024 user survey , 1% of respondents used DuckDB files of 2 TB or more (corresponding to roughly 10 TB of CS
...able in the TPC-H SF1000 database takes approximately 5 seconds. On block-based storage, the file system has a significant effect on performance when working with large files. On Linux, DuckDB performs best with XFS on large files. For storing large am... - https://man.mtool.work/duckdb/2.0/guides/performance/working_with_huge_databases...
- SAMPLE Clause 8358
- The SAMPLE clause allows you to run the query on a sample from the base table. This can significantly speed up processing of queries, at the expense of accuracy in the result. Samples can also be used to quickly see a snapshot of the data w
...the sample clause is "applied right after anything in the FROM clause" describes the logical point at which sampling takes effect, not where the clause is written. Syntactically, the USING SAMPLE clause appears at the end of the SELECT statement body:... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/sample.html
- Tuning Workloads 7941
- DuckDB aims to perform well out of the box, so most workloads do not require any tuning. When tuning is worthwhile, there is no single configuration or magic number that is optimal for every workload: the right settings depend on what you a
...g a fixed value, identify which of the concerns below match your workload, apply the corresponding change, and measure its effect. If your priority is… Consider Covered in Loading or exporting data larger than memory Disabling preserve_insertion_order...operations preserve row order and which do not, see the Order Preservation page . Parallelism (Multi-Core Processing) The Effect of Row Groups on Parallelism DuckDB parallelizes the workload based on row groups , i.e., groups of rows that are stored t...of data that is scanned. Either sort or partition data by columns that are regularly used for filters: this increases the effectiveness of the filters in reducing IO. To inspect how much remote data is transferred for a query, EXPLAIN ANALYZE can be u... - https://man.mtool.work/duckdb/2.0/guides/performance/how_to_tune_workloads.html
DuckDB 2.0 Documentation / man.mtool.work