Open urtho opened 2 years ago
Hash or nonclustered may be good for mixed data sets, if clustering/ordering lookups on common keys are not needed: https://postgrespro.com/blog/pgsql/4161321
Do you have an example proc, table and index to look into?
I am not up to speed on postgres, but in general if page or row or columnstore compression can be leveraged, that would also reduce disk utilization depending on the index type.
If summarized results are needed clustered columnstore type indexing may offer more compression for summary performance but poor insert/update maintenance, although for large data warehouses they can also help compress wide complex data sets.
Problem
All indexer indices are btree but not all have to be.
Solution
Replace btree indexes with hash indexes in places where only exact searches are used and the resulting hash index is significantly smaller.
One such example could be :
Urgency
Performance optimization.