BUG #16130: planner does not pick unique btree index and goes for seq scan but unsafe hash index works.

The following bug has been logged on the website:

Bug reference: 16130
Logged by: Mayur B.
Email address: mayur555b(at)protonmail(dot)com
PostgreSQL version: 9.6.15
Operating system: Ubuntu
Description:

Hi,
If anyone encountered this then please suggest solution.

version => PostgreSQL 9.6.15 on x86_64-pc-linux-gnu (Ubuntu
9.6.15-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1)
7.4.0, 64-bit

Planner does not pick unique btree index and goes for seq scan but unsafe
hash index works.

Below is output of a simple test case and other stats, settings etc.
I have tried everything from vacuum analyze,cover indexes to disabling seq
scan but it does not make planner go for index scan.
Short term fix: Reindex system and reboot works, that means everything
created prior to reindex system starts getting used by planner. Another
observation, if there are no sessions running on database (killed/services
shutdown) and then btree index is created then it is used by planner.

postgres(at)db_findb on findb1 ([local]:5432)=# CREATE TABLE test_tab AS (
db_findb(# SELECT GENERATE_SERIES::numeric id1
db_findb(# , (random() * 90000)::numeric + 100000 id2
db_findb(# , (random() * 90000)::numeric + 100000 id3
db_findb(# , (random() * 90000)::numeric + 100000 id4
db_findb(# , (random() * 90000)::numeric + 100000 id5
db_findb(# FROM GENERATE_SERIES(100000, 199999)
db_findb(# );
SELECT 100000
postgres(at)db_findb on findb1 ([local]:5432)=# create unique index
idx_btree_uq_id1 on test_tab(id1);
CREATE INDEX
postgres(at)db_findb on findb1 ([local]:5432)=# analyze test_tab;
ANALYZE
postgres(at)db_findb on findb1 ([local]:5432)=# explain (analyze,buffers)
select id1,id2 from test_tab where id1=100002;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on test_tab (cost=0.00..2387.00 rows=1 width=18) (actual
time=0.009..13.438 rows=1 loops=1)
Filter: (id1 = '100002'::numeric)
Rows Removed by Filter: 99999
Buffers: shared hit=1137
Planning time: 0.083 ms
Execution time: 13.452 ms
(6 rows)

postgres(at)db_findb on findb1 ([local]:5432)=# create index idx_hash_uq_id1 on
test_tab using hash(id1);
WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
postgres(at)db_findb on findb1 ([local]:5432)=# analyze test_tab;
ANALYZE
postgres(at)db_findb on findb1 ([local]:5432)=# explain (analyze,buffers)
select id1,id2 from test_tab where id1=100002;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_hash_uq_id1 on test_tab (cost=0.00..2.22 rows=1
width=18) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (id1 = '100002'::numeric)
Buffers: shared hit=3
Planning time: 0.098 ms
Execution time: 0.025 ms
(5 rows)

---========--- Some settings and stats ---============

postgres(at)db_findb on findb1 ([local]:5432)=# select name,setting,unit from
pg_catalog.pg_settings
db_findb-# where name in
('random_page_cost','seq_page_cost','effective_cache_size','shared_buffers','enable_indexonlyscan'
db_findb(# ,'enable_indexscan');
name | setting | unit
----------------------+----------+------
effective_cache_size | 37748736 | 8kB
enable_indexonlyscan | on |
enable_indexscan | on |
random_page_cost | 1.1 |
seq_page_cost | 1 |
shared_buffers | 12582912 | 8kB
(6 rows)

postgres(at)db_findb on findb1 ([local]:5432)=# select reltuples::numeric from
pg_class where relnamespace='findb'::regnamespace and relname='test_tab';
reltuples
-----------
100000
(1 row)

postgres(at)db_findb on findb1 ([local]:5432)=# select reltuples::numeric from
pg_class where relname='idx_btree_uq_id1';
reltuples
-----------
100000
(1 row)

postgres(at)db_findb on findb1 ([local]:5432)=# select indexrelid::regclass,
indnatts, indisunique, indisvalid, indisready, indislive, indkey
db_findb-# from pg_index where indrelid='findb.test_tab'::regclass;
indexrelid | indnatts | indisunique | indisvalid | indisready |
indislive | indkey
------------------+----------+-------------+------------+------------+-----------+--------
idx_hash_uq_id1 | 1 | f | t | t | t
| 1
idx_btree_uq_id1 | 1 | t | t | t | t
| 1
(2 rows)

postgres(at)db_findb on findb1 ([local]:5432)=# select attname, null_frac,
avg_width, n_distinct::numeric, correlation from pg_stats where
tablename='test_tab';
attname | null_frac | avg_width | n_distinct | correlation
---------+-----------+-----------+------------+-------------
id1 | 0 | 6 | -1 | 1
id2 | 0 | 12 | -0.99998 | -0.00425422
id3 | 0 | 12 | -0.99997 | -0.00237009
id4 | 0 | 12 | -0.99999 | 0.000423895
id5 | 0 | 12 | -0.99996 | -0.00520937
(5 rows)