Re: [PERFORM] [BUGS] BUG #2737: hash indexing large table fails,while btree of same index works

[Simon Riggs]

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" <[EMAIL PROTECTED]> 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



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] [BUGS] BUG #2704: pg_class.relchecks overflow problem

[Tom Lane]

Toru SHIMOGAKI <[EMAIL PROTECTED]> writes:
> + if (numchecks == 0x7FFF)
> + ereport(ERROR,
> + 
> (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
> +   errmsg("cannot have more than 2^15-1 checks in a 
> table")));

While there's not anything wrong with this proposed patch in itself,
I have to admit that I don't see the point.  There are probably
thousands of places in the backend where we increment an integer
value without checking for overflow.  Is this one particularly more
likely to occur than other ones, or does it have worse consequences
than other ones?  I don't see a security issue here (since the backend
doesn't crash) and I also don't see that this limit is close enough to
real practice to be important to guard against.

It's not that the check imposes any significant addition in code space
or runtime, but what it *would* impose is a nontrivial extra burden on
our message translators.  Scale this up by a few hundred or thousand
equally unlikely conditions with their own error messages, and we'd
have a revolt ...

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org