CODEWITHSUNDEEP
postgresql
user10339780
user10339780

Reputation: 963

Why in the same query, hash index is ignored and btree index is used

I think both cases only need to scan the indexes, but this is not the case. Only BTree index is used.

The following is an example

drop table if exists test;

create table test
(
    id    serial4 primary key,
    name  varchar(255) not null,
    score int          not null
);

I inserted 1,000,000 rows. 9867 rows score is 0.

Explain With Hash Indexes

drop index if exists test_score_zero;
create index test_score_zero on test using hash (id) where score = 0;
explain select id from test where score = 0;

Explain With BTree Indexes

drop index if exists test_score_zero;
create index test_score_zero on test using btree (id) where score = 0;
explain select id from test where score = 0;

The Hash Indexes result is

Gather  (cost=1000.00..13578.03 rows=9867 width=4)
  Workers Planned: 2
  ->  Parallel Seq Scan on test  (cost=0.00..11591.33 rows=4111 width=4)
        Filter: (score = 0)

The BTree Indexes result is

Index Only Scan using test_score_zero on test  (cost=0.29..262.58 rows=9867 width=4)

PostgreSQL Version:

psql (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1)

Upvotes: 0

Views: 597

Answers (1)

user330315
user330315

Reputation:

As documented in the manual hash indexes simply do not support "Index Only Scan"

To solve this performance problem, PostgreSQL supports index-only scans [...]

The index type must support index-only scans. B-tree indexes always do. GiST and SP-GiST indexes support index-only scans for some operator classes but not others. Other index types have no support.

(emphasis mine)

There are only very few reasons to use a hash index instead of a B-Tree index and if you expect that Index Only Scan will help performance for your queries substantially, just stick with a B-Tree index.

Upvotes: 1

Related Questions