PostgreSQL (12-16) may ignore your fresh built index due to long-running transaction in another session. It's since 2007, introduced by HOT optimization. Look at pg_index.indcheckxmin of your index.
First session:
postgres=# begin;
BEGIN
postgres=*# select txid_current();
txid_current
--------------
10008974883
(1 row)
Second session:
postgres=# drop table tt;
DROP TABLE
postgres=# create table tt(id bigint, val text);
CREATE TABLE
postgres=#
postgres=# insert into tt(id,val) select i, md5(i::text) from generate_series(1,10000) as i;
INSERT 0 10000
postgres=# update tt set val = 'asdf' where id = 10000;
UPDATE 1
postgres=# delete from tt where id = 10000;
DELETE 1
postgres=#
postgres=# create index on tt(id);
CREATE INDEX
postgres=#
postgres=# select indcheckxmin from pg_index where indexrelid = 'tt_id_idx'::regclass::oid; -- true
indcheckxmin
--------------
t
(1 row)
postgres=# explain select * from tt where id = 100
postgres-# ;
QUERY PLAN
------------------------------------------------------
Seq Scan on tt (cost=0.00..218.99 rows=50 width=40)
Filter: (id = 100)
(2 rows)
After commit inside first session:
postgres=# explain select * from tt where id = 100
;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using tt_id_idx on tt (cost=0.29..2.50 rows=1 width=41)
Index Cond: (id = 100)
(2 rows)