Re: Delete query takes exorbitant amount of time
От | Simon Riggs |
---|---|
Тема | Re: Delete query takes exorbitant amount of time |
Дата | |
Msg-id | 1111787404.11750.825.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Delete query takes exorbitant amount of time (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-performance |
On Fri, 2005-03-25 at 16:25 -0500, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.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
В списке pgsql-performance по дате отправления: