From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: maxim.boguk@gmail.com
Subject: BUG #17594: conditional hash indexes size (hash index ignore WHERE condition during CREATE INDEX?)
Date: Thu, 25 Aug 2022 20:19:54 +0000
Message-ID: <17594-fca95086967695be@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17594
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 14.4
Operating system: Linux
Description:
Hi,
I found very weird situation with size of highly selective partial hash
indexes (they has almost same size as full table hash index).
Test case:
create table test_1000000 as select id from generate_series(1, 1000000) as
g(id);
create index test_1000000_hash_full_idx on test_1000000 using hash(id);
create index test_1000000_hash_partial_idx on test_1000000 using hash(id)
where id<11;
create table test_10 as select id from generate_series(1, 10) as g(id);
create index test_10_hash_full on test_10 using hash(id);
\di+ test_1*
List of relations
Schema | Name | Type | Owner | Table |
Persistence | Access method | Size | Description
--------+-------------------------------+-------+--------+--------------+-------------+---------------+-------+-------------
public | test_1000000_hash_full_idx | index | mboguk | test_1000000 |
permanent | hash | 32 MB |
public | test_1000000_hash_partial_idx | index | mboguk | test_1000000 |
permanent | hash | 28 MB |
public | test_10_hash_full | index | mboguk | test_10 |
permanent | hash | 80 kB |
Expected: the test_1000000_hash_partial_idx and test_10_hash_full indexes
should have same or at least close size because they index same amount of
rows (10):
select count(*) from test_10;
count
-------
10
select count(*) from test_1000000 where id<11;
count
-------
10
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, maxim.boguk@gmail.com
Subject: Re: BUG #17594: conditional hash indexes size (hash index ignore WHERE condition during CREATE INDEX?)
In-Reply-To: <17594-fca95086967695be@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox