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 A971453783
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 16:23:27 +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 18636-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Mar 2005 16:23:22 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id B557A52955
	for <pgsql-performance@postgresql.org>;
	Fri, 25 Mar 2005 16:23:21 +0000 (GMT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 4F0F835622; Fri, 25 Mar 2005 08:23:19 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 4D53F3561E; Fri, 25 Mar 2005 08:23:19 -0800 (PST)
Date: Fri, 25 Mar 2005 08:23:19 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Karim Nassar <karim.nassar@NAU.EDU>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1111766478.11750.762.camel@localhost.localdomain>
Message-ID: <20050325082101.G4358@megazone.bigpanda.com>
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>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200503/547
X-Sequence-Number: 11252


On Fri, 25 Mar 2005, Simon Riggs wrote:

> On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > > this situation to continue.
> >
> > 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.
> >
> > It doesn't say that it has to be indexable, and most definitely not that
> > there has to be an index.
>
> specs at dawn, eh?
>
> Well, SQL:2003 Foundation, p.550 clause 3a) states that the the
> <reference columns> in the referencing table must match a unique
> constraint on the referenced table, or the PRIMARY KEY if the columns
> are not specified. Either way, the referenced columns are a unique
> constraint (which makes perfect sense from a logical data perspective).
>
> We implement unique constraints via an index, so for PostgreSQL the
> clause implies that it must refer to an index.

IMHO, that reference is irrrelevant.  Yes, there must be an index due to
our implementation, however that doesn't imply that the types must be the
same, nor even that the index must be usable for the cross table
comparison.