MCQs on Advanced Query Optimization | PostgreSQL Database

Understanding advanced query optimization in PostgreSQL is crucial for achieving high-performance database management. This set of 30 MCQs focuses on execution plans, joins, subqueries, index optimization, and parallel query techniques.

Advanced Query Optimization | PostgreSQL

Understanding Query Execution Plans (EXPLAIN ANALYZE)

1. What does the EXPLAIN ANALYZE command do in PostgreSQL?
   • a) Shows the query execution plan without executing the query
   • b) Displays the execution plan along with actual runtime statistics
   • c) Displays only runtime statistics
   • d) Executes the query without showing the execution plan
2. Which of the following information is provided by EXPLAIN ANALYZE?
   • a) Query execution time
   • b) Number of rows returned
   • c) Estimated cost
   • d) All of the above
3. What does the Total runtime value represent in the output of EXPLAIN ANALYZE?
   • a) The time taken by the query to compile
   • b) The overall time taken to execute the query
   • c) The time spent on I/O operations
   • d) The estimated execution time
4. Which of the following statements is true about the EXPLAIN ANALYZE output?
   • a) It provides only the logical steps of query execution
   • b) It includes both the planned and actual execution times
   • c) It shows only the query plan but not execution statistics
   • d) It includes only the estimated execution cost
5. How can you identify a bottleneck in a query using EXPLAIN ANALYZE?
   • a) By analyzing the number of rows processed
   • b) By reviewing the execution time of each step
   • c) By comparing the cost of different operators
   • d) All of the above
6. Which of the following is an example of a query execution plan operation shown by EXPLAIN ANALYZE?
   • a) Sequential Scan
   • b) Index Scan
   • c) Hash Join
   • d) All of the above
7. What is the Cost field in the EXPLAIN ANALYZE output?
   • a) The time taken for the execution of the query
   • b) The estimated I/O cost for the operation
   • c) The total time for disk I/O operations
   • d) The estimated cost of executing a step in the query plan
8. What does a Seq Scan indicate in a query plan?
   • a) A full table scan
   • b) An index scan on a column
   • c) A join operation between two tables
   • d) A sorting operation

Optimizing Joins and Subqueries

9. Which type of join is generally faster in PostgreSQL?
   • a) INNER JOIN
   • b) LEFT JOIN
   • c) RIGHT JOIN
   • d) CROSS JOIN
10. How can a query be optimized if EXPLAIN ANALYZE shows a sequential scan on a large table?
    • a) Use an index on the relevant column
    • b) Convert the query into a subquery
    • c) Use a FULL OUTER JOIN
    • d) Remove the WHERE clause
11. What is the main advantage of using an index for joins?
    • a) Faster retrieval of matching rows
    • b) Reduced I/O operations
    • c) Faster sorting of results
    • d) Both a and b
12. Which of the following can cause a slow performance in subqueries?
    • a) Subqueries in the SELECT clause
    • b) Uncorrelated subqueries
    • c) Subqueries with a high number of rows
    • d) Subqueries with limited data
13. How can correlated subqueries be optimized in PostgreSQL?
    • a) By turning them into joins
    • b) By reducing the number of outer queries
    • c) By avoiding the use of the IN operator
    • d) All of the above
14. What is the purpose of using a WITH clause (CTE) in a query?
    • a) To break down complex queries into simpler steps
    • b) To optimize subqueries
    • c) To improve query readability
    • d) All of the above
15. Which join type can lead to slower performance if the tables involved are large and unindexed?
    • a) INNER JOIN
    • b) LEFT JOIN
    • c) OUTER JOIN
    • d) CROSS JOIN
16. What does an Index Nested Loop join imply in a query plan?
    • a) A sequential scan followed by a nested loop
    • b) A join operation that uses an index to speed up the process
    • c) A join where indexes are not used
    • d) A merge join using indexes
17. How can subqueries be optimized to reduce query execution time?
    • a) By using EXISTS instead of IN
    • b) By using indexes on subquery columns
    • c) By avoiding subqueries in the SELECT clause
    • d) All of the above

Index Optimization Techniques

18. What is the primary purpose of indexing in PostgreSQL?
    • a) To reduce data redundancy
    • b) To improve query performance by reducing I/O
    • c) To simplify query structure
    • d) To optimize joins
19. Which type of index is suitable for columns with a low cardinality?
    • a) B-tree index
    • b) Hash index
    • c) GIN index
    • d) GiST index
20. Which of the following is true about the use of multi-column indexes in PostgreSQL?
    • a) Multi-column indexes improve query performance for all queries
    • b) They are only useful for queries that involve all indexed columns
    • c) They are slower than single-column indexes for all queries
    • d) They do not improve query performance at all
21. How can an index be used to optimize a JOIN operation?
    • a) By indexing the join keys
    • b) By indexing all the columns in both tables
    • c) By creating a multi-column index for both tables involved
    • d) Both a and c
22. What does VACUUM ANALYZE do in PostgreSQL?
    • a) Removes unused rows from the table
    • b) Optimizes the index structure
    • c) Reclaims disk space and updates statistics for the planner
    • d) Rebuilds all indexes in the database
23. What is a Bitmap Index Scan?
    • a) A type of index scan used for filtering large datasets
    • b) A sequential scan
    • c) A method for updating indexes in real-time
    • d) A type of join optimization
24. What type of index is most effective for full-text search in PostgreSQL?
    • a) B-tree index
    • b) GIN index
    • c) GiST index
    • d) Hash index

Working with Parallel Queries

25. What is a parallel query in PostgreSQL?
    • a) A query that executes using multiple CPU cores simultaneously
    • b) A query that processes data in parallel across multiple databases
    • c) A query that handles concurrent connections efficiently
    • d) A query that uses multi-threading
26. Which of the following operations can benefit from parallel query execution?
    • a) Aggregations on large datasets
    • b) Sequential scans on large tables
    • c) Joins involving large tables
    • d) All of the above
27. How can you enable parallel query execution in PostgreSQL?
    • a) By setting the max_parallel_workers_per_gather parameter
    • b) By using the PARALLEL keyword in queries
    • c) By indexing all relevant columns
    • d) Parallel query execution is always enabled by default
28. What is the role of the Parallel Seq Scan operation in a query plan?
    • a) It splits the sequential scan across multiple CPU cores
    • b) It accelerates index scans
    • c) It improves the performance of joins
    • d) It is not used in PostgreSQL
29. What can hinder the performance of parallel queries in PostgreSQL?
    • a) Insufficient system resources such as CPU cores
    • b) Poorly optimized queries with non-indexed columns
    • c) Small table sizes
    • d) All of the above
30. Which of the following is an important factor to consider when tuning parallel query performance?
    • a) The number of available CPU cores
    • b) The size of the dataset
    • c) The complexity of the query
    • d) All of the above

Answer Key