Session settings

To configure a modifiable session setting, use SET, for example:

To retrieve the current value of a session setting, use eg:

Besides using SHOW, it is also possible to use the current_setting .

search_path

Default: pg_catalog, doc

Modifiable: yes

The list of schemas to be searched when a relation is referenced without a schema.

CrateDB will try to resolve an unqualified relation name against the configured search_path by iterating over the configured schemas in the order they were declared. The first matching relation in the search_path is used. CrateDB will report an error if there is no match.

Note

This setting mirrors the PostgreSQL search_path setting.

Some require access to various tables in the pg_catalog schema. Usually, this is to extract information about built-in data types or functions.

CrateDB implements the system pg_catalog schema and it automatically includes it in the search_path before the configured schemas, unless it is already explicitly in the schema configuration.

Default:

Modifiable: yes

An setting which enables CrateDB to consider whether a JOIN operation should be using the HashJoin implementation instead of the Nested-Loops implementation.

Note

It is not always possible or efficient to use the HashJoin implementation. Having this setting enabled, will only add the option of considering it, it will not guarantee it. See also the available join algorithms for more insights on this topic.

max_index_keys

Default: 32

Modifiable: no

Shows the maximum number of index keys.

Note

The session setting has no effect in CrateDB and exists for compatibility with PostgreSQL.

server_version_num

Modifiable: no

Shows the emulated PostgreSQL server version.

server_version

Default: 10.5

Modifiable: no

Shows the emulated PostgreSQL server version.

optimizer

Default: true

Modifiable: yes

This setting indicates whether a query optimizer rule is activated. The name of the query optimizer rule has to be provided as a suffix as part of the setting e.g. SET optimizer_rewrite_collect_to_get = false.

Note

The optimizer setting is for advanced use only and can significantly impact the performance behavior of the queries.

Experimental session settings might be removed in the future even in minor feature releases.