Re: Delete query takes exorbitant amount of time

On Fri, 2005-03-25 at 16:25 -0500, Tom Lane wrote:
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > On Fri, 25 Mar 2005, Simon Riggs wrote:
> >> Could it be that because PostgreSQL has a very highly developed sense of
> >> datatype comparison that we might be taking this to extremes? Would any
> >> other RDBMS consider two different datatypes to be comparable?
>
> > We do have a broader comparable than the spec.
>
> However, the set of comparisons that we can presently support *with
> indexes* is narrower than the spec, so rejecting nonindexable cases
> would be a problem.

OK. Can we have a TODO item then?

* Ensure that all SQL:2003 comparable datatypes are also indexable when
compared

...or something like that

> It's worth noting also that the test being discussed checks whether the
> PK index is usable for testing the RI constraint. In the problem that
> started this thread, the difficulty is lack of a usable index on the FK
> column, not the PK (because that's the table that has to be searched to
> do a delete in the PK table). We cannot enforce that there be a usable
> index on the FK column (since indexes on the FK table may not have been
> built yet when the constraint is declared), and shouldn't anyway because
> there are reasonable usage patterns where you don't need one.

Yes, I agree for CASCADE we wouldn't always want an index.

Alright then, time to leave it there.

I want to write up some additional comments for performance tips:
- Consider defining RI constraints after tables have been loaded
- Remember to add an index on the referencing table if the constraint is
defined as CASCADEing

Have a good Easter, all, wherever you are and whatever you believe in.

Best Regards, Simon Riggs