Re: Delete query takes exorbitant amount of time

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