Best index for column where values are mostly the same
First off, like you said yourself, not the best use of the column. Should be a separate boolean and an integer column for your "32-bit identifiers". If that's NULL 99% of the time, that is no problem. NULL storage is very cheap.

Either way, you should definitely use a partial index. (That's the proper term as used in the manual.) Excluding 99 % of the rows from the index makes it massively smaller, which matters for performance with millions of rows.

However, if you have a complete index on event_value anyway, and your common queries are retrieving single rows like:

SELECT * FROM tbl WHERE event_value = 123;


... then an additional partial index won't buy much. It would still be used as it's still a bit faster, but not much faster than a complete index. And the costs for an additional index may outweigh the benefits.

While the rare values are "32-bit identifiers", it may be incorrect to assume those are all > 1. Postgres uses signed integer, and 32-bit entities would also cover negative numbers. (Can we even rule out 0 or 1 as one of those identifiers?) If there can be negative values, too:

CREATE INDEX tbl_event_value_part_idx ON tbl (event_value)
WHERE event_value &gt; 1 OR event_value &lt; 0; -- or similar


event_value does not have to be an index column, regardless of its use in the WHERE clause. That entirely depends on the kinds of queries to expect. Either way, the safe bet is to add the same WHERE conditions literally to any query supposed to use the index, even if that's logically redundant. Postgres can make very basic logical conclusions to determine applicable indexes, but it is no AI and does not try to be (would get too expensive quickly). Like:

SELECT * FROM tbl WHERE event_value &gt; 1 OR event_value &lt; 0


Related:


Postgres partial index on IS NULL not working
PostgreSQL partial index unused when created on a table with existing data
How to index a query with `WHERE field IS NULL`?