X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 60519535E6
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 20:30:23 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 93866-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 20:30:13 +0000 (GMT)
Received: from cmailm1.svr.pol.co.uk (cmailm1.svr.pol.co.uk [195.92.193.18])
	by svr1.postgresql.org (Postfix) with ESMTP id 6F3505360A
	for <pgsql-performance@postgresql.org>;
	Fri, 25 Mar 2005 20:30:12 +0000 (GMT)
Received: from modem-2520.lion.dialup.pol.co.uk ([217.135.169.216]
	helo=192.168.0.102) by cmailm1.svr.pol.co.uk with esmtp (Exim 4.41)
	id 1DEvRR-0004ci-MJ; Fri, 25 Mar 2005 20:30:02 +0000
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: <28057.1111776442@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>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Fri, 25 Mar 2005 20:28:49 +0000
Message-Id: <1111782529.11750.815.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200503/559
X-Sequence-Number: 11264

On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> >>> Other than spec compliance, you mean?  SQL99 says
> >>> 
> >>> ... The declared type of each referencing column shall be
> >>> comparable to the declared type of the corresponding referenced
> >>> column.
> 
> > Tom had said SQL99 required this; I have pointed out SQL:2003, which
> > supercedes the SQL99 standard, does not require this.
> 
> You're reading the wrong part of SQL:2003.  11.8 <referential constraint
> definition> syntax rule 9 still has the text I quoted.

So, we have this from SQL:2003 section 11.8 p.550
- 3a) requires us to have an index
- 9) requires the data types to be "comparable"

In the name of spec-compliance we wish to accept an interpretation of
the word "comparable" that means we will accept two datatypes that are
not actually the same. 

So we are happy to enforce having the index, but not happy to ensure the
index is actually usable for the task?

> > Leading us back to my original point - what is the benefit of continuing
> > with having a WARNING when that leads people into trouble later?
> 
> Accepting spec-compliant schemas.

I definitely want this too - as you know I have worked on documenting
compliance previously.

Is the word "comparable" defined elsewhere in the standard?

Currently, datatypes with similar type categories are comparable and yet
(in 8.0) will now use the index. So, we are taking comparable to include
fairly radically different datatypes?

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?

Please consider this. 

Best Regards, Simon Riggs