Indexing & Optimization Flashcards
Index types: B-tree, Hash, GiST (Postgres), Bitmap (Oracle) Index usage in queries EXPLAIN / EXPLAIN ANALYZE (Postgres), Execution Plan (Oracle) Index-only scans Composite and partial indexes Query rewriting and hints Vacuum and analyze in Postgres (32 cards)
What is a B-tree index?
A B-tree index is a balanced tree structure used to quickly locate records with ordered data; it’s the default index type in many databases.
What is a Hash index?
A Hash index maps keys to buckets using a hash function, offering constant-time lookup for equality searches, but no range support.
What is a GiST index in PostgreSQL?
Generalized Search Tree (GiST) is a flexible indexing framework that supports complex data types like geometric or full-text search.
What is a Bitmap index?
A Bitmap index uses bitmaps for each distinct column value and is efficient for low-cardinality columns, especially in Oracle.
When does the database use an index in queries?
An index is used when it helps speed up data access based on the WHERE clause, joins, sorting, or grouping.
What is an EXPLAIN plan?
EXPLAIN shows the planned execution path of a query, including how indexes and joins will be used.
What does EXPLAIN ANALYZE do in PostgreSQL?
It runs the query and provides actual execution statistics, including timing and rows processed.
What is an execution plan in Oracle?
A plan that outlines the steps the optimizer will take to execute a query, viewable using EXPLAIN PLAN.
What is an index-only scan?
A scan where the query is fully answered using the index without touching the table, improving performance.
What is a composite index?
An index on multiple columns; useful when queries filter or sort by a combination of those columns.
What is a partial index?
An index on a subset of rows in a table that meet a specific condition, reducing size and improving performance.
What is query rewriting?
Changing the structure of a query for better performance without altering its output, e.g., replacing subqueries with joins.
What are query hints?
Directives given to the SQL optimizer to influence its decisions, such as which index to use or join method to prefer.
What is VACUUM in PostgreSQL?
VACUUM reclaims storage occupied by dead tuples and updates visibility maps to maintain performance.
What is ANALYZE in PostgreSQL?
ANALYZE collects statistics about tables and indexes to help the query planner make better decisions.
What are the advantages of indexing?
Indexes speed up data retrieval, reduce I/O, and improve query performance significantly.
What are the disadvantages of indexing?
They add overhead to INSERT/UPDATE/DELETE operations and consume additional storage.
What’s a best practice when indexing?
Index columns that appear frequently in WHERE, JOIN, or ORDER BY clauses, and avoid over-indexing.
When should you use a partial index?
When a query targets a subset of rows based on a predictable condition like ‘is_active = true’.
What is a common use case for GiST indexes?
Spatial queries or full-text search in PostgreSQL where standard B-tree indexes aren’t suitable.
How do indexes impact system design?
They improve read performance but introduce complexity in write-heavy or real-time systems.
What’s an architectural implication of over-indexing?
It can degrade write performance and slow down bulk operations or migrations.
What is a performance tradeoff with composite indexes?
They help multi-column queries but only work efficiently when leading columns are used.
How can index-only scans improve performance?
By eliminating table lookups, reducing I/O and memory usage for large datasets.
