Index Strategy Vocabulary Quiz
Learn index strategy vocabulary: composite indexes, covering indexes, partial indexes, index bloat, unused indexes, and index maintenance.
0 / 22 completed
1 / 22
What is a 'covering index' and when is it valuable?
A covering index adds non-key columns to the index (using INCLUDE in PostgreSQL) so the query engine can return results directly from the index. This eliminates expensive heap fetches and dramatically speeds up read-heavy queries.
2 / 22
What is a 'partial index' and what problem does it solve?
Partial indexes index only the rows matching a predicate, making them smaller and faster to maintain than full-table indexes. They're ideal for queries that always filter on the same condition, like querying only active records or only unprocessed jobs.
3 / 22
What is 'index bloat' and how does it affect performance?
In PostgreSQL, deleted or updated rows leave dead tuples in both the table and its indexes until VACUUM cleans them. Index bloat means the index pages contain many dead entries, making scans slower and the index larger than the live data warrants. REINDEX or VACUUM can reclaim space.
4 / 22
A DBA says 'the index isn't being used because the query has a function call on the column'. What does this mean?
Standard B-tree indexes store the column value as-is. If the WHERE clause transforms the value with a function (LOWER(), DATE_TRUNC(), etc.), the planner can't match it to the index. The fix is an expression index: CREATE INDEX ON users (LOWER(email)).
5 / 22
What does 'we dropped the unused index' mean in a database optimisation context?
Every index must be maintained on every write operation. Unused indexes consume write performance and storage with no read benefit. Regularly auditing index usage (pg_stat_user_indexes.idx_scan) and dropping unused ones is a key maintenance practice.
6 / 22
John: 'Hey team, I'm seeing slow performance on this query fetching user profiles. It's running against the users table and filtering by `last_name`. I've suggested creating an index on `last_name` but it doesn't seem to be helping.
Sarah: 'I checked the query execution plan, and it appears the database is using a full table scan instead of utilizing any indexes. It seems like our current indexing strategy isn't optimized for this common use case.'This scenario highlights the importance of understanding why an index isn't being used. While creating an index on `last_name` is often a good starting point, the execution plan reveals that the database isn't using it. This could be due to various reasons – the query optimizer might be choosing a different (less optimal) index, or the index itself may not be a *covering* index, meaning it doesn't include all the columns required by the query. The key takeaway is that simply adding an index isn't always sufficient; you need to analyze why the database isn't leveraging it.
7 / 22
David: 'I'm getting a 5xx error when the API endpoint for retrieving product details is called with a specific ID. The logs show the database query is running fine, but the response from the server indicates a missing index on the `product_id` column.
Maria: 'Okay, let's investigate. I ran EXPLAIN ANALYZE on the query and it returned that the database is performing a full table scan instead of using an index. It seems like the index we have isn't covering the columns used in the `WHERE` clause.'
Which of the following best describes Maria's observation regarding the missing index?The correct answer highlights that 'missing index' signifies the database *should* be using an existing index but isn't. This is crucial because it points to a problem with the query planner's decisions – likely due to outdated statistics or a lack of appropriate index coverage. The other options misinterpret this, suggesting a simple absence of an index (option 1), a configuration error without explanation (option 3), or a term with no practical meaning for developers (option 4).
8 / 22
During a code review discussion about slow query performance for retrieving customer orders, Ben reports: 'The database is running full table scans on the `orders` table when we filter by both `customer_id` and `order_date`. I've suggested creating an index on
(customer_id, order_date), but it doesn't seem to be improving things.' Alice replies: 'I checked the query execution plan. It appears that the database is only using the index on customer_id, not the combined index we created. Could this be due to a missing covering index?'This question tests understanding of 'covering indexes'. A covering index contains all columns required by a query's `WHERE` clause and `SELECT` statement. When this is present, the database can satisfy the entire request solely from the index itself, avoiding costly table scans. The incorrect options misunderstand the optimizer's role or suggest alternative solutions that don't address the core issue of the missing coverage.
9 / 22
John: 'Hey team, I'm seeing slow performance on this query fetching user profiles. It's running against the users table and filtering by `last_name`. I've suggested creating an index on `last_name` but it doesn't seem to be helping.
Sarah: 'I checked the query execution plan, and it appears the database is using a full table scan instead of utilizing any indexes. It seems like our current indexing strategy isn't optimized for this common use case.'This scenario highlights the importance of understanding why an index isn't being used. While creating an index on `last_name` is often a good starting point, the execution plan reveals that the database isn't using it. This could be due to various reasons – the query optimizer might be choosing a different (less optimal) index, or the index itself may not be a *covering* index, meaning it doesn't include all the columns required by the query. The key takeaway is that simply adding an index isn't always sufficient; you need to analyze why the database isn't leveraging it.
10 / 22
David: 'I'm getting a 5xx error when the API endpoint for retrieving product details is called with a specific ID. The logs show the database query is running fine, but the response from the server indicates a missing index on the `product_id` column.
Maria: 'Okay, let's investigate. I ran EXPLAIN ANALYZE on the query and it returned that the database is performing a full table scan instead of using an index. It seems like the index we have isn't covering the columns used in the `WHERE` clause.'
Which of the following best describes Maria's observation regarding the missing index?The correct answer highlights that 'missing index' signifies the database *should* be using an existing index but isn't. This is crucial because it points to a problem with the query planner's decisions – likely due to outdated statistics or a lack of appropriate index coverage. The other options misinterpret this, suggesting a simple absence of an index (option 1), a configuration error without explanation (option 3), or a term with no practical meaning for developers (option 4).
11 / 22
During a code review discussion about slow query performance for retrieving customer orders, Ben reports: 'The database is running full table scans on the `orders` table when we filter by both `customer_id` and `order_date`. I've suggested creating an index on
(customer_id, order_date), but it doesn't seem to be improving things.' Alice replies: 'I checked the query execution plan. It appears that the database is only using the index on customer_id, not the combined index we created. Could this be due to a missing covering index?'This question tests understanding of 'covering indexes'. A covering index contains all columns required by a query's `WHERE` clause and `SELECT` statement. When this is present, the database can satisfy the entire request solely from the index itself, avoiding costly table scans. The incorrect options misunderstand the optimizer's role or suggest alternative solutions that don't address the core issue of the missing coverage.
12 / 22
John: 'Hey team, I'm seeing slow performance on this query fetching user profiles. It's running against the users table and filtering by `last_name`. I've suggested creating an index on `last_name` but it doesn't seem to be helping.
Sarah: 'I checked the query execution plan, and it appears the database is using a full table scan instead of utilizing any indexes. It seems like our current indexing strategy isn't optimized for this common use case.'This scenario highlights the importance of understanding why an index isn't being used. While creating an index on `last_name` is often a good starting point, the execution plan reveals that the database isn't using it. This could be due to various reasons – the query optimizer might be choosing a different (less optimal) index, or the index itself may not be a *covering* index, meaning it doesn't include all the columns required by the query. The key takeaway is that simply adding an index isn't always sufficient; you need to analyze why the database isn't leveraging it.
13 / 22
David: 'I'm getting a 5xx error when the API endpoint for retrieving product details is called with a specific ID. The logs show the database query is running fine, but the response from the server indicates a missing index on the `product_id` column.
Maria: 'Okay, let's investigate. I ran EXPLAIN ANALYZE on the query and it returned that the database is performing a full table scan instead of using an index. It seems like the index we have isn't covering the columns used in the `WHERE` clause.'
Which of the following best describes Maria's observation regarding the missing index?The correct answer highlights that 'missing index' signifies the database *should* be using an existing index but isn't. This is crucial because it points to a problem with the query planner's decisions – likely due to outdated statistics or a lack of appropriate index coverage. The other options misinterpret this, suggesting a simple absence of an index (option 1), a configuration error without explanation (option 3), or a term with no practical meaning for developers (option 4).
14 / 22
During a code review discussion about slow query performance for retrieving customer orders, Ben reports: 'The database is running full table scans on the `orders` table when we filter by both `customer_id` and `order_date`. I've suggested creating an index on
(customer_id, order_date), but it doesn't seem to be improving things.' Alice replies: 'I checked the query execution plan. It appears that the database is only using the index on customer_id, not the combined index we created. Could this be due to a missing covering index?'This question tests understanding of 'covering indexes'. A covering index contains all columns required by a query's `WHERE` clause and `SELECT` statement. When this is present, the database can satisfy the entire request solely from the index itself, avoiding costly table scans. The incorrect options misunderstand the optimizer's role or suggest alternative solutions that don't address the core issue of the missing coverage.
15 / 22
John: 'Hey team, I'm seeing slow performance on this query fetching user profiles. It's running against the users table and filtering by `last_name`. I've suggested creating an index on `last_name` but it doesn't seem to be helping.
Sarah: 'I checked the query execution plan, and it appears the database is using a full table scan instead of utilizing any indexes. It seems like our current indexing strategy isn't optimized for this common use case.'This scenario highlights the importance of understanding why an index isn't being used. While creating an index on `last_name` is often a good starting point, the execution plan reveals that the database isn't using it. This could be due to various reasons – the query optimizer might be choosing a different (less optimal) index, or the index itself may not be a *covering* index, meaning it doesn't include all the columns required by the query. The key takeaway is that simply adding an index isn't always sufficient; you need to analyze why the database isn't leveraging it.
16 / 22
David: 'I'm getting a 5xx error when the API endpoint for retrieving product details is called with a specific ID. The logs show the database query is running fine, but the response from the server indicates a missing index on the `product_id` column.
Maria: 'Okay, let's investigate. I ran EXPLAIN ANALYZE on the query and it returned that the database is performing a full table scan instead of using an index. It seems like the index we have isn't covering the columns used in the `WHERE` clause.'
Which of the following best describes Maria's observation regarding the missing index?The correct answer highlights that 'missing index' signifies the database *should* be using an existing index but isn't. This is crucial because it points to a problem with the query planner's decisions – likely due to outdated statistics or a lack of appropriate index coverage. The other options misinterpret this, suggesting a simple absence of an index (option 1), a configuration error without explanation (option 3), or a term with no practical meaning for developers (option 4).
17 / 22
During a code review discussion about slow query performance for retrieving customer orders, Ben reports: 'The database is running full table scans on the `orders` table when we filter by both `customer_id` and `order_date`. I've suggested creating an index on
(customer_id, order_date), but it doesn't seem to be improving things.' Alice replies: 'I checked the query execution plan. It appears that the database is only using the index on customer_id, not the combined index we created. Could this be due to a missing covering index?'This question tests understanding of 'covering indexes'. A covering index contains all columns required by a query's `WHERE` clause and `SELECT` statement. When this is present, the database can satisfy the entire request solely from the index itself, avoiding costly table scans. The incorrect options misunderstand the optimizer's role or suggest alternative solutions that don't address the core issue of the missing coverage.
18 / 22
Sarah (Senior Developer) comments on a code review: 'This query is hitting the `users` table without an index. It's scanning the entire table for each user profile request – this will cause performance issues as our user base grows!'. What does Sarah *specifically* mean in this context?
Sarah highlights the absence of an index on a frequently used column (`last_name`) within the `users` table. This forces the database to perform a full table scan – examining every row – rather than using an index for faster retrieval based on that field. The query optimizer's choice is crucial; it determines how the database executes the query, and in this case, hasn't utilized an available index.
19 / 22
Alex, during a Slack discussion about slow API responses, reports: 'The endpoint for retrieving product details (
/api/products/{product_id}) is timing out. The database query itself seems fine – it returns data quickly. But the overall response time is high.' What's the *most likely* reason for this?While the database query itself might be fast, the overall response time can still be slow if there's a bottleneck in the communication between the API server and the database. This could be due to network latency, inefficient serialization of the data returned by the database, or limitations in the transport protocol (e.g., too small TCP window). The database itself isn't the source of the delay.
20 / 22
In a PR description for adding an index to the `orders` table, David writes: 'This index will significantly improve query performance when filtering by both `customer_id` and `order_date`.' What is David referring to regarding index strategy?
David is explaining the core benefit of a composite (multi-column) index. When a query filters on multiple columns, an index can be created that efficiently locates rows matching *all* those criteria without needing to scan the entire table. This dramatically reduces the time required for complex queries.
21 / 22
During a standup meeting, Maria says: 'I've identified a slow query retrieving customer orders – it's performing full table scans on the `orders` table when filtering by both `customer_id` and `order_date`.'. What immediate action should she suggest to improve performance?
Maria's observation points directly at a full table scan caused by missing or ineffective indexing. The most direct solution is to create an index that includes both `customer_id` and `order_date`. This allows the database to efficiently locate matching orders without scanning the entire table – a fundamental principle of index strategy.
22 / 22
A DBA reports: 'The query optimizer isn't using the newly created index on `products.name` because it detects a function call (
lower(products.name)) in the WHERE clause.' What does this indicate about index usage?Database optimizers generally avoid using indexes when they encounter functions (like `lower()`) applied to columns in the WHERE clause. The optimizer attempts to rewrite the query on-the-fly without utilizing the index. This is because the function call changes the data being compared against the indexed value, rendering the index ineffective and often leading to a full table scan.
Frequently Asked Questions
What does the "Index Strategy Vocabulary Quiz" exercise practise?
Learn index strategy vocabulary: composite indexes, covering indexes, partial indexes, index bloat, unused indexes, and index maintenance.
How many questions are in this exercise?
This exercise has 22 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database Optimization category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Index Strategy Vocabulary Quiz" part of a larger series?
Yes — it's one exercise in the Database Optimization category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database Optimization category page for related exercises, or browse the main Exercises hub for other IT English topics.