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

The following bug has been logged on the website:

Bug reference:      16130
Logged by:          Mayur B.
Email address:      mayur555b@protonmail.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@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@db_findb on findb1 ([local]:5432)=# create unique index
idx_btree_uq_id1 on test_tab(id1);
CREATE INDEX
postgres@db_findb on findb1 ([local]:5432)=# analyze test_tab;
ANALYZE
postgres@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@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@db_findb on findb1 ([local]:5432)=# analyze test_tab;
ANALYZE
postgres@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@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@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@db_findb on findb1 ([local]:5432)=# select reltuples::numeric from
pg_class where relname='idx_btree_uq_id1';
 reltuples
-----------
    100000
(1 row)

postgres@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@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)