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.