README

Introduction

PgsqlIndexAnalyzer - it's a simple PHP library, which help support indexes in your postgresql database.
Attention! This library is of a recommendation nature. All the indexes obtained need to be analyzed.

You can use it to find the following indexes:

All indexes by tables
Unused indexes by tables
Overlapping indexes by tables
Indexes Contains In Other Indexes ByTables

Installing
Methods
Usage
Tests

Installing

composer require sapronovps/pgsqlindexanalyzer --dev

Methods

Library contains only 4 methods:

allIndexesByTables - method return all indexes by tables.
unusedIndexesByTables - method return unused indexes by tables. Unused indexes are determined by the parameter IndexScan = 0
overlappingIndexesByTables - method return overlapping indexes by tables.
indexesContainsInOtherIndexesByTables - method return indexes contains in other indexes by tables.

Usage

First you need to create an instance of the library and estimate the configuration.

<?php

use Sapronovps\PgsqlIndexAnalyzer\Connection\Connection;
use Sapronovps\PgsqlIndexAnalyzer\Option\Options;
use Sapronovps\PgsqlIndexAnalyzer\PgsqlIndexAnalyzer;

// Create options as array OR with Options class.
 
$options = [
'host' => 'localhost',
'dbName' => 'postgresql',
'user' => 'postgresql',
'password' => 'secretPassword',
];

// OR 
$options = new Options();
$options->setHost('localhost')
        ->setDbName('postgresql')
        ->setUser('postgresql')
        ->setPassword('secretPassword');
        
$connection = new Connection($options);

$pgsqlIndexAnalyzer = new PgsqlIndexAnalyzer($connection);

Get all indexes by tables:

$tables = [
'table1',
'table2',
'table3',
];

$allIndexes = $pgsqlIndexAnalyzer->allIndexesByTables($tables);

Get unused indexes by tables:
Unused indexes - it's indexes when parameter "IndexScan" === 0;

$tables = [
'table1',
'table2',
'table3',
];

$unusedIndexesByTables = $pgsqlIndexAnalyzer->unusedIndexesByTables($tables);

Get overlapping indexes by tables:
Overlapping indexes - in postgresql, indexes are read from left to right, so there are indexes that are redundant and already contained in any existing index in strict left-to-right order. Usually such indexes can be deleted.

$tables = [
'table1',
'table2',
'table3',
];

$overlappingIndexesByTables = $pgsqlIndexAnalyzer->overlappingIndexesByTables($tables);

Get indexes contains in other indexes by tables:
Indexes contains in other indexes - this method is very similar to overlappingIndexesByTables, but this method looks for redundant indexes without taking into account reading from left to right. Attention: such indexes can be deleted only after a detailed analysis.

$tables = [
'table1',
'table2',
'table3',
];

$indexesContainsInOtherIndexesByTables = $pgsqlIndexAnalyzer->indexesContainsInOtherIndexesByTables($tables);

Tests

This library is covered by unit test and phpstan.

sapronovps / pgsqlindexanalyzer

Maintainers

Details