Re: Delete query takes exorbitant amount of time - Mailing list pgsql-performance

From Simon Riggs
Subject Re: Delete query takes exorbitant amount of time
Date
Msg-id 1111782529.11750.815.camel@localhost.localdomain
Whole thread Raw
In response to Re: Delete query takes exorbitant amount of time  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Delete query takes exorbitant amount of time  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: lazy_update_relstats considered harmful (was Re: pg_autovacuum not having enough suction ?)
Next
From: "Otto Blomqvist"
Date:
Subject: Re: pg_autovacuum not having enough suction ?