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