Subject: Re: Delete query takes exorbitant amount of time
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>,
	Karim Nassar <karim.nassar@NAU.EDU>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
In-Reply-To: <12831.1111785909@sss.pgh.pa.us>
References: <1111709457.9085.127.camel@k2.cet.nau.edu>
	<2630.1111711927@sss.pgh.pa.us>
	<1111715131.9085.174.camel@k2.cet.nau.edu>
	<3750.1111715318@sss.pgh.pa.us> <4243879B.10200@familyhealth.com.au>
	<20845.1111733900@sss.pgh.pa.us>
	<1111763448.11750.730.camel@localhost.localdomain>
	<23515.1111763837@sss.pgh.pa.us>
	<1111766478.11750.762.camel@localhost.localdomain>
	<20050325082101.G4358@megazone.bigpanda.com>
	<1111775050.11750.790.camel@localhost.localdomain>
	<28057.1111776442@sss.pgh.pa.us>
	<1111782529.11750.815.camel@localhost.localdomain>
	<20050325130124.U15470@megazone.bigpanda.com>
	<12831.1111785909@sss.pgh.pa.us>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Fri, 25 Mar 2005 21:50:04 +0000
Message-Id: <1111787404.11750.825.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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