Re: BUG #1410: Hibernate PerformanceTest is incredibly slow

Simon Nicholls wrote:

> Bug reference:      1410
> PostgreSQL version: PostgreSQL 8.0
> Operating system:   WindowsXP
> Description:        Hibernate PerformanceTest is incredibly slow (in effect
> unusable)

> 00:41:41,453 DEBUG SchemaExport:136 - drop table Simple
> 00:41:41,500 DEBUG SchemaExport:154 - create table Simple (
>    id_ int8 not null,
>    name varchar(255),
>    address varchar(255),
>    count_ int4 not null unique,
>    date_ timestamp,
>    pay float4,
>    other int8,
>    primary key (id_)
> )
> 00:41:41,656 DEBUG SchemaExport:154 - alter table Simple add constraint
> FK939D1DD26527F10 foreign key (other) references Simple

Without an index on Simple.other, DELETEs on Simple can be slow (each
change requires a seqscan over Simple to check the FK constraint). It
looks like Hibernate's postgresql dialect code does not add an index in
this case.

If I patch Hibernate to not add a FK constraint at all, it runs
substantially faster. It does not seem trivial to fix Hibernate to add
an index in this case -- Hibernate wants to run "ALTER TABLE tablename
<dialect specific sql>" to add a FK constraint, but we need to execute
"ALTER TABLE tablename ADD CONSTRAINT ...; CREATE INDEX .... ON
tablename ..." and the dialect code is not given "tablename".

So to fix this properly needs some work on the Hibernate side.

-O