B-tree index (default) behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a B-tree index in PostgreSQL?

A B-tree index is the default type of index in PostgreSQL. It organizes data in a balanced tree structure to allow fast searching, insertion, and deletion of rows based on the indexed column values.

beginner

How does a B-tree index improve query performance?

It speeds up queries by quickly locating rows with specific values or ranges without scanning the entire table, using a balanced tree to minimize the number of disk reads.

intermediate

Which types of queries benefit most from a B-tree index?

Queries that use equality (=), range comparisons (<, <=, >, >=), and ORDER BY clauses on the indexed columns benefit most from B-tree indexes.

intermediate

Can a B-tree index be used for pattern matching with LIKE in PostgreSQL?

Yes, but only when the pattern does not start with a wildcard (e.g., LIKE 'abc%'). If the pattern starts with a wildcard (e.g., LIKE '%abc'), the B-tree index cannot be used efficiently.

beginner

What happens to a B-tree index when data is inserted or deleted?

The B-tree index automatically updates to reflect the changes, maintaining its balanced structure to ensure fast access.

What is the default index type in PostgreSQL?

A B-tree

B Hash

C GIN

D GiST

PostgreSQL uses B-tree as the default index type because it supports a wide range of queries efficiently.

Which query condition can a B-tree index NOT efficiently support?

A WHERE column = 5

B WHERE column LIKE '%abc'

C WHERE column > 10

D WHERE column LIKE 'abc%'

B-tree indexes cannot efficiently support LIKE patterns starting with a wildcard such as '%abc'.

How does a B-tree index store data internally?

A In a flat file

B In a linked list

C In a hash table

D In a balanced tree structure

B-tree indexes store data in a balanced tree structure to allow fast searching and updates.

Which of these operations causes a B-tree index to update?

A SELECT query

B CREATE TABLE

C INSERT

D DROP DATABASE

Inserting data updates the B-tree index to include the new row's indexed values.

What kind of queries benefit from B-tree indexes?

A Range queries

B Full text search

C JSON queries

D Spatial queries

B-tree indexes are optimized for range queries and equality comparisons.

Explain how a B-tree index works and why it is useful in PostgreSQL.

Think about how a tree helps find things quickly without checking every item.

Describe when a B-tree index can and cannot be used with the LIKE operator in PostgreSQL.

Consider how the pattern starts and how the index searches.