Базы данных PostgreSQL, учебный курс, модуль Индексы Скачай модуль учебного курса…
- User
Базы данных PostgreSQL, учебный курс, модуль Индексы Скачай модуль учебного курса https://edu.postgrespro.ru/16/qpt-16/qpt_03_am_am.html Напиши на его основе конспект одной лекции про индексы в базах данных. Добавь сравнение видов индексов с Oracle, PostgreSQL, MySQL и другими OLTP СУБД. Пометь места для иллюстраций и опиши иллюстрации для их отрисовки в mermaid или plantuml или другом инструменте.
- Assistant
Конспект лекции: Индексы в базах данных
Тема: Индексы в реляционных базах данных (на примере PostgreSQL и сравнение с другими СУБД)
Цель лекции: Понять, что такое индексы, зачем они нужны, какие бывают типы индексов в PostgreSQL и других популярных СУБД (Oracle, MySQL и др.), как выбирать подходящий тип индекса в зависимости от задачи.
План лекции:
-
Введение в индексы
-
Архитектура индексов в PostgreSQL
-
Типы индексов в PostgreSQL
-
Сравнение с другими СУБД (Oracle, MySQL, SQL Server)
-
Практические рекомендации по выбору индексов
-
Заключение
-
Введение в индексы
Индекс — это структура данных, которая ускоряет поиск строк в таблице по значениям одного или нескольких столбцов.
Аналогия: индекс в книге — позволяет быстро найти нужную страницу по ключевому слову.
Преимущества:
- Ускорение операций SELECT, JOIN, WHERE, ORDER BY
- Возможность реализации уникальности (UNIQUE)
- Поддержка полнотекстового поиска, поиска по диапазонам и т.д.
Недостатки:
- Занимают дополнительное место
- Замедляют операции INSERT, UPDATE, DELETE
- Требуют обслуживания (REINDEX, VACUUM)
📌 Иллюстрация 1 (mermaid):
graph TD A[Таблица без индекса] -->|Поиск по значению| B[Полный перебор строк] C[Таблица с индексом] -->|Поиск по значению| D[Быстрый доступ через индекс]
- Архитектура индексов в PostgreSQL
PostgreSQL поддерживает модульную архитектуру индексов через Access Methods (методы доступа).
Каждый тип индекса реализован как отдельный метод доступа (AM — Access Method).
Основные методы доступа:
- btree — по умолчанию, сбалансированное дерево
- hash — хеш-таблица
- gin — Generalized Inverted Index (для массивов, JSON, полнотекстового поиска)
- gist — Generalized Search Tree (для геоданных, диапазонов)
- spgist — Space-partitioned GiST (оптимизирован для разреженных данных)
- brin — Block Range Index (для очень больших таблиц)
📌 Иллюстрация 2 (plantuml):
@startuml class Index { +name +columns +access_method } class BTree class Hash class GIN class GiST class SP-GiST class BRIN
Index <|-- BTree Index <|-- Hash Index <|-- GIN Index <|-- GiST Index <|-- SP-GiST Index <|-- BRIN @enduml
- Типы индексов в PostgreSQL
3.1 B-tree (по умолчанию)
- Поддерживает сравнение <, <=, =, >=, >
- Используется для большинства операций
- Поддерживает уникальность
3.2 Hash
- Только для равенства (=)
- Быстрее B-tree при равенстве, но редко используется
- Не поддерживает уникальность до PostgreSQL 10
3.3 GiST
- Обобщённое дерево поиска
- Поддерживает пользовательские типы данных (гео, диапазоны)
- Используется в PostGIS
3.4 GIN
- Обратный индекс
- Эффективен для массивов, JSONB, полнотекстового поиска
- Поддерживает быстрый поиск по вхождению
3.5 SP-GiST
- Пространственно-разделённый индекс
- Эффективен для данных с неравномерным распределением
3.6 BRIN
- Индекс по диапазонам блоков
- Очень компактный
- Эффективен для больших таблиц с упорядоченными данными
📌 Иллюстрация 3 (mermaid):
graph LR A[B-tree] -->|универсальный| B[=, <, >] C[Hash] -->|только =| B D[GIN] -->|поиск по массивам| E[JSONB, tsvector] F[GiST] -->|гео, диапазоны| G[PostGIS] H[BRIN] -->|по блокам| I[большие таблицы]
- Сравнение с другими СУБД
Тип индекса PostgreSQL Oracle MySQL (InnoDB) SQL Server B-tree Да (по умолчанию) Да (B*-tree) Да (по умолчанию) Да Hash Да (ограниченно) Нет Нет (устарел) Нет GiST Да Нет Нет Нет GIN Да Нет Нет Нет BRIN Да Нет Нет Нет Bitmap Нет Да Нет Да (bitmap scan) Clustered Нет Да (IOT) Да (PRIMARY KEY) Да Fulltext Да (через GIN) Да (Oracle Text) Да (FULLTEXT) Да (FTS) Выводы:
- PostgreSQL предлагает наибольшее разнообразие типов индексов
- Oracle и SQL Server поддерживают bitmap-индексы, которых нет в PostgreSQL
- MySQL ограничен B-tree и FULLTEXT
- PostgreSQL позволяет создавать собственные методы доступа
📌 Иллюстрация 4 (mermaid):
graph TD A[PostgreSQL] --> B[B-tree, Hash, GIN, GiST, BRIN] C[Oracle] --> D[B-tree, Bitmap, IOT] E[MySQL] --> F[B-tree, FULLTEXT] G[SQL Server] --> H[B-tree, Bitmap, FTS]
- Практические рекомендации
- Используйте B-tree по умолчанию
- GIN — для массивов, JSONB, полнотекстового поиска
- GiST — для геоданных, диапазонов
- BRIN — для больших таблиц с упорядоченными данными
- Не создавайте индекс "на всякий случай" — это замедлит INSERT/UPDATE
- Используйте EXPLAIN ANALYZE для оценки эффективности
- Заключение
Индексы — мощный инструмент оптимизации запросов. PostgreSQL предоставляет гибкую архитектуру индексов, позволяя выбирать подходящий тип под конкретную задачу. Понимание различий между типами индексов и их аналогами в других СУБД помогает проектировать эффективные схемы баз данных.
Домашнее задание:
- Создайте таблицу с 1 млн строк
- Создайте B-tree и GIN индексы
- Сравните время выполнения запросов с использованием EXPLAIN ANALYZE
Дополнительно:
- Прочитать документацию PostgreSQL по методам доступа: https://www.postgresql.org/docs/current/indexes-types.html
- Ознакомиться с модулем курса: https://edu.postgrespro.ru/16/qpt-16/qpt_03_am_am.html
Конец лекции.
-
- User
учти еще материал по ссылкам https://edu.postgrespro.ru/16/qpt-16/qpt_04_am_parallels.html https://edu.postgrespro.ru/16/qpt-16/qpt_05_am_indexes.html
- Assistant
Конспект лекции: Индексы в PostgreSQL и других СУБД
Тема: Индексы в реляционных базах данных: архитектура, типы, параллелизм и сравнение с другими СУБД
Цель лекции: Изучить архитектуру и типы индексов в PostgreSQL, понять, как они используются в планах выполнения запросов, рассмотреть параллельное выполнение с использованием индексов, а также сравнить с реализациями индексов в других популярных СУБД (Oracle, MySQL, SQL Server).
План лекции:
-
Что такое индекс и зачем он нужен
-
Архитектура индексов в PostgreSQL
-
Типы индексов в PostgreSQL
-
Использование индексов в планах выполнения
-
Параллельное выполнение с использованием индексов
-
Сравнение с другими СУБД
-
Практические рекомендации
-
Заключение
-
Что такое индекс и зачем он нужен
Индекс — это структура данных, предназначенная для ускорения поиска строк в таблице по значениям одного или нескольких столбцов.
Преимущества:
- Ускорение операций SELECT, JOIN, WHERE, ORDER BY
- Поддержка уникальности (UNIQUE)
- Возможность реализации полнотекстового поиска, поиска по диапазонам и т.д.
Недостатки:
- Дополнительное место на диске
- Замедление операций INSERT, UPDATE, DELETE
- Необходимость обслуживания (REINDEX, VACUUM)
📌 Иллюстрация 1 (mermaid):
graph TD A[Таблица без индекса] -->|Поиск по значению| B[Полный перебор строк] C[Таблица с индексом] -->|Поиск по значению| D[Быстрый доступ через индекс]
- Архитектура индексов в PostgreSQL
PostgreSQL использует модульную архитектуру индексов через Access Methods (методы доступа).
Каждый тип индекса реализован как отдельный метод доступа (AM — Access Method), который определяет:
- Структуру хранения
- Алгоритмы поиска
- Поддерживаемые операторы
- Поддержку параллелизма
Методы доступа:
- btree — сбалансированное дерево (по умолчанию)
- hash — хеш-таблица
- gin — Generalized Inverted Index
- gist — Generalized Search Tree
- spgist — Space-partitioned GiST
- brin — Block Range Index
📌 Иллюстрация 2 (plantuml):
@startuml class Index { +name +columns +access_method } class BTree class Hash class GIN class GiST class SP-GiST class BRIN
Index <|-- BTree Index <|-- Hash Index <|-- GIN Index <|-- GiST Index <|-- SP-GiST Index <|-- BRIN @enduml
- Типы индексов в PostgreSQL
3.1 B-tree
- Универсальный индекс
- Поддерживает сравнение <, <=, =, >=, >
- Используется по умолчанию
- Поддерживает уникальность
3.2 Hash
- Только для равенства (=)
- Быстрее B-tree при равенстве, но редко используется
- Поддерживает параллельное выполнение
3.3 GiST
- Обобщённое дерево поиска
- Поддерживает пользовательские типы данных (гео, диапазоны)
- Используется в PostGIS
- Поддерживает параллельное выполнение
3.4 GIN
- Обратный индекс
- Эффективен для массивов, JSONB, полнотекстового поиска
- Поддерживает параллельное выполнение
3.5 SP-GiST
- Пространственно-разделённый индекс
- Эффективен для данных с неравномерным распределением
- Поддерживает параллельное выполнение
3.6 BRIN
- Индекс по диапазонам блоков
- Очень компактный
- Эффективен для больших таблиц с упорядоченными данными
- Поддерживает параллельное выполнение
📌 Иллюстрация 3 (mermaid):
graph LR A[B-tree] -->|универсальный| B[=, <, >] C[Hash] -->|только =| B D[GIN] -->|поиск по массивам| E[JSONB, tsvector] F[GiST] -->|гео, диапазоны| G[PostGIS] H[BRIN] -->|по блокам| I[большие таблицы]
- Использование индексов в планах выполнения
PostgreSQL использует индексы в следующих операциях:
- Index Scan — последовательный проход по индексу
- Index Only Scan — чтение только из индекса (без обращения к таблице)
- Bitmap Index Scan — построение битовой карты совпадений
- Parallel Index Scan — параллельный проход по индексу
Index Only Scan возможен, если:
- Все нужные столбцы есть в индексе
- Индекс содержит актуальные данные (visibility map)
📌 Иллюстрация 4 (mermaid):
graph TD A[Запрос SELECT] --> B[Index Scan] A --> C[Index Only Scan] A --> D[Bitmap Index Scan] A --> E[Parallel Index Scan]
- Параллельное выполнение с использованием индексов
Параллельное выполнение возможно, если:
- Используется подходящий тип индекса (btree, gin, gist, spgist, brin)
- Таблица достаточно большая
- Запрос допускает параллелизм (например, SELECT с WHERE)
- Настроены параметры parallel_workers и parallel_setup_cost
Примеры:
- Parallel Index Scan — параллельный проход по btree
- Parallel Bitmap Heap Scan — используется с Bitmap Index Scan
📌 Иллюстрация 5 (plantuml):
@startuml actor Client participant "PostgreSQL Planner" as Planner participant "Worker 1" as W1 participant "Worker 2" as W2 participant "Index" as IDX
Client -> Planner : SELECT ... WHERE ... Planner -> W1 : Parallel Index Scan (range 1) Planner -> W2 : Parallel Index Scan (range 2) W1 -> IDX : Чтение диапазона 1 W2 -> IDX : Чтение диапазона 2 @enduml
- Сравнение с другими СУБД
Тип индекса PostgreSQL Oracle MySQL (InnoDB) SQL Server B-tree Да (по умолчанию) Да (B*-tree) Да (по умолчанию) Да Hash Да (ограниченно) Нет Нет (устарел) Нет GiST Да Нет Нет Нет GIN Да Нет Нет Нет BRIN Да Нет Нет Нет Bitmap Нет Да Нет Да (bitmap scan) Clustered Нет Да (IOT) Да (PRIMARY KEY) Да Fulltext Да (через GIN) Да (Oracle Text) Да (FULLTEXT) Да (FTS) Параллельный индексный скан Да (btree, gin и др.) Да (Parallel Query) Нет Да Выводы:
- PostgreSQL предлагает наибольшее разнообразие типов индексов
- Oracle и SQL Server поддерживают bitmap-индексы, которых нет в PostgreSQL
- MySQL ограничен B-tree и FULLTEXT
- PostgreSQL поддерживает параллельное выполнение с большинством типов индексов
📌 Иллюстрация 6 (mermaid):
graph TD A[PostgreSQL] --> B[B-tree, Hash, GIN, GiST, BRIN, параллелизм] C[Oracle] --> D[B-tree, Bitmap, IOT, Parallel Query] E[MySQL] --> F[B-tree, FULLTEXT] G[SQL Server] --> H[B-tree, Bitmap, FTS, Parallel Scan]
- Практические рекомендации
- Используйте B-tree по умолчанию
- GIN — для массивов, JSONB, полнотекстового поиска
- GiST — для геоданных, диапазонов
- BRIN — для больших таблиц с упорядоченными данными
- Не создавайте индекс "на всякий случай" — это замедлит INSERT/UPDATE
- Используйте EXPLAIN (ANALYZE, VERBOSE, BUFFERS) для оценки эффективности
- Настройте параметры параллелизма: max_parallel_workers, parallel_setup_cost, parallel_tuple_cost
- Заключение
Индексы — ключевой инструмент оптимизации запросов. PostgreSQL предоставляет мощную и гибкую архитектуру индексов, включая поддержку параллельного выполнения. Понимание типов индексов и их применения позволяет проектировать эффективные схемы баз данных и ускорять выполнение запросов.
Домашнее задание:
- Создайте таблицу с 1 млн строк
- Создайте B-tree, GIN и BRIN индексы
- Выполните запросы с EXPLAIN ANALYZE
- Сравните планы выполнения с и без параллелизма
Дополнительные материалы:
- https://edu.postgrespro.ru/16/qpt-16/qpt_03_am_am.html
- https://edu.postgrespro.ru/16/qpt-16/qpt_04_am_parallels.html
- https://edu.postgrespro.ru/16/qpt-16/qpt_05_am_indexes.html
- https://www.postgresql.org/docs/current/indexes-types.html
Конец лекции.
-
- User
переведи этот конспект на английский язык умеренно используй идиомы
- Assistant
Lecture Summary: Indexes in PostgreSQL and Other RDBMS
Topic: Indexes in Relational Databases — Architecture, Types, Parallelism, and Comparison with Other DBMS
Goal: To understand the architecture and types of indexes in PostgreSQL, how they are used in query execution plans, how parallelism works with indexes, and how PostgreSQL compares to other popular RDBMS (Oracle, MySQL, SQL Server).
Lecture Outline:
-
What is an Index and Why Do We Need It?
-
Index Architecture in PostgreSQL
-
Types of Indexes in PostgreSQL
-
Index Usage in Query Execution Plans
-
Parallel Execution with Indexes
-
Comparison with Other RDBMS
-
Practical Recommendations
-
Conclusion
-
What is an Index and Why Do We Need It?
An index is a data structure that speeds up the retrieval of rows from a table based on the values of one or more columns.
Think of it like the index in a book — it helps you jump straight to the page you need.
Benefits:
- Speeds up SELECT, JOIN, WHERE, ORDER BY operations
- Supports uniqueness (UNIQUE constraints)
- Enables full-text search, range queries, and more
Drawbacks:
- Takes up additional disk space
- Slows down INSERT, UPDATE, DELETE operations
- Requires maintenance (REINDEX, VACUUM)
📌 Illustration 1 (mermaid):
graph TD A[Table without index] -->|Search by value| B[Full table scan] C[Table with index] -->|Search by value| D[Fast access via index]
- Index Architecture in PostgreSQL
PostgreSQL uses a modular index architecture based on Access Methods (AM).
Each index type is implemented as a separate access method, which defines:
- Storage structure
- Search algorithms
- Supported operators
- Parallelism support
Built-in access methods:
- btree — balanced tree (default)
- hash — hash table
- gin — Generalized Inverted Index
- gist — Generalized Search Tree
- spgist — Space-partitioned GiST
- brin — Block Range Index
📌 Illustration 2 (plantuml):
@startuml class Index { +name +columns +access_method } class BTree class Hash class GIN class GiST class SP-GiST class BRIN
Index <|-- BTree Index <|-- Hash Index <|-- GIN Index <|-- GiST Index <|-- SP-GiST Index <|-- BRIN @enduml
- Types of Indexes in PostgreSQL
3.1 B-tree
- General-purpose index
- Supports <, <=, =, >=, > comparisons
- Default index type
- Supports uniqueness
3.2 Hash
- Only supports equality (=)
- Slightly faster than B-tree for equality, but rarely used
- Supports parallel execution
3.3 GiST
- Generalized Search Tree
- Supports custom data types (e.g., geometric, range types)
- Used in PostGIS
- Supports parallel execution
3.4 GIN
- Inverted index
- Efficient for arrays, JSONB, full-text search
- Supports parallel execution
3.5 SP-GiST
- Space-partitioned GiST
- Efficient for non-uniformly distributed data
- Supports parallel execution
3.6 BRIN
- Block Range Index
- Very compact
- Ideal for large, naturally ordered tables
- Supports parallel execution
📌 Illustration 3 (mermaid):
graph LR A[B-tree] -->|general-purpose| B[=, <, >] C[Hash] -->|equality only| B D[GIN] -->|array/JSONB search| E[JSONB, tsvector] F[GiST] -->|geo, range types| G[PostGIS] H[BRIN] -->|block-level| I[large tables]
- Index Usage in Query Execution Plans
PostgreSQL uses indexes in several ways:
- Index Scan — sequential scan through the index
- Index Only Scan — reads only from the index (no table access)
- Bitmap Index Scan — builds a bitmap of matching rows
- Parallel Index Scan — parallel traversal of the index
Index Only Scan is possible when:
- All required columns are in the index
- The index contains up-to-date visibility information
📌 Illustration 4 (mermaid):
graph TD A[SELECT query] --> B[Index Scan] A --> C[Index Only Scan] A --> D[Bitmap Index Scan] A --> E[Parallel Index Scan]
- Parallel Execution with Indexes
Parallel execution is possible when:
- The index type supports it (btree, gin, gist, spgist, brin)
- The table is large enough
- The query allows parallelism (e.g., SELECT with WHERE)
- Parallelism parameters are properly configured
Examples:
- Parallel Index Scan — parallel traversal of a btree index
- Parallel Bitmap Heap Scan — used with Bitmap Index Scan
📌 Illustration 5 (plantuml):
@startuml actor Client participant "PostgreSQL Planner" as Planner participant "Worker 1" as W1 participant "Worker 2" as W2 participant "Index" as IDX
Client -> Planner : SELECT ... WHERE ... Planner -> W1 : Parallel Index Scan (range 1) Planner -> W2 : Parallel Index Scan (range 2) W1 -> IDX : Read range 1 W2 -> IDX : Read range 2 @enduml
- Comparison with Other RDBMS
Index Type PostgreSQL Oracle MySQL (InnoDB) SQL Server B-tree Yes (default) Yes (B*-tree) Yes (default) Yes Hash Yes (limited) No No (deprecated) No GiST Yes No No No GIN Yes No No No BRIN Yes No No No Bitmap No Yes No Yes (bitmap scan) Clustered No Yes (IOT) Yes (PRIMARY KEY) Yes Full-text Yes (via GIN) Yes (Oracle Text) Yes (FULLTEXT) Yes (FTS) Parallel Index Yes (btree, etc) Yes (Parallel Query) No Yes Takeaways:
- PostgreSQL offers the widest variety of index types
- Oracle and SQL Server support bitmap indexes, which PostgreSQL lacks
- MySQL is limited to B-tree and FULLTEXT
- PostgreSQL supports parallel index scans for most index types
📌 Illustration 6 (mermaid):
graph TD A[PostgreSQL] --> B[B-tree, Hash, GIN, GiST, BRIN, parallelism] C[Oracle] --> D[B-tree, Bitmap, IOT, Parallel Query] E[MySQL] --> F[B-tree, FULLTEXT] G[SQL Server] --> H[B-tree, Bitmap, FTS, Parallel Scan]
- Practical Recommendations
- Use B-tree by default
- Use GIN for arrays, JSONB, full-text search
- Use GiST for geometric and range types
- Use BRIN for large, naturally ordered tables
- Don’t create indexes “just in case” — they slow down writes
- Use EXPLAIN (ANALYZE, VERBOSE, BUFFERS) to evaluate performance
- Tune parallelism settings: max_parallel_workers, parallel_setup_cost, etc.
- Conclusion
Indexes are a powerful tool for query optimization. PostgreSQL provides a rich and flexible index architecture, including support for parallel execution. Understanding the types of indexes and when to use them is key to designing efficient database schemas and speeding up queries.
Homework:
- Create a table with 1 million rows
- Create B-tree, GIN, and BRIN indexes
- Run queries with EXPLAIN ANALYZE
- Compare execution plans with and without parallelism
Further Reading:
- https://edu.postgrespro.ru/16/qpt-16/qpt_03_am_am.html
- https://edu.postgrespro.ru/16/qpt-16/qpt_04_am_parallels.html
- https://edu.postgrespro.ru/16/qpt-16/qpt_05_am_indexes.html
- https://www.postgresql.org/docs/current/indexes-types.html
End of lecture.
-
- User
continue in english write teacher demo + tasks and instructions (with solutions separately) for seminar on indexes include instructions on infrastructure preparation in two versions: shared postgresql for all students (approx 100 person) and individual docker setup
- Assistant
Seminar Plan: Indexes in PostgreSQL
Topic: Practical Work with Indexes in PostgreSQL
Audience: Intermediate-level students with basic SQL and PostgreSQL knowledge
Duration: 90–120 minutes
Objectives:
- Understand how different types of indexes work in practice
- Learn to analyze query plans using EXPLAIN
- Compare performance with and without indexes
- Explore parallel index scans
- Practice creating and using B-tree, GIN, and BRIN indexes
Structure:
-
Infrastructure Setup (shared & individual)
-
Teacher Demo (30 minutes)
-
Student Tasks (45–60 minutes)
-
Solutions (for instructor use)
-
Wrap-up and Q&A
-
Infrastructure Setup
Option A: Shared PostgreSQL Server (for ~100 students)
Requirements:
- One powerful PostgreSQL server (v14+ recommended)
- At least 16 CPU cores, 64 GB RAM, SSD storage
- pg_hba.conf configured to allow student logins
- Each student gets a separate schema or database
Setup Instructions:
- Create a database: seminar_indexes
- Create 100 roles: student01 to student100
- Grant each role access to their own schema:
CREATE ROLE student01 LOGIN PASSWORD 'pass01'; CREATE SCHEMA AUTHORIZATION student01;
Repeat for all students (can be scripted).
- Set search_path to student's schema on login:
ALTER ROLE student01 SET search_path TO student01;
- Enable parallelism in postgresql.conf:
max_parallel_workers = 16 max_parallel_workers_per_gather = 4 parallel_setup_cost = 1000 parallel_tuple_cost = 0.1
Option B: Individual Docker Setup
Requirements:
- Docker installed on student machines
- Docker Compose (optional)
Dockerfile:
FROM postgres:15 ENV POSTGRES_USER=student ENV POSTGRES_PASSWORD=student ENV POSTGRES_DB=seminar
COPY init.sql /docker-entrypoint-initdb.d/
init.sql (partial):
CREATE TABLE big_table ( id serial PRIMARY KEY, category text, data text, tags text[], created_at timestamp );
-- Fill with 1 million rows INSERT INTO big_table (category, data, tags, created_at) SELECT CASE WHEN i % 5 = 0 THEN 'A' WHEN i % 5 = 1 THEN 'B' WHEN i % 5 = 2 THEN 'C' WHEN i % 5 = 3 THEN 'D' ELSE 'E' END, md5(random()::text), ARRAY[md5(random()::text), md5(random()::text)], now() - (random() * interval '365 days') FROM generate_series(1, 1000000) AS i;
Run:
docker build -t pg-index-seminar . docker run -p 5432:5432 pg-index-seminar
- Teacher Demo (30 minutes)
Goal: Show how different indexes affect query performance and plans
Step 1: Create a large table
CREATE TABLE big_table ( id serial PRIMARY KEY, category text, data text, tags text[], created_at timestamp );
-- Insert 1 million rows (see init.sql above)
Step 2: Run a query without index
EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = 'C';
Observe: Seq Scan
Step 3: Create B-tree index
CREATE INDEX idx_category ON big_table(category);
Rerun the query:
EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = 'C';
Observe: Index Scan or Bitmap Index Scan
Step 4: Create GIN index for tags
CREATE INDEX idx_tags ON big_table USING gin(tags);
Query:
EXPLAIN ANALYZE SELECT * FROM big_table WHERE tags @> ARRAY['abc123'];
Step 5: Create BRIN index for created_at
CREATE INDEX idx_brin_created ON big_table USING brin(created_at);
Query:
EXPLAIN ANALYZE SELECT * FROM big_table WHERE created_at BETWEEN now() - interval '30 days' AND now();
Step 6: Show parallelism
Enable parallelism (if not already):
SET max_parallel_workers_per_gather = 4;
Query:
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM big_table WHERE category = 'C';
Observe: Parallel Index Scan or Parallel Bitmap Heap Scan
- Student Tasks (45–60 minutes)
Task 1: Create Indexes
- Create a B-tree index on category
- Create a GIN index on tags
- Create a BRIN index on created_at
Task 2: Compare Query Plans
- Run EXPLAIN ANALYZE for queries with and without indexes
- Identify when PostgreSQL uses Index Scan, Bitmap Scan, or Seq Scan
Task 3: Use Index Only Scan
- Add a new table:
CREATE TABLE small_table ( id serial PRIMARY KEY, name text, age int );
INSERT INTO small_table (name, age) SELECT 'Name' || i, (random() * 100)::int FROM generate_series(1, 100000);
CREATE INDEX idx_age ON small_table(age);
- Run:
EXPLAIN ANALYZE SELECT age FROM small_table WHERE age BETWEEN 20 AND 30;
- Check if Index Only Scan is used
Task 4: Parallel Execution
- Run a query on big_table with WHERE category = 'C'
- Enable parallelism:
SET max_parallel_workers_per_gather = 4;
- Observe the plan
Bonus Task: Drop and recreate indexes, measure query time with \timing
- Solutions (for instructor)
Task 1:
CREATE INDEX idx_category ON big_table(category); CREATE INDEX idx_tags ON big_table USING gin(tags); CREATE INDEX idx_brin_created ON big_table USING brin(created_at);
Task 2:
-- Without index: EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = 'C'; -- Seq Scan
-- With index: EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = 'C'; -- Index Scan or Bitmap Index Scan
Task 3:
-- Index Only Scan possible if visibility map is updated VACUUM small_table;
EXPLAIN ANALYZE SELECT age FROM small_table WHERE age BETWEEN 20 AND 30;
-- Should show Index Only Scan
Task 4:
SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = 'C';
-- Should show Parallel Index Scan or Parallel Bitmap Heap Scan
- Wrap-up and Q&A
Discussion points:
- When to use which index type?
- How to detect if an index is being used?
- How to tune for parallelism?
- What are the trade-offs of indexing?
Optional Homework:
- Create a table with 10 million rows
- Test performance of BRIN vs B-tree on timestamp column
- Write a report comparing query times and plans
End of Seminar.
- User
add 20 knowledge testing questions and tasks with correct answers
- Assistant
Knowledge Testing: Indexes in PostgreSQL
(20 Questions and Tasks with Correct Answers)This set includes a mix of multiple-choice questions, true/false, short answers, and practical SQL tasks. It is designed to test understanding of index types, usage, performance, and PostgreSQL-specific features.
🧠 Section 1: Multiple Choice (1–10)
- Which index type is used by default in PostgreSQL?
A) GIN
B) BRIN
C) B-tree
D) Hash✅ Correct Answer: C) B-tree
- Which index type is best suited for full-text search in PostgreSQL?
A) B-tree
B) GIN
C) BRIN
D) Hash✅ Correct Answer: B) GIN
- Which of the following index types is most space-efficient for very large, naturally ordered tables?
A) B-tree
B) GIN
C) BRIN
D) GiST✅ Correct Answer: C) BRIN
- Which index type supports only equality (=) operations?
A) B-tree
B) Hash
C) GIN
D) GiST✅ Correct Answer: B) Hash
- Which of the following index types supports parallel index scans in PostgreSQL? (Choose all that apply)
A) B-tree
B) GIN
C) Hash
D) BRIN✅ Correct Answer: A), B), C), D)
- What is the main drawback of creating too many indexes on a table?
A) It increases SELECT performance
B) It reduces disk usage
C) It slows down INSERT/UPDATE/DELETE operations
D) It disables parallelism✅ Correct Answer: C) It slows down INSERT/UPDATE/DELETE operations
- Which of the following queries is most likely to benefit from a BRIN index?
A) SELECT * FROM logs WHERE id = 123;
B) SELECT * FROM logs WHERE message LIKE '%error%';
C) SELECT * FROM logs WHERE created_at > now() - interval '1 day';
D) SELECT * FROM logs WHERE tags @> ARRAY['urgent'];✅ Correct Answer: C) SELECT * FROM logs WHERE created_at > now() - interval '1 day';
- What is the purpose of an Index Only Scan?
A) To scan only the first page of the index
B) To avoid accessing the table heap if all data is in the index
C) To scan only unique values
D) To scan only visible rows✅ Correct Answer: B) To avoid accessing the table heap if all data is in the index
- Which PostgreSQL index type is best suited for geometric data types?
A) GIN
B) GiST
C) BRIN
D) Hash✅ Correct Answer: B) GiST
- What is the main difference between GIN and GiST indexes?
A) GIN is faster for insertions
B) GiST is used for full-text search
C) GIN is optimized for containment queries on composite values
D) GiST supports only numeric types✅ Correct Answer: C) GIN is optimized for containment queries on composite values
🧪 Section 2: True/False (11–15)
-
PostgreSQL supports bitmap indexes like Oracle.
❌ False — PostgreSQL supports bitmap scans, but not bitmap indexes. -
A B-tree index can be used for both equality and range queries.
✅ True -
GIN indexes are always smaller than B-tree indexes.
❌ False — GIN indexes are often larger due to inverted structure. -
BRIN indexes store summary information per block range.
✅ True -
Indexes in PostgreSQL are automatically used in all queries.
❌ False — The planner may choose not to use an index if it’s not efficient.
🧑💻 Section 3: Practical SQL Tasks (16–20)
- Task: Create a B-tree index on the column email in the users table.
SQL:
CREATE INDEX idx_users_email ON users(email);
✅ Correct
- Task: Create a GIN index on a JSONB column named metadata in the events table.
SQL:
CREATE INDEX idx_events_metadata ON events USING gin(metadata);
✅ Correct
- Task: Create a BRIN index on a timestamp column created_at in the logs table.
SQL:
CREATE INDEX idx_logs_created_brin ON logs USING brin(created_at);
✅ Correct
- Task: Check if a query uses an Index Only Scan.
SQL:
EXPLAIN ANALYZE SELECT id FROM users WHERE id BETWEEN 100 AND 200;
✅ Correct — If visibility map is up-to-date and only indexed columns are used, Index Only Scan may appear.
- Task: Drop an index named idx_old_index from the table orders.
SQL:
DROP INDEX idx_old_index;
✅ Correct
Bonus Tip for Instructors:
To test understanding further, ask students to:
- Compare query performance with and without indexes using \timing
- Use EXPLAIN (ANALYZE, VERBOSE, BUFFERS) to interpret query plans
- Identify when PostgreSQL chooses a sequential scan over an index
End of Knowledge Test ✅