Why performance tuning matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is performance tuning important in databases?

Imagine you run a busy online store. Why should you care about performance tuning your database?

A It helps the database run faster and handle more users smoothly.

B It makes the database use more disk space to store extra data.

C It automatically fixes all data errors without human help.

D It reduces the need for backups and data security.

💡 Hint

Think about what happens when many people visit your store at once.

Performance tuning improves speed and efficiency, so many users can access data quickly without delays.

❓ query_result

intermediate

Identify the slow query from execution times

Given these query execution times in milliseconds, which query is the slowest and needs tuning?

• Query A: 120 ms
• Query B: 450 ms
• Query C: 90 ms
• Query D: 300 ms

A Query B

B Query A

C Query C

D Query D

💡 Hint

Look for the highest number in milliseconds.

The slowest query is the one with the highest execution time, which is Query B at 450 ms.

📝 Syntax

advanced

Which SQL query uses an index to improve performance?

Consider a table orders with an index on the customer_id column. Which query best uses this index to speed up data retrieval?

A SELECT * FROM orders WHERE order_date > '2023-01-01';

B SELECT * FROM orders WHERE LOWER(customer_name) = 'alice';

C SELECT * FROM orders ORDER BY order_total DESC;

D SELECT * FROM orders WHERE customer_id = 123;

💡 Hint

Indexes speed up searches on the indexed column.

Query A filters by customer_id, which has an index, so it uses the index to find rows quickly. Other queries filter or sort by columns without indexes.

❓ optimization

advanced

Choose the best way to reduce query execution time

You have a query joining two large tables without indexes, causing slow performance. What is the best way to improve it?

A Rewrite the query to use SELECT * instead of specific columns.

B Increase the server's RAM without changing the query.

C Add indexes on the columns used in the JOIN condition.

D Run the query less often to reduce load.

💡 Hint

Think about how databases find matching rows quickly.

Adding indexes on join columns helps the database quickly match rows, reducing execution time significantly.

🔧 Debug

expert

Why does this query cause slow performance despite indexing?

Given a table products with an index on category_id, this query is slow:

SELECT * FROM products WHERE category_id = 5 AND price > 1000;

Why might the index not help here?

A Because the database does not support indexes on numeric columns.

B Because the query filters on two columns but the index is only on one, causing a full scan.

C Because the index is corrupted and needs rebuilding.

D Because the query uses SELECT * which disables index usage.

💡 Hint

Think about how indexes work with multiple conditions.

The index on category_id helps filter by that column only. Since price is also filtered but not indexed, the database may scan many rows after filtering category_id, slowing the query.

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