I'm trying to build an index for a table with 1B of rows. 24 hours has passed and the query is still running:
CREATE INDEX idx1_table1b on table1b using HASH(column1).
Since column1 is often filtered with equality operator(=), I've chosen hash indexing to be the index type. The DB instance class I'm using is Serverless V2, ACU min-max:16-128, PostgreSQL 14.6.
Not sure if I missed anything in the configuration or statement, any help is appreciated, Thanks!
Found out the column has tons of duplicate value, which might be the cause why the hashing halted(or took a long time to build hash-index).
The solution to my problem is to use btree(which accommodates well duplicate values) and the indexed was built in minutes. The performance of using indexed column to perform join in a query is at milli-second performance.