deep-foundation / deep-foundation/materialized-path
It may be useful to implement universal method for creating all possible standard indexes for any table
- 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