From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Karim Nassar <karim.nassar@NAU.EDU>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Fri, 25 Mar 2005 21:50:04 +0000
Message-ID: <1111787404.11750.825.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com, karim.nassar@NAU.EDU, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1111787404.11750.825.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox