Thread: 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@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)

PG Bug reporting form <noreply@postgresql.org> writes:
> Planner does not pick unique btree index and goes for seq scan but unsafe
> hash index works.

I couldn't reproduce this from the given instructions.

Looking at the code, it looks like it might be possible to explain it
with a combination of (1) old_snapshot_threshold being enabled (not -1),
and (2) something holding back global xmin, such as a long-running
transaction.  (Maybe you have an uncommitted prepared transaction?)
In that situation a newly-created index won't be used until all older
transactions have gone away.  But the existence of an unlogged index
(hash index) disables the snapshot threshold feature for the associated
table.

There may be some additional condition needed to cause it, because
I still couldn't reproduce the behavior with those two conditions
set up.

IMO old_snapshot_threshold is a complete kluge and you should not
have it turned on unless you desperately need it.  It has a lot of
poorly-documented drawbacks, including this one.

            regards, tom lane

Thanks Tom,
Indeed old_snapshot_threshold is enabled. I don't know the reason behind it as it is pretty old database but new
createddevelopment and test environments don't have it. I will remove this and test this again. 

Best Regards

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, 21 November 2019 19:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> PG Bug reporting form noreply@postgresql.org writes:
>
> > Planner does not pick unique btree index and goes for seq scan but unsafe
> > hash index works.
>
> I couldn't reproduce this from the given instructions.
>
> Looking at the code, it looks like it might be possible to explain it
> with a combination of (1) old_snapshot_threshold being enabled (not -1),
> and (2) something holding back global xmin, such as a long-running
> transaction. (Maybe you have an uncommitted prepared transaction?)
> In that situation a newly-created index won't be used until all older
> transactions have gone away. But the existence of an unlogged index
> (hash index) disables the snapshot threshold feature for the associated
> table.
>
> There may be some additional condition needed to cause it, because
> I still couldn't reproduce the behavior with those two conditions
> set up.
>
> IMO old_snapshot_threshold is a complete kluge and you should not
> have it turned on unless you desperately need it. It has a lot of
> poorly-documented drawbacks, including this one.
>
> regards, tom lane