Database Tuning with Partial Indexes

• Alain D. Fuentes Pontifícia Universidade Católica do Rio de Janeiro (PUC-Rio)
• Ana Carolina Almeida Universidade do Estado do Rio de Janeiro (UERJ)
• Rogério Luís de Carvalho Costa Pontifícia Universidade Católica do Rio de Janeiro (PUC-Rio)
• Vanessa Braganholo Universidade Federal Fluminense (UFF) https://orcid.org/0000-0002-1184-8192
• Sérgio Lifschitz Pontifícia Universidade Católica do Rio de Janeiro (PUC-Rio)

DOI: https://doi.org/10.5753/sbbd.2018.22229

Resumo

Database tuning usually involves indexes, materialized views, partitioning, query rewriting and other techniques. One strategy that presents good results for performance improvements is the use of partial indexes. However, partial indexes have not been used for database tuning in the past. This is because the search space for partial indexes is exponential in the number of attributes and tuples of the table. In this paper, we address this problem by proposing an optimized strategy to select partial indexes. The optimization relies on reducing the amount of logic reads. We explain how to select the indexable attributes and their corresponding restrictions through a formal procedure. We implement our strategy to illustrate the benefits of partial indexes for tuning issues. Results are promising.

Palavras-chave: Tuning, partial indexes, optimized strategy, logic reads

Referências

Agrawal, S., Chaudhuri, S., and Narasayya, V. R. (2001). Materialized view and index selection tool for microsoft SQL server 2000. In SIGMOD Conf., page 608. ACM.

Aouiche, K. and Darmont, J. (2009). Data mining-based materialized view and index selection in data ware-houses. J. Intell. Inf. Syst., 33(1):65–93.

BiobdPUC-Rio (2018). Dbx. https://github.com/BioBD/dbx. [April-03-18].

Chen, C., Li, F., Ooi, B. C., and Wu, S. (2011). TI: an efficient indexing mechanism for real-time search on tweets. In SIGMOD Conference, pages 649–660. ACM.

Fredkin, E. (1960). Trie memory. Commun. ACM, 3(9):490–499.

Fuentes, A. D. (2016). Automatic fine tuning with partial indexes (in portuguese). Master’s thesis, PUC-Rio Informática, Rio de Janeiro, Brasil.

Gouda, K. and Zaki, M. J. (2001). Efficiently mining maximal frequent itemsets. In ICDM, pages 163–170. IEEE Computer Society.

Graefe, G., Halim, F., Idreos, S., Kuno, H. A., Manegold, S., and Seeger, B. (2014). Transactional support for adaptive indexing. VLDB J., 23(2):303–328.

Graefe, G. and Kuno, H. A. (2010a). Adaptive indexing for relational keys. In ICDE Workshops, pages 69–74. IEEE Computer Society.

Graefe, G. and Kuno, H. A. (2010b). Self-selecting, self-tuning, incrementally optimized indexes. In EDBT, Intl Conf, pages 371–381. ACM.

Gupta, H., Harinarayan, V., Rajaraman, A., and Ullman, J. D. (1997). Index selection for OLAP. In ICDE, pages 208–219. IEEE Computer Society.

Idreos, S., Kersten, M. L., and Manegold, S. (2007a). Database cracking. In CIDR, pages 68–78. https://www.cidrdb.org.

Idreos, S., Kersten, M. L., and Manegold, S. (2007b). Updating a cracked database. In SIGMOD Conference, pages 413–424. ACM.

Idreos, S., Kersten, M. L., and Manegold, S. (2009). Self-organizing tuple reconstruction in column-stores. In SIGMOD Conference, pages 297–308. ACM.

Idreos, S., Manegold, S., Kuno, H. A., and Graefe, G. (2011). Merging what’s cracked, cracking what’s merged: Adaptive indexing in main-memory column-stores. PVLDB, 4(9):585–597.

Labio, W., Quass, D., and Adelberg, B. (1997). Physical database design for data warehouses. In ICDE, pages 277–288. IEEE Computer Society.

Lightstone, S. (2009). Physical database design for relational databases. In Encyclopedia of Database Systems, pages 2108–2114. Springer US.

Mackert, L. F. and Lohman, G. M. (1989). Index scans using a finite LRU buffer: A validated I/O model. ACM Trans. Database Syst., 14(3):401–424.

PostgreSQLv9 (2018). Partial indexes documentation. http://www.postgresql.org/docs/9.4/static/indexes-partial.html. [April-03-18].

Seshadri, P. and Swami, A. N. (1995). Generalized partial indexes. In ICDE, pages 420–427. IEEE Computer Society.

Shasha, D. E. and Bonnet, P. (2002). Database Tuning - Principles, Experiments, and Troubleshooting Techniques. Elsevier.

Stonebraker, M. (1989). The case for partial indexes. SIGMOD Record, 18(4):4–11.

Voigt, H., Jäkel, T., Kissinger, T., and Lehner, W. (2012). Adaptive index buffer. In ICDE Workshops, pages 308–314. IEEE Computer Society.

Wu, S., Li, J., Ooi, B. C., and Tan, K. (2008). Just-in-time query retrieval over partially indexed data on structured P2P overlays. In SIGMOD Conf., pages 279–290. ACM.