Why indexing strategy matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an index on the column 'username' in the 'users' table.

PostgreSQL

CREATE INDEX idx_username ON users([1]);

Drag options to blanks, or click blank then click option'

Ausername

Buser_id

Cemail

Dpassword

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column that is not used in queries often.

Trying to create an index on a non-existent column.

The index should be created on the 'username' column to speed up queries filtering by username.

2fill in blank

medium

Complete the query to find users with the email '[e-mail]' using the index.

PostgreSQL

SELECT * FROM users WHERE [1] = '[e-mail]';

Drag options to blanks, or click blank then click option'

Ausername

Bemail

Cuser_id

Dcreated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column that is not indexed.

Using a column unrelated to the filter condition.

The query filters by the 'email' column, so the index on 'email' would speed this up.

3fill in blank

hard

Fix the error in the index creation statement by completing the blank.

PostgreSQL

CREATE INDEX idx_created [1] users (created_at);

Drag options to blanks, or click blank then click option'

AON

BINDEX

CUNIQUE

DTABLE

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the 'ON' keyword.

Using 'TABLE' instead of 'ON'.

The correct syntax is CREATE INDEX index_name ON table_name (column); 'ON' is required before the table name.

4fill in blank

hard

Fill both blanks to create a unique index on the 'email' column in the 'customers' table.

PostgreSQL

CREATE [1] INDEX idx_email_unique [2] customers (email);

Drag options to blanks, or click blank then click option'

AUNIQUE

BTEMPORARY

CON

DTABLE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'TABLE' instead of 'ON'.

Forgetting 'UNIQUE' for unique indexes.

To create a unique index, use 'UNIQUE' before 'INDEX' and 'ON' before the table name.

5fill in blank

hard

Fill the blanks to drop an index named 'idx_username' from the 'users' table.

PostgreSQL

DROP [1] IF EXISTS [2];

Drag options to blanks, or click blank then click option'

AINDEX

BTABLE

Cidx_username

Dusers

Attempts:

3 left

💡 Hint

Common Mistakes

Using DROP TABLE instead of DROP INDEX.

Omitting the index name.

The correct command to drop an index is DROP INDEX IF EXISTS index_name;