DROP PROPERTY INDEX#

Removes a property index.

Synopsis#

DROP PROPERTY INDEX [ IF EXISTS ] index_name [, ... ] [ CASCADE | RESTRICT ]

bold keywords · italic placeholders you supply · [ ] optional · ... repeatable. See Syntax conventions.

Description#

DROP PROPERTY INDEX drops one or more indexes created by CREATE PROPERTY INDEX, and nothing else.

  • The name is resolved in the graph named by graph_path, and cannot be qualified. A name with a schema on it is rejected with improper property index name. To drop an index in another graph, change graph_path first.

  • Only a property index is accepted. Any other index on the label is refused with "<name>" is not property index: its primary key, an edge label’s endpoint indexes, an index created with SQL.

  • DROP INDEX, by contrast, takes any index of a label, a property index included, so an index made by either statement can be dropped by either.

  • An index that belongs to a constraint is dropped with the constraint, by DROP CONSTRAINT.

Parameters#

IF EXISTS

Issue a notice instead of an error for an index that does not exist. Other names in the same statement are still dropped.

index_name

Unqualified name of the property index to drop. Several names may be given, separated by commas, and they need not be on the same label.

CASCADE

Drop the objects that depend on the index.

RESTRICT

Refuse if anything depends on the index. This is the default. An index rarely has dependents, so the two behave alike most of the time.

Examples#

The examples build their own graph, unindexed, rather than using the example dataset, and drop it at the end.

CREATE GRAPH unindexed;
SET graph_path = unindexed;
CREATE VLABEL movie;
CREATE VLABEL person;
CREATE (:movie {title: 'Arrival', year: 2016}), (:person {name: 'Denis Villeneuve'});
CREATE PROPERTY INDEX movie_title_idx ON movie (title);
CREATE PROPERTY INDEX movie_year_idx ON movie (year);
CREATE PROPERTY INDEX person_name_idx ON person (name);

\dGi unindexed.*

                                                   List of property indexes
   Graph   | LabelName |    IndexName    | Unique | Owner |                              Indexdef
-----------+-----------+-----------------+--------+-------+--------------------------------------------------------------------
 unindexed | movie     | movie_title_idx | f      | agens | CREATE PROPERTY INDEX movie_title_idx ON movie USING btree (title)
 unindexed | movie     | movie_year_idx  | f      | agens | CREATE PROPERTY INDEX movie_year_idx ON movie USING btree (year)
 unindexed | person    | person_name_idx | f      | agens | CREATE PROPERTY INDEX person_name_idx ON person USING btree (name)
(3 rows)

Drop one index#

DROP PROPERTY INDEX movie_title_idx;

DROP PROPERTY INDEX

A second attempt is an error, because the index is gone:

DROP PROPERTY INDEX movie_title_idx;

ERROR:  index "movie_title_idx" does not exist

IF EXISTS turns that into a notice:

DROP PROPERTY INDEX IF EXISTS movie_title_idx;

NOTICE:  index "movie_title_idx" does not exist, skipping
DROP PROPERTY INDEX

Drop several indexes at once#

The names need not belong to the same label:

DROP PROPERTY INDEX movie_year_idx, person_name_idx;

DROP PROPERTY INDEX

\dGi unindexed.*

No matching property indexes found.

Refusals worth recognizing#

The name cannot carry the graph:

CREATE PROPERTY INDEX movie_title_idx ON movie (title);
DROP PROPERTY INDEX unindexed.movie_title_idx;

ERROR:  improper property index name

An index the statement does not own is refused. A label’s primary key is one:

DROP PROPERTY INDEX movie_pkey;

ERROR:  "movie_pkey" is not property index

DROP INDEX takes a property index as well, under its schema-qualified name:

DROP INDEX unindexed.movie_title_idx;

DROP INDEX

Finally, drop the graph the examples built:

DROP GRAPH unindexed CASCADE;

Notes#

Because the name is resolved in graph_path, an index that exists in another graph reads as missing rather than as belonging to someone else:

CREATE GRAPH oneplace;
SET graph_path = oneplace;
CREATE VLABEL movie;
CREATE PROPERTY INDEX movie_title_idx ON movie (title);
CREATE GRAPH otherplace;
SET graph_path = otherplace;
DROP PROPERTY INDEX movie_title_idx;

ERROR:  index "movie_title_idx" does not exist

Two graphs may hold indexes of the same name, so check \dGi before assuming which one a bare name will reach.

DROP GRAPH oneplace CASCADE;
DROP GRAPH otherplace CASCADE;

To turn a label’s indexes off without dropping them, for a bulk load say, use ALTER VLABEL ... DISABLE INDEX and REINDEX VLABEL instead. That keeps the definitions and only invalidates them.

Compatibility#

ISO/IEC 39075 (GQL)#

GQL has no index DDL. Its catalog-modifying statements are limited to schemas, graphs and graph types (§12), so DROP PROPERTY INDEX is AgensGraph’s own.

PostgreSQL#

DROP PROPERTY INDEX corresponds to DROP INDEX, and IF EXISTS, the name list and CASCADE/RESTRICT behave as they do there. It differs in three ways. The name is resolved in graph_path rather than search_path and may not be schema-qualified. CONCURRENTLY is not accepted. And the two statements are strictly partitioned: each refuses the other’s indexes, so a property index can only be dropped by this statement.

See also#