PostgreSQL - choosing the right index for the status field (varchar)

Question

I have a table with a lot of records and a varchar field of length 8, which represents different statuses. There are only about 5 different statuses, let's say "STATUS1", "STATUS2", ... and most of the time it is NULL.

When I index this field, it does not do much because there are many equal values, and then postgres does not use the index.

My question is: is there a way to index such a field and speed it up? In most cases, I request status IS NULL, and I think I can not do it faster. But what if I check on status = 'STATUS1'?

Answer 1

. , ,

SELECT *
  FROM the_table
 WHERE color in ('green', 'blue') AND status = 'STATUS1' ;

, , () , partial index:

CREATE TABLE the_table
(
   color text, 
   status character varying(8)
    /* and anything you need */
) ; 

CREATE INDEX
  ON public.the_table (color)
  WHERE status = 'STATUS1' ;

PostgreSQL ( , ), , , enumerated type, . : ( "" ), , ( ), , varchar (8):

CREATE TYPE status_type AS ENUM
   ('STATUS1',
    'STATUS2',
    'STATUS3');

:

CREATE TABLE the_table
(
   color text, 
   status status_type
    /* and anything you need */
) ; 

(), (, ), > .

, [ish] (anonymous_id_PK, status_value).