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
...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
In response to
pgsql-performance by date
|Next:||From: Simon Riggs||Date: 2005-03-25 22:20:23|
|Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM]|
|Previous:||From: Karim Nassar||Date: 2005-03-25 21:47:44|
|Subject: Re: Delete query takes exorbitant amount of time|