Retrieval of Status values in the database

For relational databases, table-based retrieval can achieve good search results by establishing appropriate indexes. (This does not include the rationality of the table design.) t_girlcreatetablerank_status (idintegernotnull, I _statusvarchar (3) notnull); t_girlcreatetablerank_status_extend (

For relational databases, table-based retrieval can achieve good search results by establishing appropriate indexes. (This does not include the rationality of the table design.) t_girlcreate table rank_status (id integer not null, I _status varchar (3) not null); t_girlcreate table rank_status_extend (

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)

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

T_girl> create table rank_status_extend (I _status varchar (3) not null, ids text );

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

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

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

t_girl>select count(*) as total from rank_status where i_status = 'yes'; total ------- 99600 (1 row) Time: 86.563 ms t_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 ms t_girl>select string_agg(id::text,','),i_status from rank_status group by i_status; Time: 113.393 ms t_girl>select ids from rank_status_extend where i_status = 'yes'; Time: 2.447 ms

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

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.