Retrieval of Status values in the database

For relational databases, table-based retrieval can achieve good search results by establishing appropriate indexes. (Whether the table design is reasonable or not is not included here)
For example, how to retrieve highly selective values such as status columns? In fact, this is not something that relational databases are good. However, for historical or many irresistible reasons,
We have to optimize the relational table. What should we do? Generally, a static table is created. But static tables are also diverse. How should I choose? Here are a few simple examples. Of course,
Some omissions may be caused by insufficient personal brain scales.


Original table.
20 records, about 36 MB.

t_girl>create table rank_status (id integer not null, i_status varchar(3) not null);

The first is to create a LIST table, which can be used as a static table or as the original table for related updates.
There are only two records, about KB.

t_girl>create table rank_status_extend (i_status varchar(3) not null, ids text);

We can update both tables accordingly.


Insert a record.

t_girl> insert into rank_status values (222222,'yes');Time: 4.397 mst_girl>update rank_status_extend set ids = ids ||','||'222222' where i_status = 'yes';Time: 43.725 ms

Delete a record.

t_girl>delete from rank_status where i_status = 'yes' and id = 1;Time: 47.339 mst_girl>update rank_status_extend set ids = replace(ids,',1,',',') where i_status = 'yes';Time: 45.046 ms

Update a record.

t_girl>update rank_status set id = 1000 where i_status = 'yes' and id = 20;Time: 65.834 mst_girl>update rank_status_extend set ids = replace(ids,',20,',',1000,') where i_status = 'yes';Time: 85.974 ms

We can see that in the write operation on the table, the second table is a little slower than the first one.


In fact, we are most concerned about read operations. In fact, reading is still very advantageous.

t_girl>select count(*) as total from rank_status where i_status = 'yes';                               total ------- 99600(1 row)Time: 86.563 mst_girl>select length(ids) - length(replace(ids,',','')) + 1 as total from rank_status_extend where i_status = 'yes'; total ------- 99600(1 row)Time: 35.762 mst_girl>select string_agg(id::text,','),i_status from rank_status group by i_status;Time: 113.393 mst_girl>select ids from rank_status_extend where i_status = 'yes';Time: 2.447 ms

The second one is to create two tables respectively, but the two tables have fewer fields for storing Status values, so they are much smaller in size.

t_girl>create table rank_status_yes (id int not null); 3552 kBt_girl>create table rank_status_no(id int not null); 3584 kB

Of course, the retrieval of this table must be faster than that of the original table. I will not demonstrate it here.


The third is to create a materialized view,

t_girl>create materialized view mv_rank_status_yes as select * from rank_status where i_status = 'yes';

This is actually very similar to the second type of table. The difference is that the maintenance of the second type of table needs to be done manually, and the view system can be maintained.