Index Command

The index command allows managing indexes in the VCDB v5. It provides subcommands for checking the status of indexes, as well as for creating and dropping them.

Indexes improve query performance and enable faster data retrieval, both of which are especially important for large databases. However, maintaining indexes comes with a cost, as they are updated in real time, potentially slowing down processes such as data imports or deletions. The index command gives you control over handling the indexes.

Synopsis

vcdb index [OPTIONS] COMMAND

Options

The index command inherits global options from the main vcdb command. Additionally, the index status and index create subcommands offer more options tailored to their respective tasks.

Global options

Option Description Default value

[@<filename>…​]

One or more argument files containing options.

-h, --help

Show a help message and exit.

-V, --version

Print version information and exit.

--config-file=<file>

Load configuration from this file.

-L, --log-level=<level>

Log level: fatal, error, warn, info, debug, trace.

info

--log-file=<file>

Write log messages to this file.

--quiet

Disable console log messages.

--pid-file=<file>

Create a file containing the process ID.

--plugins=<dir>

Load plugins from this directory.

--use-plugin=<plugin[=true|false]>
[,<plugin[=true|false]>…​]

Enable or disable plugins with a matching fully qualified class name.

true

For more details on the global options and usage hints, see here.

Index status options

Option Description Default value

-o, --output=<file|->

Write index status to JSON. Use - for stdout.

The above option is only available for the index status command.

Create index options

Option Description Default value

-m, --index-mode=<mode>

Index mode for property value columns: partial, full. Null values are not indexed in partial mode.

partial

The above option is only available for the index create command.

Database connection options

Option Description Default value

-H, --db-host=<host>

Name of the host on which the VCDB is running.

-P, --db-port=<port>

Port of the VCDB server.

5432

-d, --db-name=<database>

Name of the VCDB database to connect to.

-S, --db-schema=<schema>

Schema to use when connecting to the VCDB.

citydb or username

-u, --db-username=<user>

Username to use when connecting to the VCDB.

-p, --db-password
[=<password>]

Password to use when connecting to the VCDB. Leave empty to be prompted.

--db-property=<property=value>
[,<property=value>…​]

Database-specific connection properties.

For more details on the database connection options and usage hints, see here.

Commands

Command Description

help

Display help information about the specified command.

status

Show indexes with their status in the database.

create

Create indexes on the database tables.

drop

Drop indexes on the database tables.

Usage

Supported indexes

The index command operates on a subset of all indexes defined in the VCDB v5. This subset includes the most time-intensive spatial indexes and regular indexes on columns crucial for querying and filtering features.

The following indexes are supported by vcdb-tool:

Table Column(s) Index type

APPEARANCE

theme

Regular

FEATURE

identifier, identifier_codespace

Regular

FEATURE

envelope

Spatial

FEATURE

creation_date

Regular

FEATURE

valid_from

Regular

FEATURE

valid_to

Regular

GEOMETRY_DATA

geometry

Spatial

PROPERTY

name

Regular

PROPERTY

namespace_id

Regular

PROPERTY

val_timestamp

Regular

PROPERTY

val_double

Regular

PROPERTY

val_int

Regular

PROPERTY

val_lod

Regular

PROPERTY

val_string

Regular

PROPERTY

val_uom

Regular

PROPERTY

val_uri

Regular

Checking index status

The index status command lists all supported indexes in the VCDB v5 with their current status, indicating whether they are enabled (on) or dropped (off). Each index is listed with the name of the table and the column(s) for which it is defined. This command helps you understand the current index situation and decide whether an index action is needed to optimize subsequent database operations.

When setting up a new instance of the VCDB v5, all indexes are enabled by default.

The following example demonstrates how to use the index status command, which then prints the index statuses to the console.

  • Linux

  • Windows CMD

./vcdb index status \
    -H localhost \
    -d citdb \
    -u citydb_user \
    -p mySecret
vcdb index status ^
    -H localhost ^
    -d citdb ^
    -u citydb_user ^
    -p mySecret

The index status list can optionally be written as JSON using the --output option. If a file path is provided, the JSON output will be written to that file. If - is specified instead of a file path, the JSON output will be written to stdout. This JSON output can be easily piped to and processed by external tools.

The following examples demonstrate the usage of the --output option.

  • Linux

  • Windows CMD

./vcdb index status [...] -o status.json      # write JSON to a file
./vcdb index status [...] -o -                # write JSON to stdout
./vcdb index status [...] -o - > status.json  # redirect stdout to a file
vcdb index status [...] -o status.json      # write JSON to a file
vcdb index status [...] -o -                # write JSON to stdout
vcdb index status [...] -o - > status.json  # redirect stdout to a file

An excerpt of the generated JSON output is shown below.

{
  "indexes":[
    {
      "table":"appearance",
      "columns":[
        "theme"
      ],
      "type":"normal",
      "name":"appearance_theme_inx",
      "status":"on"
    },
    ...
}

Each entry in the "indexes" array provides the following information:

  • "table": The name of the table on which the index is defined.

  • "columns": The column(s) covered by the index.

  • "type": Either normal (a standard index) or spatial (an index on geometry columns).

  • "name": The database name of the index.

  • "status": Whether the index is currently enabled (on) or dropped (off).

The structure of the JSON output is defined by the JSON Schema file index-status.json.schema, which is located in the json-schema folder of the vcdb-tool installation directory.

Creating indexes

The index create command lets you create the predefined indexes supported by vcdb-tool. These indexes are crucial for optimizing query performance and enabling fast data retrieval, particularly in large databases. It is recommended to create them before exporting data, especially if the export involves filtering features.

Since indexes on the val_* columns of the PROPERTY table are often sparsely populated with many NULL values, the --index-mode option offers the following modes for creating indexes on property value columns:

  • partial: Excludes NULL values from the indexes, resulting in smaller index sizes, faster creation and maintenance, and improved query performance. However, partial indexes cannot be used to search for NULL values. This is the default mode.

  • full: Indexes all values, ensuring comprehensive coverage but potentially increasing storage requirements and the time needed for creation and maintenance.

The example below creates indexes on a VCDB v5 instance, using the full mode for property value columns.

  • Linux

  • Windows CMD

./vcdb index create \
    --index-mode=full
    -H localhost \
    -d citdb \
    -u citydb_user \
    -p mySecret
vcdb index create ^
    --index-mode=full
    -H localhost ^
    -d citdb ^
    -u citydb_user ^
    -p mySecret
Depending on the size of the database, the indexing process may take a significant amount of time, as it involves processing large amounts of data.

Dropping indexes

The index drop command removes the predefined indexes supported by vcdb-tool. Temporarily disabling indexes can significantly speed up bulk data operations, such as large imports or deletions, by eliminating the overhead of updating indexes. It can also be useful for database maintenance tasks, such as freeing up storage space.

Dropping indexes is simple, as shown in the example below.

  • Linux

  • Windows CMD

./vcdb index drop \
    -H localhost \
    -d citdb \
    -u citydb_user \
    -p mySecret
vcdb index drop ^
    -H localhost ^
    -d citdb ^
    -u citydb_user ^
    -p mySecret
While dropping indexes is a fast operation, re-creating them can be time-consuming, especially in large databases. As the database grows, the overhead of dropping and re-creating indexes may outweigh the benefits, particularly when dealing with smaller sets of features rather than bulk data operations.
Even after dropping indexes with the index drop command, a minimal set of indexes remains enabled to ensure that basic queries, such as filtering features by their objectid or termination_date, continue to perform efficiently.