From: Karim Nassar <karim.nassar@acm.org>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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 14:47:44 -0700
Message-ID: <1111787264.9168.7.camel@k2.cet.nau.edu> (raw)
In-Reply-To: <1111763448.11750.730.camel@localhost.localdomain>
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>
On Fri, 2005-03-25 at 15:10 +0000, Simon Riggs wrote:
> Karim: Did this happen? If not, can you drop and re-create and confirm
> that you get the WARNING? If not, we have problems.
No. Nor do I think that I should. SERIAL is shortcut for INTEGER, no? I
think there is some other (TBD) problem causing my big seq scan.
orfs=# ALTER TABLE measurement DROP CONSTRAINT measurement_id_int_sensor_meas_type_fkey;
ALTER TABLE
orfs=# ALTER TABLE ONLY measurement ADD CONSTRAINT measurement_id_int_sensor_meas_type_fkey
orfs-# FOREIGN KEY (id_int_sensor_meas_type) REFERENCES int_sensor_meas_type(id_int_sensor_meas_type);
ALTER TABLE
orfs=#
The add constraint statement comes directly from a pg_dump.
For clarity, the table/indexes were created as such:
CREATE TABLE int_sensor_meas_type(
id_int_sensor_meas_type SERIAL PRIMARY KEY,
id_sensor integer NOT NULL REFERENCES sensor,
id_meas_type integer NOT NULL REFERENCES meas_type UNIQUE);
CREATE TABLE measurement (
id_measurement SERIAL PRIMARY KEY,
id_int_sensor_meas_type integer NOT NULL REFERENCES int_sensor_meas_type,
datetime timestamp WITH TIME ZONE NOT NULL,
value numeric(15,5) NOT NULL,
created timestamp with time zone NOT NULL DEFAULT now(),
created_by TEXT NOT NULL REFERENCES public.person(id_person));
CREATE INDEX measurement__id_int_sensor_meas_type_idx ON measurement(id_int_sensor_meas_type);
Regards,
--
Karim Nassar
Department of Computer Science
Box 15600, College of Engineering and Natural Sciences
Northern Arizona University, Flagstaff, Arizona 86011
Office: (928) 523-5868 -=- Mobile: (928) 699-9221
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: karim.nassar@acm.org, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1111787264.9168.7.camel@k2.cet.nau.edu>
* 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