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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
