Simon Nicholls wrote:

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

Oliver Jowett <oliver (AT) opencloud (DOT) com> writes:
That division of labor seems more than a tad brain-dead anyway;
who's to say that the appropriate dialect-specific command is an
ALTER TABLE at all? It would be better if the dialect-specific
code were responsible for emitting the entire command --- in which
case it would have the tablename too and could do the Right Thing.

So I'd suggest filing a bug/enhancement request against Hibernate.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly