Collected from the PG bugs email list.
|Reported by||Ortwin Gentz|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15679 Logged by: Ortwin Gentz Email address: (redacted) PostgreSQL version: 10.6 Operating system: Amazon RDS Description: I created a partial HASH index for a sparsely populated column: CREATE INDEX partial_hash ON mytable USING HASH(my_id) WHERE my_ID IS NOT NULL; Even though the my_id VARCHAR(255) column is populated (NON NULL) only for a few hundred records, the index takes 256 MB of space (for a table with > 10m records). Also, it doesn't make a difference if the index is created as a partial index ("WHERE my_ID IS NOT NULL") or as a full index. In contrast to that, a BTREE index differs considerably in space for full and partial: CREATE INDEX full_btree ON mytable (my_id); # 543 MB CREATE INDEX partial_btree ON mytable (my_id) WHERE my_ID IS NOT NULL; # 16 KB See also this StackExchange conversation where user jjanes considers the behavior a bug in the hash index code: https://dba.stackexchange.com/a/231660/25337
|2019-03-08 20:06:45+00||PG Bug reporting form||BUG #15679: Partial HASH index takes too much space|
|2019-03-11 09:23:32+00||David Rowley||Re: BUG #15679: Partial HASH index takes too much space|