MCQs on Indexes and Performance Basics | PostgreSQL Database
Indexes are crucial for optimizing query performance in PostgreSQL. This guide explores index creation, usage, and analysis with tools like EXPLAIN, enabling efficient data retrieval and management.
What are Indexes?
- What is the primary purpose of indexes in PostgreSQL?
a) Storing data efficiently
b) Optimizing query performance
c) Managing database schemas
d) Enabling transaction logs - Which of the following best describes an index?
a) A data storage table
b) A database connection method
c) A data structure to improve query efficiency
d) A type of database backup - Which type of data operation benefits most from indexes?
a) Sequential scans
b) Random updates
c) Search queries
d) Bulk data loads - What is a disadvantage of using indexes in a database?
a) Increased query complexity
b) Reduced query performance
c) Increased storage requirements
d) Difficulty in data visualization - What type of index is automatically created for a primary key in PostgreSQL?
a) Hash index
b) B-tree index
c) GIN index
d) BRIN index
Creating and Dropping Indexes
- Which command is used to create an index in PostgreSQL?
a) CREATE INDEX
b) ADD INDEX
c) MAKE INDEX
d) INSERT INDEX - What is required to create an index on a table?
a) A foreign key constraint
b) An existing column
c) A unique constraint
d) A schema privilege - Which command removes an existing index in PostgreSQL?
a) REMOVE INDEX
b) DROP INDEX
c) DELETE INDEX
d) TRUNCATE INDEX - Can multiple indexes be created on the same table in PostgreSQL?
a) No, only one index is allowed per table
b) Yes, but only for unique constraints
c) Yes, with different columns or conditions
d) No, it is restricted by default - What is a potential impact of dropping an index?
a) Reduced query performance for related queries
b) Loss of table data
c) Automatic recreation during the next query
d) Conversion to a primary key
Understanding Index Scans
- What does an index scan in PostgreSQL indicate?
a) The database is reading all rows sequentially
b) The database is using the index to find specific rows
c) The database is creating a new index
d) The database is ignoring existing indexes - What type of query is most likely to use an index scan?
a) SELECT * FROM table
b) SELECT column FROM table WHERE column = value
c) INSERT INTO table VALUES (…)
d) DELETE FROM table - What is a sequential scan in PostgreSQL?
a) Scanning the index for all matching rows
b) Scanning the entire table row by row
c) Creating an index dynamically during query execution
d) Optimizing index scans for faster performance - Which factor determines whether PostgreSQL uses an index scan or a sequential scan?
a) The size of the table
b) The storage engine
c) The network latency
d) The database version - What happens if an index does not exist for a query’s condition?
a) PostgreSQL raises an error
b) The query fails to execute
c) A sequential scan is performed instead
d) PostgreSQL creates an index automatically
Simple Use of EXPLAIN
- What is the purpose of the EXPLAIN command in PostgreSQL?
a) To visualize database schemas
b) To analyze and display query execution plans
c) To update database indexes
d) To monitor database performance over time - Which of the following options can be used with EXPLAIN to show actual execution times?
a) EXPLAIN PERFORMANCE
b) EXPLAIN TIMINGS
c) EXPLAIN ANALYZE
d) EXPLAIN METRICS - What does the cost value in an EXPLAIN output represent?
a) The time required to execute the query
b) The monetary cost of the query
c) The estimated effort to run the query
d) The total size of the result set - How does EXPLAIN ANALYZE differ from EXPLAIN?
a) It predicts execution plans without running the query
b) It shows actual execution statistics after running the query
c) It only works for indexed tables
d) It can modify the query plan for optimization - Which part of the EXPLAIN output describes the use of an index?
a) Sequential Scan
b) Bitmap Heap Scan
c) Index Scan
d) Table Scan - Why is EXPLAIN useful for database optimization?
a) It provides insights into query performance bottlenecks
b) It updates outdated indexes automatically
c) It enforces primary key constraints
d) It generates optimized execution plans - Which parameter in the EXPLAIN output indicates rows processed?
a) Rows
b) Cost
c) Time
d) Buffers - What does a high cost value in an EXPLAIN plan suggest?
a) The query is optimized
b) The query is expensive to execute
c) The index is not being used
d) The table has insufficient rows - How can you force PostgreSQL to use a specific index in a query?
a) Use FORCE INDEX
b) Add an INDEX hint to the query
c) Rewrite the query to match the index structure
d) Use SET INDEX command - Which EXPLAIN output would suggest an index is underutilized?
a) Index Scan with low cost
b) Sequential Scan on a large table
c) Bitmap Heap Scan with high rows
d) Index Only Scan
Advanced EXPLAIN Features
- What does “Bitmap Heap Scan” in EXPLAIN output signify?
a) Rows are scanned sequentially
b) Index results are combined for efficient access
c) A new index is created dynamically
d) No index is used in the query - How can EXPLAIN help identify redundant indexes?
a) By listing all indexes in the output
b) By showing high-cost sequential scans
c) By revealing unused indexes in execution plans
d) By analyzing row counts in indexes - Which EXPLAIN keyword allows capturing detailed buffer usage?
a) EXPLAIN MEMORY
b) EXPLAIN ANALYZE BUFFERS
c) EXPLAIN COST
d) EXPLAIN DETAILS - When would “Index Only Scan” appear in an EXPLAIN output?
a) When only indexed columns are accessed
b) When sequential scanning is faster
c) When no index matches the query
d) When multiple indexes are combined - What is the advantage of EXPLAIN ANALYZE over EXPLAIN?
a) It can modify query execution plans
b) It measures real-time query execution metrics
c) It provides additional index creation options
d) It suppresses sequential scan details
Answer Key
| Qno | Answer |
|---|---|
| 1 | b) Optimizing query performance |
| 2 | c) A data structure to improve query efficiency |
| 3 | c) Search queries |
| 4 | c) Increased storage requirements |
| 5 | b) B-tree index |
| 6 | a) CREATE INDEX |
| 7 | b) An existing column |
| 8 | b) DROP INDEX |
| 9 | c) Yes, with different columns or conditions |
| 10 | a) Reduced query performance for related queries |
| 11 | b) The database is using the index to find specific rows |
| 12 | b) SELECT column FROM table WHERE column = value |
| 13 | b) Scanning the entire table row by row |
| 14 | a) The size of the table |
| 15 | c) A sequential scan is performed instead |
| 16 | b) To analyze and display query execution plans |
| 17 | c) EXPLAIN ANALYZE |
| 18 | c) The estimated effort to run the query |
| 19 | b) It shows actual execution statistics after running the query |
| 20 | c) Index Scan |
| 21 | a) It provides insights into query performance bottlenecks |
| 22 | a) Rows |
| 23 | b) The query is expensive to execute |
| 24 | c) Rewrite the query to match the index structure |
| 25 | b) Sequential Scan on a large table |
| 26 | b) Index results are combined for efficient access |
| 27 | c) By revealing unused indexes in execution plans |
Here are the answers for the last three questions:
| Qno | Answer |
|---|---|
| 28 | b) EXPLAIN ANALYZE BUFFERS |
| 29 | a) When only indexed columns are accessed |
| 30 | b) It measures real-time query execution metrics |