How to Drop an Index in PostgreSQL
When you use indexes to optimize query performance in PostgreSQL, there will be times when you may want to remove an index from the system. PostgreSQL makes it easy to accomplish this with the help of the
DROP INDEX statement. This tutorial will walk you through a series of examples that demonstrate how to create an index and then drop the index.
If you’re planning to follow along with this tutorial and create your own sample index to drop, you’ll need to make sure that PostgreSQL server has been properly installed and configured. The PostgreSQL server also needs to be running in the background.
If you’re using the Linux or Windows operating systems on your machine, you can download PostgreSQL here.
What is the DROP INDEX statement in PostgreSQL?
DROP INDEX statement is used to remove a specified existing index within the PostgreSQL database.
The statement uses the following syntax:
DROP INDEX [ CONCURRENTLY]
[ IF EXISTS ] the_index_name
[ CASCADE | RESTRICT ];
Let’s take a closer look at the syntax shown above:
As the name implies, we specify the name of the index that we want to remove in
the_index_name. This phrase comes immediately after the clause
IF EXISTSserves as a conditional option where the specified index name will only be deleted if it exists within the database system. If we tried to remove an index that doesn’t exist while using the
IF EXISTSclause, PostgreSQL will return a notification instead of an error.
CONCURRENTLYclause will cause PostgreSQL to wait for the conflicting transactions to finish before removing the index.
CASCADEoption will drop the index itself as well as all objects that depend on it.
RESTRICToption will not allow an index to be dropped if PostgreSQL detects that the index has one or more dependent objects. This option is set by default in the
Sample Data Set
Before we can demonstrate how to use the PostgreSQL
DROP INDEX statement, we’ll need to create a sample table. We can create a table named
leader_tbl using the following statement:
CREATE TABLE leader_tbl(
leader_id INT PRIMARY KEY NOT NULL,
leader_fname TEXT NOT NULL,
leader_lname TEXT NOT NULL
Then, we’ll use the statement shown below to insert a few records into the
INSERT INTO leader_tbl (leader_id,leader_fname,leader_lname)
Creating an Index
In this example, we’ll be creating an index on the
leader_fname column of the
leader_tbl table. We’ll use the following statement:
CREATE INDEX idx_leader_first_name
ON leader_tbl (leader_fname);
NOTE: To learn more about the PostgreSQL
CREATE INDEX command, consult this article: PostgreSQL CREATE INDEX.
Now, let’s try a basic
SELECT query on the
leader_tbl table that we created earlier. Our query will look like this:
SELECT * FROM leader_tbl WHERE leader_fname = 'oda';
The statement shown above simply returns a row that has a value of
oda in the column
In some cases, PostgreSQL’s built-in optimizer will not use an index if PostgreSQL finds it more efficient to just browse the whole table. We can see an example of this in action with the following query:
EXPLAIN SELECT * FROM leader_tbl WHERE leader_fname = 'oda';
You’ll get results that look something like this:
Seq Scan ON leader_tbl (cost=0.00..1.05 ROWS=1 width=68)
FILTER: (leader_fname = 'oda'::text)
We can see that the index
idx_leader_first_name isn’t very useful at this moment; therefore, we’ll delete it using the
DROP INDEX command:
DROP INDEX idx_leader_first_name;
We know that the delete operation was successful as we received a notification from PostgreSQL stating “DROP INDEX”.
Although indexes can go a long way toward optimizing queries and helping your database applications operate at peak performance, there are times when an index simply isn’t needed. In those cases, it’s important to know how to remove an index from the system. This tutorial explained how to use the
DROP INDEX statement to remove an index. With the examples we provided as a guide, you’ll be able to delete indexes when needed in your own database system.
Starting PostgreSQL Server
Let’s look at the different steps needed to start up PostgreSQL on Linux and Windows machines:
- To start the PostgreSQL service in a Linux environment, use the following command:
sudo service postgresql start
- To check whether the service is running, use the command shown below:
service postgresql status
- The output will look like this:
‚óè postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)
Aug 01 14:51:20 user-UX330UAK systemd: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
- The instructions needed to start, stop and restart PostgreSQL in a Windows environment are shown below:
- First, open Control Panel
- Then, open Administrative Tools
- Open Services
- Find the PostgreSQL Server service
- Start, Stop or Restart the service
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started