Skip site navigation (1) Skip section navigation (2)

Re: Delete query takes exorbitant amount of time

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Karim Nassar <karim(dot)nassar(at)NAU(dot)EDU>,Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Delete query takes exorbitant amount of time
Date: 2005-03-25 21:10:51
Message-ID: 20050325130124.U15470@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 25 Mar 2005, Simon Riggs wrote:

> On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)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?

The indexes "usability" only applies to the purpose of guaranteeing
uniqueness which doesn't depend on the referencing type AFAICS.

> > > 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?

Yes.  And at least in SQL99, there's a bunch of statements in 4.* about
what are comparable.

> 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?

Not entirely. I believe a referenced column of int, and a referencing
column of numeric currently displays that warning, but appears to be
allowed by the spec (as the numeric types are considered mutually
comparable).

> 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, if we were to
limit it to the spec then many of the implicit casts and cross-type
comparison operators we have would be invalid as well since the comparison
between those types would have to fail as well unless we treat the
comparable used by <comparison predicate> differently.

In response to

Responses

pgsql-performance by date

Next:From: Matthew T. O'ConnorDate: 2005-03-25 21:12:27
Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM]
Previous:From: Matthew T. O'ConnorDate: 2005-03-25 21:04:25
Subject: Re: pg_autovacuum not having enough suction ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group