PostgreSQL Advanced Indexing Quiz

1. What is the main purpose of an INCLUDE clause in an index definition?

Add non-key columns for index-only scans without sorting

Filter rows during index creation

Specify index storage parameters

Enable parallel index scans

2. CONCURRENTLY allows index creation without blocking writes to the table. True or False?

True

False

3. Which parameter controls the cost threshold for index creation decisions?

Random_page_cost

Work_mem

Maintenance_work_mem

Seq_page_cost

4. A covering index stores all columns needed by a query to enable index-only scans. True or False?

True

False

5. What does VACUUM ANALYZE do for index maintenance?

Removes dead tuples and updates query planner statistics

Rebuilds all indexes

Deletes unused indexes

Compresses index data

6. Which index type is optimized for equality comparisons only?

Hash

B-tree

GiST

BRIN

7. An expression index can index computed values derived from column data. True or False?

True

False

8. What does FILLFACTOR control in an index definition?

Percentage of page space reserved for future updates

Number of index pages allocated

Compression level for index data

Maximum index size limit

9. Which view shows index usage statistics in PostgreSQL?

Pg_stat_user_indexes

Pg_indexes

Pg_index

Pg_class

10. A Hash index supports range queries efficiently. True or False?

True

False

11. Which index type in PostgreSQL is best for range queries and supports ORDER BY optimization?

B-tree

Hash

GiST

BRIN

12. REINDEX rebuilds an index while keeping the table locked. True or False?

True

False

13. Which index type uses a Generalized Search Tree structure for geometric data?

GiST

Hash

BRIN

Bloom

14. What does the term 'index bloat' refer to?

Unused space in an index due to deletions and updates

Too many indexes on one table

Large table size

Slow query execution

15. A composite index on columns (a, b, c) can efficiently support a query filtering on column b only. True or False?

True

False

16. Which command removes an index from a PostgreSQL database?

DROP INDEX

DELETE INDEX

REMOVE INDEX

TRUNCATE INDEX

17. What is the primary advantage of a BRIN index?

Very small size with good performance on large sequential tables

Fastest for all query types

No maintenance required

Supports all data types

18. A partial index filters rows using a WHERE clause during index creation. True or False?

True

False

19. Which index type is designed for full-text search operations?

GIN

Hash

BRIN

Partial

20. What does EXPLAIN ANALYZE reveal that EXPLAIN alone does not?

Actual execution statistics and row counts

Only the query plan cost

Index names only

Table structure information

PostgreSQL Professional Advanced Indexing Quiz

1. What is the main purpose of an INCLUDE clause in an index definition?

2.

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

2. CONCURRENTLY allows index creation without blocking writes to the table. True or False?

3. Which parameter controls the cost threshold for index creation decisions?

4. A covering index stores all columns needed by a query to enable index-only scans. True or False?

5. What does VACUUM ANALYZE do for index maintenance?

6. Which index type is optimized for equality comparisons only?

7. An expression index can index computed values derived from column data. True or False?

8. What does FILLFACTOR control in an index definition?

9. Which view shows index usage statistics in PostgreSQL?

10. A Hash index supports range queries efficiently. True or False?

11. Which index type in PostgreSQL is best for range queries and supports ORDER BY optimization?

12. REINDEX rebuilds an index while keeping the table locked. True or False?

13. Which index type uses a Generalized Search Tree structure for geometric data?

14. What does the term 'index bloat' refer to?

15. A composite index on columns (a, b, c) can efficiently support a query filtering on column b only. True or False?

16. Which command removes an index from a PostgreSQL database?

17. What is the primary advantage of a BRIN index?

18. A partial index filters rows using a WHERE clause during index creation. True or False?

19. Which index type is designed for full-text search operations?

20. What does EXPLAIN ANALYZE reveal that EXPLAIN alone does not?