MCQs on Advanced Indexing Techniques | PostgreSQL Database
Introduction to Advanced Indexing Techniques in PostgreSQL:
Master advanced PostgreSQL indexing strategies like multicolumn, partial, and unique indexes while exploring indexing types such as B-Tree, Hash, GiST, GIN, and BRIN for optimized database performance.
Multicolumn Indexes
- What is the primary purpose of a multicolumn index in PostgreSQL?
a) To manage foreign key constraints
b) To optimize queries filtering by multiple columns
c) To enable table partitioning
d) To manage unique constraints - How many columns can a multicolumn index include in PostgreSQL?
a) Up to 2
b) Up to 16
c) Up to 32
d) Unlimited - When is a multicolumn index most effective?
a) When only one column is queried
b) When columns in the WHERE clause match the index order
c) When the table has a primary key
d) When the table contains only numeric data - What is the key consideration when choosing column order in a multicolumn index?
a) Alphabetical order of column names
b) The order of column usage in queries
c) Data type of the columns
d) The number of rows in the table - Can a multicolumn index improve performance if the query uses only some of the indexed columns?
a) No, all indexed columns must be used
b) Yes, but only if the leftmost columns are queried
c) Yes, regardless of column order
d) No, it is only for full column matches
Partial Indexes
- What is a partial index in PostgreSQL?
a) An index created for temporary tables
b) An index that covers a subset of table rows
c) A multicolumn index with unique constraints
d) An index created using hashed data - Which clause is essential for defining a partial index?
a) USING
b) WHERE
c) GROUP BY
d) HAVING - When is a partial index most useful?
a) For tables with frequently updated data
b) For filtering rows that meet specific conditions
c) For queries with JOIN operations
d) For managing primary keys - What is a key benefit of using partial indexes?
a) Improved performance by indexing only relevant rows
b) Automatic updating during data insertion
c) Ability to store additional row data
d) Reduced query complexity - Can a partial index be used without a WHERE clause in queries?
a) Yes, it applies to all rows by default
b) No, it requires the WHERE condition in the query
c) Yes, but only for multicolumn queries
d) No, unless it is created with a unique constraint
Unique Indexes
- What is the purpose of a unique index in PostgreSQL?
a) To optimize joins between tables
b) To enforce uniqueness of column values
c) To partition table data automatically
d) To improve query performance for sequential scans - Which PostgreSQL constraint automatically creates a unique index?
a) CHECK
b) FOREIGN KEY
c) PRIMARY KEY
d) EXCLUDE - Can a unique index be created on multiple columns?
a) No, unique indexes are for single columns only
b) Yes, by defining a multicolumn unique index
c) No, they conflict with multicolumn indexes
d) Yes, but only for foreign keys - What happens if a duplicate value is inserted into a column with a unique index?
a) The value is inserted, ignoring the index
b) The database raises an error
c) The index is automatically updated
d) The query execution slows down - Which command is used to create a unique index?
a) CREATE INDEX UNIQUE
b) CREATE UNIQUE INDEX
c) CREATE CONSTRAINT UNIQUE INDEX
d) CREATE UNIQUE INDEX ON TABLE
Understanding B-Tree, Hash, GiST, GIN, and BRIN Indexes
- Which is the default index type in PostgreSQL?
a) Hash
b) GIN
c) B-Tree
d) BRIN - For which type of queries is a B-Tree index most effective?
a) Equality and range queries
b) Text search queries
c) Geospatial queries
d) Large sequential scans - What is a unique feature of a Hash index in PostgreSQL?
a) It supports geospatial data
b) It works only for equality comparisons
c) It can handle range queries
d) It automatically updates statistics - Which index type is best suited for full-text search?
a) BRIN
b) Hash
c) GIN
d) B-Tree - What does a GiST index specialize in?
a) Equality comparisons
b) Text search
c) Complex data types like geometries
d) Optimizing sequential scans - Which type of index is optimized for very large, sequentially ordered datasets?
a) GIN
b) Hash
c) BRIN
d) B-Tree - How does a GIN index differ from a B-Tree index?
a) GIN indexes are faster for range queries
b) GIN indexes are designed for multicolumn indexing
c) GIN indexes are ideal for full-text search and arrays
d) GIN indexes require fewer resources - Which type of data structure is used in a B-Tree index?
a) Hash tables
b) Binary tree
c) Balanced tree
d) Sequential lists - For what type of data is a BRIN index most effective?
a) Text-based queries
b) Geospatial data
c) Large, sequential data blocks
d) Small datasets - Which index type is generally not recommended for small datasets?
a) B-Tree
b) Hash
c) BRIN
d) GIN
Advanced Indexing Concepts
- What is the primary limitation of Hash indexes in PostgreSQL?
a) They are limited to unique constraints
b) They do not support range queries
c) They cannot be used on multicolumns
d) They are slower than B-Tree indexes - Which command allows you to check the size of an index in PostgreSQL?
a) CHECK INDEX SIZE
b) pg_index_size() function
c) EXPLAIN ANALYZE INDEX SIZE
d) ANALYZE INDEX SIZE - What happens if an index type does not match the query requirements?
a) PostgreSQL uses the index regardless
b) PostgreSQL raises a query error
c) PostgreSQL performs a sequential scan
d) PostgreSQL creates a new index automatically - Why might you choose a BRIN index over a B-Tree index?
a) BRIN is faster for equality comparisons
b) BRIN uses less disk space for large datasets
c) BRIN supports multicolumn constraints better
d) BRIN automatically updates during queries - How does PostgreSQL determine the best index for a query?
a) Based on column names
b) By analyzing query structure and data distribution
c) By testing all indexes in the table
d) Using the first index created
Answer Key
| Qno | Answer |
|---|---|
| 1 | b) To optimize queries filtering by multiple columns |
| 2 | c) Up to 32 |
| 3 | b) When columns in the WHERE clause match the index order |
| 4 | b) The order of column usage in queries |
| 5 | b) Yes, but only if the leftmost columns are queried |
| 6 | b) An index that covers a subset of table rows |
| 7 | b) WHERE |
| 8 | b) For filtering rows that meet specific conditions |
| 9 | a) Improved performance by indexing only relevant rows |
| 10 | b) No, it requires the WHERE condition in the query |
| 11 | b) To enforce uniqueness of column values |
| 12 | c) PRIMARY KEY |
| 13 | b) Yes, by defining a multicolumn unique index |
| 14 | b) The database raises an error |
| 15 | b) CREATE UNIQUE INDEX |
| 16 | c) B-Tree |
| 17 | a) Equality and range queries |
| 18 | b) It works only for equality comparisons |
| 19 | c) GIN |
| 20 | c) Complex data types like geometries |
| 21 | c) BRIN |
| 22 | c) GIN indexes are ideal for full-text search and arrays |
| 23 | c) Balanced tree |
| 24 | c) Large, sequential data blocks |
| 25 | c) BRIN |
| 26 | b) They do not support range queries |
| 27 | b) pg_index_size() function |
| 28 | c) PostgreSQL performs a sequential scan |
| 29 | b) BRIN uses less disk space for large datasets |
| 30 | b) By analyzing query structure and data distribution |