PostgreSQL Query Performance Tuning Quiz

1. What is a covering index in PostgreSQL?

An index that includes all columns needed for a query

An index that covers multiple tables

An index that is always kept in memory

An index that automatically updates statistics

2. Which command is used to update statistics for the query planner?

VACUUM

ANALYZE

REINDEX

CLUSTER

3. What is the benefit of partial indexes in PostgreSQL?

They reduce index size by indexing only a subset of rows

They automatically parallelize queries

They encrypt sensitive data

They improve write performance

4. Which setting controls the minimum duration for logging slow queries?

Log_statement

Log_duration

Log_min_duration_statement

Log_statement_timeout

5. What does the rows estimate in EXPLAIN output indicate?

The actual number of rows returned

The estimated number of rows the planner expects

The number of rows processed per second

The total rows in the table

6. Which PostgreSQL view provides information about index usage?

Pg_indexes

Pg_stat_user_indexes

Pg_index

Pg_constraint

7. What is the purpose of setting effective_cache_size in PostgreSQL?

To limit the actual amount of memory used

To help the query planner estimate available cache

To compress cached data

To encrypt cached queries

8. Which index type is best for range queries like BETWEEN?

Hash

GIN

B-tree

BRIN

9. What does AUTOVACUUM do in PostgreSQL?

Automatically deletes unused databases

Automatically reclaims space and updates statistics

Automatically backs up the database

Automatically creates indexes

10. Which command shows the current query plans without executing the query?

EXPLAIN ANALYZE

EXPLAIN

ANALYZE

PLAN

11. What does EXPLAIN ANALYZE do in PostgreSQL?

Shows the query plan without executing the query

Executes the query and displays actual runtime statistics

Analyzes table statistics only

Displays the query in human-readable format

12. Which PostgreSQL feature allows you to capture slow queries automatically?

Log_min_duration_statement

Log_min_error_statement

Log_statement

Log_checkpoints

13. What does the cost value in EXPLAIN output represent?

The actual execution time in milliseconds

A relative estimate of query execution cost

The memory consumed by the query

The number of disk I/O operations

14. Which join type typically performs best when one table is significantly smaller?

Nested loop join

Hash join

Merge join

Cross join

15. What is the purpose of VACUUM in PostgreSQL?

To delete all data from a table

To reclaim space and update table statistics

To compress the database file

To create backups of the database

16. Which index type is optimized for full-text search in PostgreSQL?

B-tree

Hash

GIN

BRIN

17. What does a sequential scan indicate in an EXPLAIN plan?

The query is reading the entire table row by row

The query is using multiple indexes

The query is cached in memory

The query is using parallel processing

18. Which PostgreSQL setting controls the maximum amount of memory for sorting operations?

Shared_buffers

Work_mem

Maintenance_work_mem

Effective_cache_size

19. What is the primary purpose of query statistics in pg_stat_statements?

To backup database queries

To track query execution time and resource usage

To encrypt sensitive queries

To compress query logs

20. Which index type is best for equality searches in PostgreSQL?

B-tree

Hash

GiST

GIN

PostgreSQL Professional Query Performance Tuning Quiz

1. What is a covering index in PostgreSQL?

2.

What first name or nickname would you like us to use?

2. Which command is used to update statistics for the query planner?

3. What is the benefit of partial indexes in PostgreSQL?

4. Which setting controls the minimum duration for logging slow queries?

5. What does the rows estimate in EXPLAIN output indicate?

6. Which PostgreSQL view provides information about index usage?

7. What is the purpose of setting effective_cache_size in PostgreSQL?

8. Which index type is best for range queries like BETWEEN?

9. What does AUTOVACUUM do in PostgreSQL?

10. Which command shows the current query plans without executing the query?

11. What does EXPLAIN ANALYZE do in PostgreSQL?

12. Which PostgreSQL feature allows you to capture slow queries automatically?

13. What does the cost value in EXPLAIN output represent?

14. Which join type typically performs best when one table is significantly smaller?

15. What is the purpose of VACUUM in PostgreSQL?

16. Which index type is optimized for full-text search in PostgreSQL?

17. What does a sequential scan indicate in an EXPLAIN plan?

18. Which PostgreSQL setting controls the maximum amount of memory for sorting operations?

19. What is the primary purpose of query statistics in pg_stat_statements?

20. Which index type is best for equality searches in PostgreSQL?