deep-foundation / deep-foundation/materialized-path

It may be useful to implement universal method for creating all possible standard indexes for any table

Open
#8 0 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
TypeScript
Stars
8
Forks
2
PR merge metrics
No merged PRs in 30d

Description

https://github.com/deepcase/materialized-path/blob/011c8b6b7464097357d2435b6822aa4003887837/migrations/1633562151439-multidirectional.ts#L158-L182

PostgreSQL limits the number of columns for each index with 32 columns.
Indexes can be created for all possible combination of 1, 2, ... N columns. Where N is not greater than 32, but is configurable by user.
It is well-defined for each of PostgreSQL type which indexes are better suited for that type.
So this stored procedure should have two optional arguments - the maximum number of columns for combined indexes and the restricted set of applied index types (B-tree, hash, etc.).

Once this stored procedure is done, the next step will be to implement a stored procedure that will delete all unused indexes. Used indexes can be checked using stats.

On the second thought, I think it should be a good idea to have a mandatory limitation on the maximum number of columns for combined indexes. If we do not limit it and use 32, we will end up with this number of possible combinations:
```mathematica
Sum[CatalanNumber[i], {i, 1, 32}] = 75 254 198 337 177 847
```
https://www.wolframalpha.com/input/?i=Sum%5BCatalanNumber%5Bi%5D%2C+%7Bi%2C+1%2C+32%7D%5D

We can use smaller numbers:
```
Sum[CatalanNumber[i], {i, 1, 16}] = 48760366
Sum[CatalanNumber[i], {i, 1, 8}] = 2055
Sum[CatalanNumber[i], {i, 1, 4}] = 22
Sum[CatalanNumber[i], {i, 1, 2}] = 3
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reading migrations/1633562151439-multidirectional.ts around lines 158-182 and identify how the existing indexes are created. Clarify the maximum-column limit and permitted index types, then define completion as a stored procedure that creates the requested combinations without exceeding the configured limit.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.