PostgreSQL Indexing Strategies Practice Exam

Question 1 PostgreSQL Indexing Strategies

What is a tablespace in index context?

A node that fetches heap tuples in the order requested by an index bitmap, applying the original predicate as a recheck when the bitmap is lossy. A storage parameter that leaves free space on each index (or heap) page, leaving room for in-place updates and reducing page splits. A location on disk where index (or table) files are stored; you can put indexes on fast storage (e.g., SSD) via CREATE INDEX ... TABLESPACE fast_ssd;. Query pg_stat_user_indexes; the idx_scan counter shows cumulative index scans since the last reset.

Question 2 PostgreSQL Indexing Strategies

How do you force the planner to use (or not use) an index?

You cannot directly, but you can disable plan types with SET enable_indexscan = off; or disable specific joins with enable_* GUCs for testing only. More memory allows non-lossy bitmaps, which skip the recheck and can be faster. A system column giving the physical location (block, item) of a row in the heap; it changes on UPDATE, so it is not useful as a long-term key. A plan node that builds a bitmap of pages matching an index condition, often combined (BitmapAnd/BitmapOr) with results from several indexes, then visited by a Bitmap Heap Scan.

Question 3 PostgreSQL Indexing Strategies

What is a `Merge Join`?

A join that merges two pre-sorted inputs; often uses B-tree indexes to provide the sorted inputs cheaply. Null columns are stored in the index key with a bit in the entry's null bitmap; the index is not "skipped" for NULL values. On very large tables with data that is naturally correlated with physical location — e.g., append-only time-series with monotonically increasing timestamps. Overlapping indexes; generally you keep the one that best matches query patterns and drop the others to save write cost.

Question 4 PostgreSQL Indexing Strategies

What is a Bitmap Heap Scan?

An extension index that supports many equality predicates across multiple columns in a compact form, useful for star-schema fact tables where any combination of columns may be queried. The number of heap pages summarized in each BRIN block range. Smaller values = more accurate index, larger index size; larger values = more compact but coarser. A join that merges two pre-sorted inputs; often uses B-tree indexes to provide the sorted inputs cheaply. A node that fetches heap tuples in the order requested by an index bitmap, applying the original predicate as a recheck when the bitmap is lossy.

Question 5 PostgreSQL Indexing Strategies

What is BRIN?

About 1/3 of a page (≈ 2.7 KB on 8 KB pages), because the upper tree pages must fit many entries; the planner logs "index row size X exceeds btree version 4 maximum" otherwise. indisready means the index is ready for updates; indisvalid means it is ready to be used by queries. A CONCURRENTLY-built index becomes ready first, then valid. A simple index type that supports only equality (=) comparisons, using a hash of the key. Block Range INdex; stores a summary of values (min/max, optionally other aggregates) for each range of heap pages, making it tiny in size.

Question 6 PostgreSQL Indexing Strategies

What is the size of a B-tree entry's null bitmap?

CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at) WITH (pages_per_range = 32); A system column giving the physical location (block, item) of a row in the heap; it changes on UPDATE, so it is not useful as a long-term key. Null columns are stored in the index key with a bit in the entry's null bitmap; the index is not "skipped" for NULL values. An index built over a subset of rows defined by a WHERE clause, useful for sparse predicates like WHERE deleted_at IS NULL.

Question 7 PostgreSQL Indexing Strategies

How do you create a UNIQUE index on a partial set?

indisready means the index is ready for updates; indisvalid means it is ready to be used by queries. A CONCURRENTLY-built index becomes ready first, then valid. CREATE UNIQUE INDEX idx ON t (email) WHERE deleted_at IS NULL; — duplicates can exist among the excluded rows but not among the indexed ones. Hash indexes do not support range queries or ordering, and historically they were not WAL-logged (fixed in PG 10). EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Question 8 PostgreSQL Indexing Strategies

How do you check whether an index is being used?

To avoid the write cost; the DBA must add them if the workload includes heavy ON DELETE CASCADE or joins on the FK. Query pg_stat_user_indexes; the idx_scan counter shows cumulative index scans since the last reset. A hint to the planner about the OS and shared buffer cache size; affects whether index scans vs. seq scans look attractive; does not allocate memory. Enables storing pending list updates in an unordered list for faster inserts at the cost of slower lookups until VACUUM flushes; default on.

Question 9 PostgreSQL Indexing Strategies

What is a `ctid`?

An operator class that lets B-tree indexes support LIKE and ILIKE with a non-C-collation locale (e.g., C collation cannot use a regular B-tree for prefix LIKE). CREATE INDEX idx_gin ON docs USING GIN (data jsonb_path_ops); A system column giving the physical location (block, item) of a row in the heap; it changes on UPDATE, so it is not useful as a long-term key. SET max_parallel_maintenance_workers = 4; before CREATE INDEX on a large table.

Question 10 PostgreSQL Indexing Strategies

What index types does PostgreSQL ship with for general use?

B-tree, Hash, GIN, GiST, SP-GiST, BRIN, and the extension-provided Bloom (and others like zombodb, rum, pg_trgm). A correlation between two columns that helps the planner when both appear in WHERE or one is a GROUP BY key. It removes deleted tuples from the pending list, ensuring the index stays compact and queries remain fast. Hash indexes do not support range queries or ordering, and historically they were not WAL-logged (fixed in PG 10).

Question 11 PostgreSQL Indexing Strategies

How do you enable the Bloom extension?

Generalized Inverted Index; designed for values that contain many elements of another type, like arrays, JSONB, full-text tsvector, and trigrams. CREATE EXTENSION bloom; Out-of-date row counts or histograms cause wrong cardinality estimates, which can lead to bad index choices, bad join orders, and bad memory sizing. auto_explain.log_nested_statements and auto_explain.sample_rate.

Question 12 PostgreSQL Indexing Strategies

What is an expression index (functional index)?

On tables with random inserts/updates — the correlation between physical and logical order breaks, summaries become wide, and selectivity drops. Block Range INdex; stores a summary of values (min/max, optionally other aggregates) for each range of heap pages, making it tiny in size. An index built on the result of an expression, e.g. CREATE INDEX idx ON t (lower(email));. The planner estimates a BitmapOr/And of single-column indexes is cheaper, or it mis-estimates selectivity; ANALYZE and accurate pg_stats often help.

Question 13 PostgreSQL Indexing Strategies

What is a clustered index in PostgreSQL?

A contrib module that logs plans of slow queries automatically using log_min_duration. It supports only the containment operator @> and not the existence operators ?, ?|, ?&. A correlation between two columns that helps the planner when both appear in WHERE or one is a GROUP BY key. PostgreSQL does not have true clustered indexes (unlike MySQL InnoDB); the heap is unsorted. The CLUSTER command reorders the heap physically to match an index once, but is not maintained on subsequent inserts.

Question 14 PostgreSQL Indexing Strategies

What is a Bloom filter index (extension `bloom`)?

When a single column bitmap exceeds work_mem, PostgreSQL switches to a lossy representation with one bit per page; selectivity drops and recheck is required. An extension index that supports many equality predicates across multiple columns in a compact form, useful for star-schema fact tables where any combination of columns may be queried. Space-Partitioned GiST; for non-balanced data such as quadtrees, kd-trees, and radix trees; useful for phone numbers, IP addresses (with inet), and geometric point data. It removes deleted tuples from the pending list, ensuring the index stays compact and queries remain fast.

Question 15 PostgreSQL Indexing Strategies

How do you make an index cover extra columns without adding them to the key?

CREATE INDEX idx ON t (a) INCLUDE (b, c); — INCLUDE columns are stored in leaf pages only and are not part of the key, so uniqueness does not apply to them. An operator class that lets B-tree indexes support LIKE and ILIKE with a non-C-collation locale (e.g., C collation cannot use a regular B-tree for prefix LIKE). btree. Because the keys are sorted lexicographically; the index can only be used for predicates on a leading prefix of the columns.

Question 16 PostgreSQL Indexing Strategies

Can you index `NULL` values?

Yes — B-tree indexes include NULL values, and you can filter IS NULL/IS NOT NULL with a B-tree. CREATE INDEX idx ON docs USING GIN (data); — Postgres picks the default opclass for the type. SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; An operator class that lets B-tree indexes support LIKE and ILIKE with a non-C-collation locale (e.g., C collation cannot use a regular B-tree for prefix LIKE).

Question 17 PostgreSQL Indexing Strategies

What is a multicolumn index and a single-column index on the same table called together?

Overlapping indexes; generally you keep the one that best matches query patterns and drop the others to save write cost. CREATE UNIQUE INDEX idx_users_email ON users (email); — Postgres automatically creates one when you declare UNIQUE or PRIMARY KEY. 90. CREATE INDEX idx ON t (col) WITH (fillfactor = 70);

Question 18 PostgreSQL Indexing Strategies

What is a key advantage of Hash indexes in PostgreSQL 10+?

An extension that adds trigram (3-character substring) similarity functions and GiST/GIN operator classes for fast LIKE, ILIKE, and % (%> similarity) searches on text. They are write-ahead-logged (crash-safe) and are sometimes picked by the planner because they can be slightly smaller than equivalent B-trees for equality-only workloads. A join that, for each row of the outer side, probes the inner side using an index; preferred when the outer side is small or the inner side has a selective index. A combination of NOT NULL plus a UNIQUE index; Postgres uses the underlying B-tree index for lookups and joins.

Question 19 PostgreSQL Indexing Strategies

How do you index `tsvector` for full-text search?

It is also the mechanism behind ON CONFLICT (col) DO UPDATE upserts, enabling INSERT ... ON CONFLICT to find conflicts efficiently. Often a "low-cardinality partial index" — useful for hot rows like WHERE status = 'pending' when most rows are status = 'done'. CREATE INDEX idx ON articles USING GIN (to_tsvector('english', body)); Equality on a; equality on a + range on b; equality on a, b, c in that order. It cannot serve a query that filters only on b or c.

Question 20 PostgreSQL Indexing Strategies

What is an `INCLUDE` clause's main benefit?

It allows index-only scans by storing extra columns in leaf pages without enlarging the search key, so they do not affect ordering or uniqueness. A hint to the planner about the OS and shared buffer cache size; affects whether index scans vs. seq scans look attractive; does not allocate memory. A planner parameter that estimates the cost of reading a heap page sequentially; defaults to 1.0. About 1/3 of a page (≈ 2.7 KB on 8 KB pages), because the upper tree pages must fit many entries; the planner logs "index row size X exceeds btree version 4 maximum" otherwise.

Question 21 PostgreSQL Indexing Strategies

What is a covering index?

btree. An index that contains all columns referenced by a query so it can be served by an index-only scan. Created with INCLUDE. The number of heap pages summarized in each BRIN block range. Smaller values = more accurate index, larger index size; larger values = more compact but coarser. A node that fetches heap tuples in the order requested by an index bitmap, applying the original predicate as a recheck when the bitmap is lossy.

Question 22 PostgreSQL Indexing Strategies

How do you safely add an index on a large production table?

A normalized lexeme representation of text used for full-text search; to_tsvector('english', text) produces it. B-tree, Hash, GIN, GiST, SP-GiST, BRIN, and the extension-provided Bloom (and others like zombodb, rum, pg_trgm). Use CREATE INDEX CONCURRENTLY to avoid blocking writes; monitor for the INVALID state and retry or drop the failed index. A combination of NOT NULL plus a UNIQUE index; Postgres uses the underlying B-tree index for lookups and joins.

Question 23 PostgreSQL Indexing Strategies

What is `effective_cache_size`?

Memory used for VACUUM, index creation, and ANALYZE; larger values speed up CREATE INDEX by reducing sort spill. jsonb_ops indexes every key and value, supports ?, ?|, ?&, and containment @>. jsonb_path_ops indexes only paths to values, supports only @>, but is smaller and faster for containment queries. To avoid the write cost; the DBA must add them if the workload includes heavy ON DELETE CASCADE or joins on the FK. A hint to the planner about the OS and shared buffer cache size; affects whether index scans vs. seq scans look attractive; does not allocate memory.

Question 24 PostgreSQL Indexing Strategies

How do you rebuild a bloated index?

Every INSERT/UPDATE/DELETE that affects indexed columns must update each relevant index, causing additional WAL volume and random I/O. Null columns are stored in the index key with a bit in the entry's null bitmap; the index is not "skipped" for NULL values. A simple index type that supports only equality (=) comparisons, using a hash of the key. REINDEX INDEX CONCURRENTLY idx_name; (the CONCURRENTLY option avoids an ACCESS EXCLUSIVE lock).

Question 25 PostgreSQL Indexing Strategies

What is an index advisor?

Tools like pg_qualstats and pg_hint_plan, or EXPLAIN analysis, can suggest missing indexes from a workload. They are write-ahead-logged (crash-safe) and are sometimes picked by the planner because they can be slightly smaller than equivalent B-trees for equality-only workloads. An extension that adds trigram (3-character substring) similarity functions and GiST/GIN operator classes for fast LIKE, ILIKE, and % (%> similarity) searches on text. The default index type, a balanced tree that keeps keys sorted and supports equality and range predicates, plus IS NULL, IS NOT NULL, and ORDER BY traversal in index order.

Question 26 PostgreSQL Indexing Strategies

What is a `HASH` index's main weakness vs. B-tree?

A planner parameter that estimates the cost of reading a heap page sequentially; defaults to 1.0. Hash indexes do not support range queries or ordering, and historically they were not WAL-logged (fixed in PG 10). Generalized Inverted Index; designed for values that contain many elements of another type, like arrays, JSONB, full-text tsvector, and trigrams. Overlapping indexes; generally you keep the one that best matches query patterns and drop the others to save write cost.

Question 27 PostgreSQL Indexing Strategies

When is BRIN a poor choice?

When the query uses the same expression in WHERE, JOIN, or ORDER BY — the index is invisible to the planner otherwise. SELECT indisvalid, indisready FROM pg_index WHERE indexrelid = 'idx_name'::regclass; For nearest-neighbor (<->) searches, range overlap (&&), and data types that support only GiST (e.g., box, circle, tsquery in some forms). On tables with random inserts/updates — the correlation between physical and logical order breaks, summaries become wide, and selectivity drops.

Question 28 PostgreSQL Indexing Strategies

What is a `Hash Join`?

CREATE INDEX idx ON articles USING GIN (to_tsvector('english', body)); An operator class that lets B-tree indexes support LIKE and ILIKE with a non-C-collation locale (e.g., C collation cannot use a regular B-tree for prefix LIKE). The planner estimates a BitmapOr/And of single-column indexes is cheaper, or it mis-estimates selectivity; ANALYZE and accurate pg_stats often help. A join that builds a hash on the inner side and probes with the outer; preferred for large unsorted equi-joins; does not use an index directly.

Question 29 PostgreSQL Indexing Strategies

How do you create a B-tree index on a single column?

CREATE INDEX idx_users_email ON users (email); CREATE INDEX idx ON docs USING GIN (data); — Postgres picks the default opclass for the type. REINDEX INDEX CONCURRENTLY idx_name; (the CONCURRENTLY option avoids an ACCESS EXCLUSIVE lock). The bitmap was lossy (one bit per page) and PostgreSQL must re-evaluate the predicate against the heap tuples fetched.

Question 30 PostgreSQL Indexing Strategies

What is a functional dependency in `pg_stats`?

CREATE INDEX idx_gin ON docs USING GIN (data jsonb_path_ops); Generalized Inverted Index; designed for values that contain many elements of another type, like arrays, JSONB, full-text tsvector, and trigrams. A correlation between two columns that helps the planner when both appear in WHERE or one is a GROUP BY key. It is also the mechanism behind ON CONFLICT (col) DO UPDATE upserts, enabling INSERT ... ON CONFLICT to find conflicts efficiently.

Question 31 PostgreSQL Indexing Strategies

What index supports `ILIKE 'foo%'` efficiently?

Tools like pg_qualstats and pg_hint_plan, or EXPLAIN analysis, can suggest missing indexes from a workload. indisready means the index is ready for updates; indisvalid means it is ready to be used by queries. A CONCURRENTLY-built index becomes ready first, then valid. About 1/3 of a page (≈ 2.7 KB on 8 KB pages), because the upper tree pages must fit many entries; the planner logs "index row size X exceeds btree version 4 maximum" otherwise. A B-tree on a text_pattern_ops or bpchar_pattern_ops opclass, or a GIN with gin_trgm_ops for the ILIKE '%foo%' pattern.

Question 32 PostgreSQL Indexing Strategies

How do you disable fastupdate for a GIN index?

B-tree, Hash, GIN, GiST, SP-GiST, BRIN, and the extension-provided Bloom (and others like zombodb, rum, pg_trgm). Use CREATE INDEX CONCURRENTLY to avoid blocking writes; monitor for the INVALID state and retry or drop the failed index. The planner estimates a BitmapOr/And of single-column indexes is cheaper, or it mis-estimates selectivity; ANALYZE and accurate pg_stats often help. ALTER INDEX idx SET (fastupdate = off);

Question 33 PostgreSQL Indexing Strategies

How do you move an existing index to another tablespace?

An operator class that lets B-tree indexes support LIKE and ILIKE with a non-C-collation locale (e.g., C collation cannot use a regular B-tree for prefix LIKE). 90. ALTER INDEX idx SET TABLESPACE fast_ssd; Postgres can build a B-tree index using multiple background workers since version 11, controlled by max_parallel_maintenance_workers.

Question 34 PostgreSQL Indexing Strategies

How many columns can a B-tree index have?

A join that, for each row of the outer side, probes the inner side using an index; preferred when the outer side is small or the inner side has a selective index. Up to 32 columns by default; the limit is controlled by the compile-time constant INDEX_MAX_KEYS. About 1/3 of a page (≈ 2.7 KB on 8 KB pages), because the upper tree pages must fit many entries; the planner logs "index row size X exceeds btree version 4 maximum" otherwise. A combination of NOT NULL plus a UNIQUE index; Postgres uses the underlying B-tree index for lookups and joins.

Question 35 PostgreSQL Indexing Strategies

What is `VACUUM`'s role for GIN indexes?

It removes deleted tuples from the pending list, ensuring the index stays compact and queries remain fast. An index that contains all columns referenced by a query so it can be served by an index-only scan. Created with INCLUDE. Postgres can build a B-tree index using multiple background workers since version 11, controlled by max_parallel_maintenance_workers. About 1/3 of a page (≈ 2.7 KB on 8 KB pages), because the upper tree pages must fit many entries; the planner logs "index row size X exceeds btree version 4 maximum" otherwise.

Question 36 PostgreSQL Indexing Strategies

What is a lossy bitmap?

Enables storing pending list updates in an unordered list for faster inserts at the cost of slower lookups until VACUUM flushes; default on. When a single column bitmap exceeds work_mem, PostgreSQL switches to a lossy representation with one bit per page; selectivity drops and recheck is required. Generalized Inverted Index; designed for values that contain many elements of another type, like arrays, JSONB, full-text tsvector, and trigrams. EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

Question 37 PostgreSQL Indexing Strategies

What is SP-GiST?

ALTER INDEX idx SET TABLESPACE fast_ssd; CREATE INDEX idx ON t (col) WITH (fillfactor = 70); Space-Partitioned GiST; for non-balanced data such as quadtrees, kd-trees, and radix trees; useful for phone numbers, IP addresses (with inet), and geometric point data. A system column giving the physical location (block, item) of a row in the heap; it changes on UPDATE, so it is not useful as a long-term key.

Question 38 PostgreSQL Indexing Strategies

How does increasing `work_mem` help bitmap plans?

Out-of-date row counts or histograms cause wrong cardinality estimates, which can lead to bad index choices, bad join orders, and bad memory sizing. More memory allows non-lossy bitmaps, which skip the recheck and can be faster. Every INSERT/UPDATE/DELETE that affects indexed columns must update each relevant index, causing additional WAL volume and random I/O. Postgres looks up the row via the specified unique index (or primary key) and skips inserting if a conflict is found; without a unique index, the command cannot be used.

Question 39 PostgreSQL Indexing Strategies

What is TOAST?

A simple index type that supports only equality (=) comparisons, using a hash of the key. A plan where the planner reads everything it needs from the index itself (no heap fetch) because the visibility map shows the page is all-visible and all required columns are in the index. The default index type, a balanced tree that keeps keys sorted and supports equality and range predicates, plus IS NULL, IS NOT NULL, and ORDER BY traversal in index order. The Oversized Attribute Storage Technique; long field values are stored out-of-line in a separate TOAST table, and indexed keys are compressed before insertion into the index.

Question 40 PostgreSQL Indexing Strategies

How can you find unused indexes?

Up to 32 columns by default; the limit is controlled by the compile-time constant INDEX_MAX_KEYS. SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; Indexes built with CONCURRENTLY take roughly 2-3x longer and can fail, leaving an INVALID index that must be dropped or recreated. CREATE INDEX idx ON articles USING GIN (title gin_trgm_ops);

Question 41 PostgreSQL Indexing Strategies

When would you use GiST over GIN?

btree. The planner estimates a BitmapOr/And of single-column indexes is cheaper, or it mis-estimates selectivity; ANALYZE and accurate pg_stats often help. For nearest-neighbor (<->) searches, range overlap (&&), and data types that support only GiST (e.g., box, circle, tsquery in some forms). B-tree entries must fit in a single page (8 KB by default) after TOAST compression; very long values are not indexable as keys but can appear in INCLUDE.

Question 42 PostgreSQL Indexing Strategies

How do you inspect a query plan with a focus on indexes?

CREATE INDEX idx ON articles USING GIN (to_tsvector('english', body)); EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...; A plan node that builds a bitmap of pages matching an index condition, often combined (BitmapAnd/BitmapOr) with results from several indexes, then visited by a Bitmap Heap Scan. EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Question 43 PostgreSQL Indexing Strategies

What is the `CONCURRENTLY` option's cost?

A secondary data structure that lets the planner find rows matching a predicate without scanning the whole table, at the cost of extra storage and write overhead. An extension that adds trigram (3-character substring) similarity functions and GiST/GIN operator classes for fast LIKE, ILIKE, and % (%> similarity) searches on text. Indexes built with CONCURRENTLY take roughly 2-3x longer and can fail, leaving an INVALID index that must be dropped or recreated. The planner estimates a BitmapOr/And of single-column indexes is cheaper, or it mis-estimates selectivity; ANALYZE and accurate pg_stats often help.

Question 44 PostgreSQL Indexing Strategies

What is the visibility map?

An extension index that supports many equality predicates across multiple columns in a compact form, useful for star-schema fact tables where any combination of columns may be queried. A bitmap that marks heap pages whose tuples are visible to all current and future transactions, enabling index-only scans and speeding up vacuum. Indexes built with CONCURRENTLY take roughly 2-3x longer and can fail, leaving an INVALID index that must be dropped or recreated. A contrib module that logs plans of slow queries automatically using log_min_duration.

Question 45 PostgreSQL Indexing Strategies

What is `random_page_cost`?

A planner parameter that estimates the cost of an index scan; lowering it (e.g., to 1.1) makes the planner favor indexes on SSD storage. Up to 32 columns by default; the limit is controlled by the compile-time constant INDEX_MAX_KEYS. jsonb_ops indexes every key and value, supports ?, ?|, ?&, and containment @>. jsonb_path_ops indexes only paths to values, supports only @>, but is smaller and faster for containment queries. Plain REINDEX takes an ACCESS EXCLUSIVE lock and is faster; REINDEX CONCURRENTLY rebuilds the index in place without blocking writes (Postgres 12+).

Question 46 PostgreSQL Indexing Strategies

How do you create a GIN index on a JSONB column?

jsonb_ops indexes every key and value, supports ?, ?|, ?&, and containment @>. jsonb_path_ops indexes only paths to values, supports only @>, but is smaller and faster for containment queries. REINDEX INDEX CONCURRENTLY idx_name; (the CONCURRENTLY option avoids an ACCESS EXCLUSIVE lock). CREATE INDEX idx_gin ON docs USING GIN (data jsonb_path_ops); For nearest-neighbor (<->) searches, range overlap (&&), and data types that support only GiST (e.g., box, circle, tsquery in some forms).

Question 47 PostgreSQL Indexing Strategies

What does `CREATE INDEX CONCURRENTLY` do?

An extension index that supports many equality predicates across multiple columns in a compact form, useful for star-schema fact tables where any combination of columns may be queried. Builds the index without locking the table against writes; takes longer and can leave an invalid index if it fails, which must be cleaned up manually. Enables storing pending list updates in an unordered list for faster inserts at the cost of slower lookups until VACUUM flushes; default on. Query pg_stat_user_indexes; the idx_scan counter shows cumulative index scans since the last reset.

Question 48 PostgreSQL Indexing Strategies

What is `seq_page_cost`?

Machine-readable plan output for tools like pg_plan_guarantee, pev2, or auto_explain analysis. A planner parameter that estimates the cost of reading a heap page sequentially; defaults to 1.0. It speeds up referential integrity checks during INSERT/UPDATE/DELETE on the referenced table, and enables efficient joins on the FK column. More memory allows non-lossy bitmaps, which skip the recheck and can be faster.

Question 49 PostgreSQL Indexing Strategies

What is the `pages_per_range` BRIN parameter?

You cannot; you must DROP and re-CREATE, or use REINDEX CONCURRENTLY to a new definition. A join that merges two pre-sorted inputs; often uses B-tree indexes to provide the sorted inputs cheaply. The number of heap pages summarized in each BRIN block range. Smaller values = more accurate index, larger index size; larger values = more compact but coarser. Generalized Search Tree; a balanced tree that supports many different search strategies (geometry, range types, full-text, exclusion constraints).

Question 50 PostgreSQL Indexing Strategies

What is the difference between `indisvalid` and `indisready`?

A join that, for each row of the outer side, probes the inner side using an index; preferred when the outer side is small or the inner side has a selective index. A plan node that builds a bitmap of pages matching an index condition, often combined (BitmapAnd/BitmapOr) with results from several indexes, then visited by a Bitmap Heap Scan. indisready means the index is ready for updates; indisvalid means it is ready to be used by queries. A CONCURRENTLY-built index becomes ready first, then valid. Postgres looks up the row via the specified unique index (or primary key) and skips inserting if a conflict is found; without a unique index, the command cannot be used.