Why performance tuning matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the employees table.

PostgreSQL

SELECT [1] FROM employees;

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

Aeverything

BALL

C*

Dcolumns

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ALL' instead of '*'

Using 'columns' which is not valid SQL

The asterisk (*) selects all columns from the table.

2fill in blank

medium

Complete the code to filter employees with salary greater than 50000.

PostgreSQL

SELECT * FROM employees WHERE salary [1] 50000;

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

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' which means less than

Using '=' which means equal

The '>' operator filters rows where salary is greater than 50000.

3fill in blank

hard

Fix the error in the query to count employees in each department.

PostgreSQL

SELECT department, COUNT([1]) FROM employees GROUP BY department;

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

Asalary

B*

Cdepartment

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(department) which counts non-null departments

Using COUNT(employee_id) which may miss nulls

Using COUNT(*) counts all rows per department, which is the correct way to count employees.

4fill in blank

hard

Fill both blanks to create an index on the salary column to improve query speed.

PostgreSQL

CREATE INDEX idx_salary ON employees USING [1]([2]);

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

ABTREE

BHASH

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using HASH which is less common and limited

Indexing the wrong column like department

BTREE is the default and most common index type in PostgreSQL. Indexing the salary column helps speed up queries filtering by salary.

5fill in blank

hard

Fill all three blanks to write a query that selects employee names and orders them by salary descending.

PostgreSQL

SELECT [1], salary FROM employees ORDER BY [2] [3];

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

Aname

Bsalary

CDESC

DASC

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by name instead of salary

Using ASC instead of DESC for descending order

Select the 'name' column, order by 'salary' in descending order (DESC) to see highest salaries first.

Practice

1. Why is performance tuning important for a PostgreSQL database?

easy

A. It changes the database structure randomly.

B. It makes the database use more disk space.

C. It deletes old data automatically.

D. It helps the database run faster and handle more users efficiently.

Solution

1. Step 1: Understand the goal of performance tuning

   Performance tuning aims to improve speed and efficiency of database operations.

2. Step 2: Identify the correct effect of tuning

   Faster queries and better handling of many users are direct benefits of tuning.

3. Final Answer:

   It helps the database run faster and handle more users efficiently. -> Option D

4. Quick Check:

   Performance tuning = faster, efficient database [OK]

Hint: Performance tuning improves speed and efficiency [OK]

Common Mistakes:

• Thinking tuning deletes data
• Believing tuning increases disk usage unnecessarily
• Assuming tuning changes data structure randomly

2. Which of the following is the correct way to create an index on the column email in PostgreSQL?

easy

A. CREATE INDEX idx_email ON users (email);

B. MAKE INDEX idx_email ON users (email);

C. CREATE INDEX ON users email;

D. INDEX CREATE idx_email users (email);

Solution

1. Step 1: Recall the syntax for creating an index

   The correct syntax is CREATE INDEX index_name ON table_name (column_name);.

2. Step 2: Match the syntax with options

   CREATE INDEX idx_email ON users (email); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_email ON users (email); -> Option A

4. Quick Check:

   CREATE INDEX syntax = CREATE INDEX idx_email ON users (email); [OK]

Hint: Use 'CREATE INDEX index_name ON table (column);' [OK]

Common Mistakes:

• Using wrong keywords like MAKE or INDEX CREATE
• Missing parentheses around column name
• Incorrect order of keywords

3. Consider this query on a large table without indexes:
SELECT * FROM orders WHERE customer_id = 123;
What is the likely effect on performance before and after adding an index on customer_id?

medium

A. Query runs faster after adding the index.

B. Query runs slower after adding the index.

C. Query result changes after adding the index.

D. Query causes an error after adding the index.

Solution

1. Step 1: Understand how indexes affect query speed

   Indexes help the database find rows faster by avoiding full table scans.

2. Step 2: Predict the query performance change

   Adding an index on customer_id speeds up queries filtering by that column.

3. Final Answer:

   Query runs faster after adding the index. -> Option A

4. Quick Check:

   Index on filter column = faster query [OK]

Hint: Indexes speed up filtered queries [OK]

Common Mistakes:

• Thinking indexes slow down SELECT queries
• Expecting query results to change
• Assuming indexes cause errors

4. You wrote this query to improve performance:
CREATE INDEX idx_date ON sales (sale_date);
SELECT * FROM sales WHERE DATE(sale_date) = '2023-01-01';
But the query is still slow. What could be the problem?

medium

A. The index was created on the wrong column.

B. The query uses a function on the column, preventing index use.

C. PostgreSQL does not support indexes on dates.

D. The table has no data.

Solution

1. Step 1: Check if query uses functions on indexed column

   If the query applies a function like DATE(sale_date), the index may not be used.

2. Step 2: Understand index usage rules

   Indexes work best when the column is used directly without transformations.

3. Final Answer:

   The query uses a function on the column, preventing index use. -> Option B

4. Quick Check:

   Functions on column block index use [OK]

Hint: Avoid functions on indexed columns in WHERE clause [OK]

Common Mistakes:

• Assuming PostgreSQL can't index dates
• Ignoring function usage on columns
• Thinking empty table causes slowness

5. A growing app has a users table with millions of rows. You notice slow login queries filtering by username. Which combined approach best improves performance?

hard

A. Store usernames in a separate table without indexes.

B. Drop all indexes and rely on sequential scans.

C. Add an index on username and analyze query plans regularly.

D. Increase server RAM without changing queries or indexes.

Solution

1. Step 1: Identify indexing as key for fast lookups

   Adding an index on username helps queries find users quickly.

2. Step 2: Use query plan analysis to maintain performance

   Regularly checking query plans helps spot slow parts and optimize further.

3. Final Answer:

   Add an index on username and analyze query plans regularly. -> Option C

4. Quick Check:

   Index + query plan analysis = best tuning [OK]

Hint: Combine indexing with query plan checks [OK]

Common Mistakes:

• Removing indexes causes slower queries
• Ignoring query plan analysis
• Relying only on hardware upgrades