COMPARATIVE ANALYSIS OF B-TREE AND HASH INDEXES FOR POSTGRESQL QUERY OPTIMIZATION

Angga Ramdhani; Suprih Widodo

doi:10.33330/jurteksi.v12i3.4653

Angga Ramdhani Universitas Pendidikan Indonesia
Suprih Widodo Universitas Pendidikan Indonesia

DOI: https://doi.org/10.33330/jurteksi.v12i3.4653

Keywords: b-tree index, hash index, optimasi query, PostgreSQL, query planner

Abstract

Abstract: Query performance is a critical factor in managing large-scale databases. One of the most widely used optimization techniques is indexing. This study aims to analyze the impact of indexing on query performance in PostgreSQL, compare the effectiveness of B-Tree and Hash indexes, and evaluate their influence on query planner decisions. A quantitative experimental approach was employed using the TPC-H benchmark dataset at scale factors SF0.1, SF1, and SF10. Experiments were conducted using EXPLAIN ANALYZE on exact match, range, and join queries under three conditions: without indexing, with B-Tree indexing, and with Hash indexing. The results demonstrate that indexing significantly improves query performance. For exact match queries on the SF10 dataset, execution time decreased from 93.36 ms without indexing to 0.034 ms using B-Tree and 0.045 ms using Hash indexes. For join queries, execution time was reduced from 857.77 ms to 0.180 ms using B-Tree and 0.079 ms using Hash indexes. B-Tree showed consistent performance across different query types, while Hash achieved the best results for equality-based queries. Furthermore, index usage influenced query planner decisions in selecting more efficient execution strategies. These findings indicate that appropriate index selection can substantially improve data access efficiency in PostgreSQL.

Keywords: b-tree index; hash index; PostgreSQL; query optimization; query planner

Abstrak: Performa query merupakan faktor penting dalam pengelolaan basis data berskala besar. Salah satu teknik optimasi yang umum digunakan adalah indexing. Penelitian ini bertujuan menganalisis pengaruh penggunaan indexing terhadap performa query pada PostgreSQL, membandingkan efektivitas B-Tree dan Hash index, serta mengevaluasi pengaruhnya terhadap keputusan query planner. Penelitian menggunakan metode eksperimen kuantitatif dengan dataset benchmark TPC-H pada skala SF0.1, SF1, dan SF10. Pengujian dilakukan menggunakan EXPLAIN ANALYZE pada exact match query, range query, dan join query dalam kondisi tanpa index, menggunakan B-Tree index, dan Hash index. Hasil penelitian menunjukkan bahwa indexing meningkatkan performa query secara signifikan. Pada exact match query dataset SF10, execution time menurun dari 93,36 ms tanpa index menjadi 0,034 ms menggunakan B-Tree dan 0,045 ms menggunakan Hash index. Pada join query, execution time berkurang dari 857,77 ms menjadi 0,180 ms menggunakan B-Tree dan 0,079 ms menggunakan Hash index. B-Tree menunjukkan performa yang konsisten pada berbagai jenis query, sedangkan Hash index memberikan performa terbaik pada query berbasis equality. Selain itu, penggunaan index memengaruhi keputusan query planner dalam memilih strategi eksekusi yang lebih efisien. Hasil penelitian menunjukkan bahwa pemilihan metode indexing yang tepat dapat meningkatkan efisiensi akses data pada PostgreSQL

Kata kunci: b-tree index; hash index; optimasi query; PostgreSQL; query planner

References

S. V. Salunke and A. Ouda, “A Performance Benchmark for the PostgreSQL and MySQL Databa ses,” Future Internet, vol. 16, no. 10, pp. 1–22, 2024, doi: 10.3390/fi16100382.

D. Zolotukhina, “Comparative analysis of indexing strategies in PostgreSQL under various load scenarios,” Программные систе мы и вычислительные методы, no. 1, pp. 21–31, Jan. 2025, doi: 10.7256/2454-0714.2025.1.73138.

S. Huang, Y. Qin, X. Zhang, Y. Tu, Z. Li, and B. Cui, “Survey on performance optimization for da-tabase systems,” Science China In-formation Sciences, vol. 66, no. 2, p. 121102, Feb. 2023, doi: 10.1007/s11432-021-3578-6.

V. B. Ramu, “Optimizing Data base Performance: Strategies for Efficient Query Execution and Re-source Utilization,” Internatio nal Journal of Computer Trends and Technology, vol. 71, no. 7, pp. 15–21, Jul. 2023, doi: 10.14445/22312803/ijctt-v71i7p103.

K. Sirigiri, “Enhancing SQL Query Performance: A Case Study on Optimizing Enterprise Data Pro-cessing,” International Journal of Basic and Applied Sciences, vol. 14, no. 5, pp. 353–360, Sep. 2025, doi: 10.14419/x2wqqh31.

A. Anchlia, “Enhancing Query Performance Through Relational Database Indexing,” International Journal of Computer Trends and Technology, vol. 72, no. 8, pp. 130–133, Aug. 2024, doi: 10.14445/22312803/IJCTT-V72I8P119.

I. C. Saidu, M. Yusuf, F. C. Nemariyi, and A. C. George, “In-dexing techniques and structu red queries for relational databases management systems,” Journal of the Nigerian Society of Physical Sciences, vol. 6, no. 4, Nov. 2024, doi: 10.46481/jnsps.2024.2155.

M. Müller, L. Benson, and V. Leis, “B-Trees Are Back: Engineering Fast and Pageable Node Layouts,” Proceedings of the ACM on Management of Data, vol. 3, no. 1, pp. 1–26, Feb. 2025, doi: 10.1145/3709664.

M. Abbasi, M. V. Bernardo, P. Váz, J. Silva, and P. Martins, “Re-visiting Database Indexing for Parallel and Accelerated Compu-ting: A Comprehensive Study and Novel Approaches,” Information (Switzerland), vol. 15, no. 8, Aug. 2024, doi: 10.3390/info15080429.

Kurniadi, V. A. Andrea, J. Panja-ya, and K. Jingga, “Performance Comparison of Relational Data-base Management Systems for Processing Large Amount of Text Data,” Procedia Comput. Sci., vol. 269, pp. 150–160, 2025, doi: 10.1016/j.pro cs.2025.08.268.

L. Qu et al., “Are current bench-marks adequate to evaluate dis-tributed transactional databa ses?,” BenchCouncil Transactions on Benchmarks, Standards and Eval-uations, vol. 2, no. 1, p. 100031, Mar. 2022, doi: 10.1016/j.tbench.2022.100031.

H. Lan, Z. Bao, and Y. Peng, “A Survey on Advancing the DBMS Query Optimizer: Cardinality Es-timation, Cost Model, and Plan Enumeration,” Data Sci. Eng., vol. 6, no. 1, pp. 86–101, Mar. 2021, doi: 10.1007/s41019-020-00149-7.

J. R. Haritsa, “Robust Query Pro-cessing: A Survey,” Founda tions and Trends in Databases, vol. 15, no. 1, pp. 1–114, Dec. 2024, doi: 10.1561/1900000089.

D. Samoladas, C. Karras, A. Kar-ras, L. Theodorakopoulos, and S. Sioutas, “Tree Data Structures and Efficient Indexing Techniques for Big Data Management: A Com-prehensive Study,” in Procee dings of the 26th Pan-Hellenic Conference on Informatics, New York, NY, USA: ACM, Nov. 2022, pp. 123–132. doi: 10.1145 /3575879.3575977.

S. Nakazono, Y. Bessho, H. Ka-washima, and T. Nakamori, “Grif-fin: Fast Transactional Data base Index with Hash and B+-Tree,” in 2024 IEEE 20th International Conference on e-Science (e-Science), IEEE, Sep. 2024, pp. 1–10. doi: 10.1109/e-Science62913.2024.10678674.