Characteristic of indexing in the PostgreSQL database management system

  • Natalia Grafeeva ITMO University, 49 Kronverksky, bldg. A, 197101, Saint Petersburg, Russia
  • Artem Nazarov ITMO University, 49 Kronverksky, bldg. A, 197101, Saint Petersburg, Russia
DOI: https://doi.org/10.32603/2071-2340-2024-4-82-96
Keywords: indexing, PostgreSQL, B-indexes

Abstract

The article discusses the features of the indexing process in the PostgreSQL database. The authors conducted a review and comparative analysis of indexes of various types, demonstrated examples of effective use of indexes. Based on the obtained results, conclusions about indexing as one of the means of optimizing SQL queries were drawn.

Author Biographies

Natalia Grafeeva, ITMO University, 49 Kronverksky, bldg. A, 197101, Saint Petersburg, Russia

Cand. of Sciences (Phys.-Math.), Associate Professor, ITMO University, nggrafeeva@corp.ifmo.ru

Artem Nazarov, ITMO University, 49 Kronverksky, bldg. A, 197101, Saint Petersburg, Russia

ITMO University, 49 Kronverksky, bldg. A, 197101, Saint Petersburg, Russia, nggrafeeva@corp.ifmo.ru

References

S. V. Morozov and S. A. Nesterov, “Comparative Analysis of Index Types in SQL Server And PostgreSQL DBMS,” in Proc. of 28th International Scientific, Educational and Practical Conference SYSTEM ANALYSIS IN ENGINEERING AND CONTROL. St. Petersburg 16 Jul 2024, vol. 2, pp. 485–491, 2024 (in Russian); doi:10.18720/SPBPU/2/id24-202

A. A. Golovlev, “Research and Development of Approaches to Indexing of Big Texts in Oracle DBMS,” Teoriya i praktika sovremennoi nauki, vol. 12, pp. 108–111, 2018 (in Russian).

A. S. Vendin, [Using Indexes in RDBMS] “Ispol’zovanie indeksov v relyatsionnykh SUBD,” Uspekhi sovremennoi nauki, vol.6, no. 3, pp. 38–41, 2017 (in Russian).

Postgres Professional, “Indexes in PostgreSQL,” in habr.com, 2017 (in Russian). [Online]. Available: https://habr.com/ru/companies/postgrespro/articles/326096/

S. D. Kuznetsov, “Basics of modern databases,” in citforum.ru, 2024 (in Russian). [Online]. Available: https://citforum.ru/database/osbd/contents.shtml

The PostgreSQL Global Development Group, “PostgreSQL 16.6 Documentation. Chapter 11. Indexes,” in postgrespro.ru, 2024 (in Russian). [Online]. Available: https://postgrespro.ru/docs/postgresql/16/indexes

C. J. Date, Introduction to Database Systems, 8th edition, Moscow: Williams Pbl. h., 2005 (in Russian).

Postgres Professional, “Query Optimization. Index Access,” in postgrespro.ru, 2024. [Online]. Available: https://edu.postgrespro.ru/qpt-13/qpt_04_indexscan.html

E. V. Rogov, PostgreSQL 16 inside, Moscow: DMK Press, 2024 (in Russian).

B. A. Novikov, E. A. Gorshkova, and N. G. Grafeeva, Fundamentals of Database Technologies: a textbook E. V. Rogov ed., 2nd edition, Moscow: DMK Press, 2020 (in Russian).

Published
2025-04-26
How to Cite
Grafeeva, N., & Nazarov, A. (2025). Characteristic of indexing in the PostgreSQL database management system. Computer Tools in Education, (4), 82-96. https://doi.org/10.32603/2071-2340-2024-4-82-96
Issue
No 4 (2024)
Section
Informational systems