pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #2737: hash indexing large table fails, while btree of same index works
4+ messages / 3 participants
[nested] [flat]
* BUG #2737: hash indexing large table fails, while btree of same index works
@ 2006-11-05 13:47 Balazs Nagy <bnagy@thenewpush.com>
2006-11-10 23:55 ` Re: BUG #2737: hash indexing large table fails, while btree of same index works Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Balazs Nagy @ 2006-11-05 13:47 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
The following bug has been logged online:
Bug reference: 2737
Logged by: Balazs Nagy
Email address: bnagy@thenewpush.com
PostgreSQL version: 8.1.5
Operating system: RHEL4
Description: hash indexing large table fails, while btree of same
index works
Details:
Postgres: 8.1.5
Database table size: ~60 million rows
Field to index: varchar 127
CREATE INDEX ... USING hash ...
fails with a file not found error (psql in verbose mode):
ERROR: 58P01: could not open segment 3 of relation 1663/16439/16509 (target
block 528283): No such file or directory
LOCATION: _mdfd_getseg, md.c:954
VACUUM, VACUUM FULL doesn't help, full dump and reload doesn't help either
CREATE INDEX ... USING btree ...
works fine. Could there be a bug in the hash algorithm's implementation?
System is x86_64 SMP 8 CPU core, 16GB RAM, Fiber channel SAN, kernel
2.6.9-42.0.3.ELsmp
I haven't tried the 8.2beta2 yet, but would be happy to try, as the hash
method is better suited for the kind of index I need...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #2737: hash indexing large table fails, while btree of same index works
2006-11-05 13:47 BUG #2737: hash indexing large table fails, while btree of same index works Balazs Nagy <bnagy@thenewpush.com>
@ 2006-11-10 23:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-11 08:17 ` Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-11-10 23:55 UTC (permalink / raw)
To: Balazs Nagy <bnagy@thenewpush.com>; +Cc: pgsql-bugs@postgresql.org; pgsql-performance
[ cc'ing to pgsql-performance because of performance issue for hash indexes ]
"Balazs Nagy" <bnagy@thenewpush.com> writes:
> Database table size: ~60 million rows
> Field to index: varchar 127
> CREATE INDEX ... USING hash ...
> fails with a file not found error (psql in verbose mode):
> ERROR: 58P01: could not open segment 3 of relation 1663/16439/16509 (target
> block 528283): No such file or directory
> LOCATION: _mdfd_getseg, md.c:954
Wow, you're trying to build an 8GB hash index? Considering that hash
indexes still don't have WAL support, it hardly seems like a good idea
to be using one that large.
The immediate problem here seems to be that the hash code is trying to
touch a page in segment 4 when it hasn't yet touched anything in segment
3. The low-level md.c code assumes (not unreasonably) that this
probably represents a bug in the calling code, and errors out instead of
allowing the segment to be created.
We ought to think about rejiggering the smgr.c interface to support
hash's behavior more reasonably. There's already one really bad kluge
in mdread() for hash support :-(
One thought that comes to mind is to require hash to do an smgrextend()
addressing the last block it intends to use whenever it allocates a new
batch of blocks, whereupon md.c could adopt a saner API: allow
smgrextend but not other calls to address blocks beyond the current EOF.
I had once wanted to require hash to explicitly fill all the blocks in
sequence, but that's probably too radical compared to what it does now
--- especially seeing that it seems the extension has to be done while
holding the page-zero lock (see _hash_expandtable). Writing just the
logically last block in a batch would have the effect that hash indexes
could contain holes (unallocated extents) on filesystems that support
that. Normally I would think that probably a Bad Thing, but since hash
indexes are never scanned sequentially, it might not matter whether they
end up badly fragmented because of after-the-fact filling in of a hole.
Thoughts?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works
2006-11-05 13:47 BUG #2737: hash indexing large table fails, while btree of same index works Balazs Nagy <bnagy@thenewpush.com>
2006-11-10 23:55 ` Re: BUG #2737: hash indexing large table fails, while btree of same index works Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-11-11 08:17 ` Simon Riggs <simon@2ndquadrant.com>
2006-11-16 22:48 ` Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Simon Riggs @ 2006-11-11 08:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Balazs Nagy <bnagy@thenewpush.com>; pgsql-bugs@postgresql.org; pgsql-performance
On Fri, 2006-11-10 at 18:55 -0500, Tom Lane wrote:
> [ cc'ing to pgsql-performance because of performance issue for hash indexes ]
>
> "Balazs Nagy" <bnagy@thenewpush.com> writes:
> > Database table size: ~60 million rows
> > Field to index: varchar 127
>
> > CREATE INDEX ... USING hash ...
I'd be interested in a performance test that shows this is the best way
to index a table though, especially for such a large column. No wonder
there is an 8GB index.
> One thought that comes to mind is to require hash to do an smgrextend()
> addressing the last block it intends to use whenever it allocates a new
> batch of blocks, whereupon md.c could adopt a saner API: allow
> smgrextend but not other calls to address blocks beyond the current EOF.
> Thoughts?
Yes, do it.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works
2006-11-05 13:47 BUG #2737: hash indexing large table fails, while btree of same index works Balazs Nagy <bnagy@thenewpush.com>
2006-11-10 23:55 ` Re: BUG #2737: hash indexing large table fails, while btree of same index works Tom Lane <tgl@sss.pgh.pa.us>
2006-11-11 08:17 ` Re: [PERFORM] BUG #2737: hash indexing large table fails, while btree of same index works Simon Riggs <simon@2ndquadrant.com>
@ 2006-11-16 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-11-16 22:48 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Balazs Nagy <bnagy@thenewpush.com>; pgsql-bugs@postgresql.org; pgsql-performance
"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Fri, 2006-11-10 at 18:55 -0500, Tom Lane wrote:
>> One thought that comes to mind is to require hash to do an smgrextend()
>> addressing the last block it intends to use whenever it allocates a new
>> batch of blocks, whereupon md.c could adopt a saner API: allow
>> smgrextend but not other calls to address blocks beyond the current EOF.
> Yes, do it.
I found out that it's easy to reproduce this failure in the regression
tests, just by building with RELSEG_SIZE set to 128K instead of 1G:
inline patch 1: ./results/create_index.out Thu Nov 16 17:33:29 2006 (+1/-0 lines) download inline-1.patch
*** ./expected/create_index.out Sun Sep 10 13:44:25 2006
--- ./results/create_index.out Thu Nov 16 17:33:29 2006
***************
*** 323,328 ****
--- 323,329 ----
--
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
+ ERROR: could not open segment 7 of relation 1663/16384/26989 (target block 145): No such file or directory
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
end of thread, other threads:[~2006-11-16 22:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-05 13:47 BUG #2737: hash indexing large table fails, while btree of same index works Balazs Nagy <bnagy@thenewpush.com>
2006-11-10 23:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-11 08:17 ` Simon Riggs <simon@2ndquadrant.com>
2006-11-16 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
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